Excel is changing my formula for no reason. Can someone explain
Posted by adam on April 18, 2001 12:20 PM
I have two workbooks. One has data and the other links to that data.
On sheet 2 the formulas look like this
=INDEX([Book1.xls]Sheet1!$A$1:$M$10000, MATCH(ramsecname, [Book1.xls]Sheet1!$A$1:$A$10000, FALSE),7)
This is a basic lookup of sheet one's values.
When I delete cells A10:M10000 on sheet 1, the formulas on sheet 2 change to
=INDEX([Book1.xls]Sheet1!$A$1:$M$9, MATCH(ramsecname, [Book1.xls]Sheet1!$A$1:$A$9, FALSE),7)
I dont want this to do this because I rebuild sheet one and I want the links to stay. I cannot CLEAR instead of DELETE because I need all the rows on sheet 1.
Can anyone tell me why Excel changes my formula and how I can keep Excel from doing this ??
Thanks,
Adam
Posted by Aladin Akyurek on April 18, 2001 12:32 PM
Adam
I think that behavior is to be expected. I understand that the consequence thereof is hindering you, in that when you refill the range with data, the formula ignores the new data. You can overcome the problem by creating a named dynamic range on Sheet1 of Book1.xls and use that name in your formula. Be warned that, because you have to make use of a volatile function (d.i. OFFSET), your spreadsheet will re-calculate often.
Aladin
Posted by adam on April 18, 2001 1:30 PM
Aladin, (or anyone else)
Can you please explain what you are talking about, maybe an example> I dont understand what you mean when you say create a volatile formula.
Thanks,
Adam
Posted by Aladin Akyurek on April 18, 2001 1:48 PM
Adam
My suggestion is:
Activate Sheet1. Activate the option Insert|Name|Define. Type DATA for "Names in workbook". Type the following formula as "Refers to":
=OFFSET(Sheet1!$A$1,0,0,COUNTA($A:$A),13)
Now, you can replace the Sheet1!$A$1:$M$10000 by DATA. If you delete any row in the range and add new data to the range, the formula will still work.
OFFSET is a so-called volatile function. Any change anywhere in your workbook, will cause a recalc in order to determine the changes to the range referred to by data. You might thus experience some slow-down wrt your spreadsheet model.
Aladin
Posted by Big Bob on April 19, 2001 8:15 AM
Suggestion from Big Bob
Before you delete the range could you edit the formula so that it is no longer a formula but text .Then when you have finished rebuilding Sheet 1 you could change it back to a formula
All you need to do is add some text before the equals sign then Excel will not recognise it as a formula
HTH
Posted by adam on April 19, 2001 9:48 AM
I did this and it works. The only problem is that my sheets seem to take for ever to calculate now. Is this a side effect of this action. Is there anyway else that would not have this effect.
thanks
adam
Posted by Aladin Akyurek on April 19, 2001 10:45 AM
Another way...
Adam
Yes, it's the the effect of OFFSET. I want to make another suggestion. Insert 2 rows before the cell where your data starts.
In A1 enter: ="Sheet1!"&ADDRESS(3,1)&":"&ADDRESS(COUNTA(A:A),13)
You may name cell A1, e.g., DRANGE via the Name Box.
In your formula, you can now use, if I'm not mistaken, the name DRANGE in combination with INDIRECT.
....INDIRECT(DRANGE)....
I'm not sure whether this will give you a better performance. One thing that I hope is that there wil not be "extraneous" re-calcs, because determining the data range is restricted to a sinle sheet. I'd appreciate if you could test this hypothesis.
Take also a look at Big Bob's suggestion. It might be a better way.
Aladin
Posted by Aladin Akyurek on April 19, 2001 1:57 PM
A better solution... due to MarkW
