Fixing a speadsheet formula reference so that adding a row does not change it
This is probably a very simple answer, but I am pulling my hair out trying to figure it out. I am doing a spreadsheet (in OpenOffice) in which I want to calculate the sum of the last 12 months. Every month we will need to add a new row, and I don't want to have to reset the formulas each time.
Now before you all start talking about absolute cell references, I have tried that, and as you add a row, the "absolute" reference stays with the cells originally defined, so despite the new month being added, the formula still reference the old data. Now that makes a certain amount of sense to me, but I need a way of fixing the formula to a specific cell, regardless of any rows of columns that may be added to the rest of the sheet.
Named ranges move as well, so that's no use. At the moment the only thing I can see working is to cut the data and paste it one row down, which as the data set is growing by one row a month, is not a fixed target either.
Any ideas?
Marco.
Now before you all start talking about absolute cell references, I have tried that, and as you add a row, the "absolute" reference stays with the cells originally defined, so despite the new month being added, the formula still reference the old data. Now that makes a certain amount of sense to me, but I need a way of fixing the formula to a specific cell, regardless of any rows of columns that may be added to the rest of the sheet.
Named ranges move as well, so that's no use. At the moment the only thing I can see working is to cut the data and paste it one row down, which as the data set is growing by one row a month, is not a fixed target either.
Any ideas?
Marco.
what about giving a name to rows/range and use that name in your formulas? worst case you will re-difine name...
or use a range from start to end , which first 10-12 has rows (refer to 1000 rows in your formulas) and arrange the formulas/script according to these... loop until row is empty... so you will not have any issue, say until you have 1000 records...
or use a range from start to end , which first 10-12 has rows (refer to 1000 rows in your formulas) and arrange the formulas/script according to these... loop until row is empty... so you will not have any issue, say until you have 1000 records...
ASKER
Hi,
The blank row idea doesn't work because it just makes the selection bigger, and I just want 12 rows.
Ranges seem to behave the same as absolute references, and shift down and I can't do the 1000 rows because the spreadsheet has to be used by an admin person who will want to keep all the data from rows 13-1999 as they build up.
Any more ideas anyone?
The blank row idea doesn't work because it just makes the selection bigger, and I just want 12 rows.
Ranges seem to behave the same as absolute references, and shift down and I can't do the 1000 rows because the spreadsheet has to be used by an admin person who will want to keep all the data from rows 13-1999 as they build up.
Any more ideas anyone?
You have to adjust your formulas if you do not want a blank row to help you. While I admit it is a low-brow way to do things, I use the idea all the time because it saves me time.
Can you not change the habits of the admin person? Otherwise, you need to change formulas when you add a row. ... Thinkpads_User
Can you not change the habits of the admin person? Otherwise, you need to change formulas when you add a row. ... Thinkpads_User
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
From Excel Help (I am quoting it): Returns the reference specified by a text string. References are immediately evaluated to display their contents. Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself.
Useage is: Indirect(ref_text,a1) Returns the reference specified by a text string.
I don't know if that will help here or not. ... Thinkpads_User
Useage is: Indirect(ref_text,a1) Returns the reference specified by a text string.
I don't know if that will help here or not. ... Thinkpads_User
ASKER
If you want a cell reference to stay nailed to the same row/column, regardless of adding or deleting rows and columns, use INDIRECT("C" & "3") instead of C3 or $C$3.
.... Thinkpads_User