Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Absolute Reference Changes
I have the below formula in "sheet2" of a workbook ("Schedule" being the only
other sheet). =IF(OR(Schedule!$A$5="Totals", Schedule!$A$4="Totals"), "", IF(OR(Schedule!$B$4="START", NOT($C$1=""))*NOT(Schedule!$B$4="END"), Schedule!$A$5-Schedule!$A$4, "")) In creating this I initially used relative references so that I could copy it over a large number of rows (10000) and have the row references update automatically. I then converted it to absolute references by using find/replace (eg Find: A Replace: $A$). I've done this over so many rows because the other sheet is a blank form and it will be used multiple times with varying numbers of rows. Because of this, I need to be able to insert blank rows into the middle of it. That is where the problem arises. In the above formula, the refences currently showing $A$5 don't appear to be absolute, they change when I insert new rows. None of the other values do that. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Absolute Reference Changes
If you insert a new row at row 5, all references to cells in row 5 and
beyond will adjust, relative or absolute. -- __________________________________ HTH Bob "ATChurch" wrote in message ... I have the below formula in "sheet2" of a workbook ("Schedule" being the only other sheet). =IF(OR(Schedule!$A$5="Totals", Schedule!$A$4="Totals"), "", IF(OR(Schedule!$B$4="START", NOT($C$1=""))*NOT(Schedule!$B$4="END"), Schedule!$A$5-Schedule!$A$4, "")) In creating this I initially used relative references so that I could copy it over a large number of rows (10000) and have the row references update automatically. I then converted it to absolute references by using find/replace (eg Find: A Replace: $A$). I've done this over so many rows because the other sheet is a blank form and it will be used multiple times with varying numbers of rows. Because of this, I need to be able to insert blank rows into the middle of it. That is where the problem arises. In the above formula, the refences currently showing $A$5 don't appear to be absolute, they change when I insert new rows. None of the other values do that. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Absolute Reference Changes
Yes, I see that now. Thanks.
Anyway, as is "^£%*$ typical, the next question I actually found on here answered it for me. I inserted INDIRECT("X#") around every cell reference. "Bob Phillips" wrote: If you insert a new row at row 5, all references to cells in row 5 and beyond will adjust, relative or absolute. -- __________________________________ HTH Bob "ATChurch" wrote in message ... I have the below formula in "sheet2" of a workbook ("Schedule" being the only other sheet). =IF(OR(Schedule!$A$5="Totals", Schedule!$A$4="Totals"), "", IF(OR(Schedule!$B$4="START", NOT($C$1=""))*NOT(Schedule!$B$4="END"), Schedule!$A$5-Schedule!$A$4, "")) In creating this I initially used relative references so that I could copy it over a large number of rows (10000) and have the row references update automatically. I then converted it to absolute references by using find/replace (eg Find: A Replace: $A$). I've done this over so many rows because the other sheet is a blank form and it will be used multiple times with varying numbers of rows. Because of this, I need to be able to insert blank rows into the middle of it. That is where the problem arises. In the above formula, the refences currently showing $A$5 don't appear to be absolute, they change when I insert new rows. None of the other values do that. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
absolute reference | Charts and Charting in Excel | |||
Absolute cell reference will not remain absolute. | Excel Worksheet Functions | |||
Absolute Reference (again) | Excel Discussion (Misc queries) | |||
what is absolute reference | Excel Discussion (Misc queries) | |||
Absolute Reference | Excel Discussion (Misc queries) |