MrExcel.com - Your One Stop for Excel Tips & Solutions

Indirect References

Posted by Andy Cantrell on July 09, 2001 10:46 AM

I have the following
Summary!A10 =SUM(Weekly!D1:Weekly!G1)

If I set
Summary!A9 "Weekly"

Can I then use the value from Summary!A9 in Summary!A10 ??
The following:
Summary!A10 =SUM(INDIRECT(A9&"!D1"):INDIRECT(A9&"!G1"))
works, but I loose the ability to easily shift columns around
under the Weekly sheet. Is there a better way to do this?

Posted by Aladin Akyurek on July 09, 2001 11:05 AM

Andy,

The formula can be a bit shorter: =SUM(INDIRECT(A9&"D1:G1")) in case you put "Weekly!" in A9. If you prefer having "Weekly" in A9, then the formula becomes: =SUM(INDIRECT(A9&"!"&"D1:G1")).

Back to your real question: As I understand it, you want be able to have to change the D1:G1 on the sheet Weekly. In that case, name the range e.g., Data via the Name Box and use this name in the formula:

=SUM(INDIRECT(A9&!"&"Data"))

Now, you have some flexibility to change the cell refs within the range Data refers to.

Aladin

Posted by Andy Cantrell on July 09, 2001 10:06 PM

Got it. Thanks Aladin.