MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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))
='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

Thank you very much for your assistance, this seems to work very well.