How to make bootable windows 7 usb on mac os x. How To Create & Manage Named Ranges In Excel 2013. How formulae definition is quite flexible and accommodating in the application especially due to the in-built Name Manager in Microsoft Excel 2013. Subscribe to our mailing list for updates in your email inbox.

I am new to this forum and VBA in Excel. I have a routine that I've managed to cobble together that performs erases any values in adjacent columns when the value in the current column is changed.

I also need to provide a default value in a cell 3 columns over. I defined a name in the Name Manager (Task_Dept_Default) and just want to set this value into the cell. This is in a table and I didn't want to use a formula to set the default since the user can overwrite it and hence erase the formula causing problem in the table. The line is written into the routine is: Target.Offset(0, 3).Value = ThisWorkbook.Names('Task_Dept_Default').Value (Target is passed into to the routine and is the current cell) However, this statement puts the formula for Task_Dept_Default into the cell and not the resultant value. I'm sure there is a simple solution to this, I'm just having trouble finding it. Many thanks Carrie.

Code: ThisWorkbook.Names.Add Name:='Task_Dept_Default', RefersTo:=Target.Offset(0, 3) OK this is not intuitively obvious. So please forgive me. This looks like it sets the value in Name manager to the value that is in Target.offset(0.3) when I need it the other way around.

Here is the entire routine so that you can see it in context. (all suggestion for improvement, greatly appreciated!) Private Sub Worksheet_Change(ByVal Target As Range) 'This routine deletes values in Activity (Column 3) and Activiy Descriptor (Column4) 'when task (Column 2) is modified. This is done to eliminate the possibility 'of incorrect data combinations being used. Thanks for your help! When I saw this and thought, Oh My I was only missing the [], but alas, this doesn't appear to work either.

It appears to copy the null string into the cell and Task_dept_Default is not a null string. At this point, at least until I can devise a more elegant solution, I am placing the Task_dept_Default in a column at the end of my table and then copying it into the cell using Target.offset(0,4).Value = target (0,11).Value. Best youtube grabber. This is my very first attempt at VBA and I admit it is much harder than I expected, even for someone with load s of traditional programming experience. I'll need to get some good reference and/or tutorial books.

Yes, it is in the UI: That keystroke summons the Insert> Name> Define dialog referred to by Bob G in his original reply in this thread. It also can be summoned in Excel 2016 by clicking the Define Name button on the Formulas tab of the Ribbon.

[Note that the use of the fn key is dependent on keyboard type & Keyboard settings in System Preferences. On standard full keyboards Command+F3 is sufficient.] As an MVP my contributions here are purely voluntary.

Please mark Yes/No as to whether a Reply answers your question. Regards, Bob J.