Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Offset Reference Cell
I am using the OFFSET function with a spreadsheet created by our IT
department. I have added the offset function to help sort the results in the spreadsheet. When initially entered the function works fine. However, when the data is updated (background macro, code is protected), the "refer to" in the named range changes to OFFSET (sheetname!#REF!). Before I go to my IT folks, I would like to understand what could be causing the reference to change. Any ideas would be appreciated. Thank you. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Offset Reference Cell
It could be the background macro is actually deleting the cell at one point,
and then filling it in later with something else. Other option is if it somehow inserts rows/cells that push your reference cell off the sheet. When it deleted the cell, your formula lost the REF, and created the error. You could use INDIRECT though to get around this. E.g., =OFFSET(INDIRECT("'Sheet2'!C6"),1,1) This will always look at Sheet2, C6, no matter if the current C6 gets moved or deleted. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Alex Mackenzie" wrote: I am using the OFFSET function with a spreadsheet created by our IT department. I have added the offset function to help sort the results in the spreadsheet. When initially entered the function works fine. However, when the data is updated (background macro, code is protected), the "refer to" in the named range changes to OFFSET (sheetname!#REF!). Before I go to my IT folks, I would like to understand what could be causing the reference to change. Any ideas would be appreciated. Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Repost: Copy and Offset cell reference | Excel Worksheet Functions | |||
Dynamic Reference Cell in Offset Formula | Excel Discussion (Misc queries) | |||
How to reference an offset from a cell? | Excel Worksheet Functions | |||
problem with cell reference in =sum(offset(cell reference,x,y,z,a)). Want cell ref to be variable. | Excel Worksheet Functions | |||
Problem with =sum(offset(cell reference,w,x,y,z). I want cell reference to be variable | Excel Worksheet Functions |