Changing cell references on a sheet by changin a cell value
Posted by Patrick on February 12, 2001 4:48 PM
I read some comments on how to concatenate using the INDEX formula. I would like to change a cell and have it change a refernce in a formula. Let's say that the value on sheet 1 at cell A1 is c93 and cell A2 equals C130, if I change cell A1 to D93 and cell A2 to D130 then the formula would automatically change from:
='Bound inside 25.5 lb'!C93-('Bound inside 25.5 lb'!C130*('Bound inside 25.5 lb'!C7/1000))
To:
='Bound inside 25.5 lb'!D93-('Bound inside 25.5 lb'!D130*('Bound inside 25.5 lb'!C7/1000))
Basically I want to change a cell value rather than have to edit all 200 rows to change the reference.
Any suggestions?
Posted by Mark W. on February 12, 2001 9:38 PM
Patrick, it's not the INDEX() function, but rather
the INDIRECT() function that you seek. Try:
=INDIRECT("'Bound inside 25.5 lb'!"&A1)-INDIRECT("'Bound inside 25.5 lb'!"&A2)*('Bound inside 25.5 lb'!C7/1000)
Posted by Patrick on February 13, 2001 8:02 AM
Mark,
Thank you very much for your assistance, this seems to work very well.
Patrick
