Have a link with a Cell Reference

[grimm33184]

Hi All,

I Have this link below in my spreadsheet. It's in Cell C5 in this example. It is referencing a report with the monthly sales for the third qrt broken down by month. I have several of these links and every month I have to change each link to refer to another Cell from that report. It is the same cell reference but in different tabs. Which means I have to go to each link and manually update it with the new cell reference.

I would like to simplify this but having a cell (for this example in C2 in the spreadsheet) with the reference of C$46 and have the link reference that cell. So all I need to do is change the cell value in C2 and it will update all my links. Any Ideas?

='[Q3 GSV FY13 1-18-13.xlsb]Joe Plumber'!C$46

Thanks in advance!

[Pauleyb]

Something like:
=INDIRECT("'[Q3 GSV FY13 1-18-13.xlsb]Joe Plumber'!"&C2)

[grimm33184]

It didn't work... I got a #REF.

Also Tired:
=INDIRECT("[Q3 GSV FY13 1-18-13.xlsb]Joe Plumber'!")&O7

[Pauleyb]

Hmmm, I tried it on a sample of mine and it worked...
What, exactly, did you try that gave the #REF? Both for the equation I provided and in your C2 cell.
Your second attempt will definitely not work.

[grimm33184]

Here is the whole link:
='R:\DSFGFin\FY 2013\New Wave GSV SS Templates\[Q3 GSV FY13 1-18-13.xlsb]Macy''s'!L$46

I usually shorten the length to keep things simple so I can repeat it for future endevours.

The cell reference is still C2.

[Pauleyb]

I'm looking for the INDIRECT statement you used that caused the REF error. I'm thinking you left a ' or " out of it.

[grimm33184]

=INDIRECT("'R:\DSFGFin\FY 2013\New Wave GSV SS Templates\[Q3 GSV FY13 1-18-13.xlsb]Macy''s'!"&C2)

Here you go.

[Pauleyb]

I don't see anything immediately wrong with it. You do have the 'Q3 GSV FY13 1-18-13.xlsb' file open, correct? INDIRECT will only work when the file is open.

What is in C2? L$46, I assume.

[grimm33184]

Bingo! THat's it! Once I open the linked file its all good! Thank you so much. It is very good to learn this and thank you for showing it to me!