Link sheets automatically

**rsamboragal** · 11-04-2009, 02:34 PM

Hello all! I'm trying to set up a workbook at my office. Basically, I have a master spreadsheet that is a summary of subsequent sheets. I want to be able to enter info into the sub-sheets and have them automatically populate the master sheet. I figured out how to do this BUT while creating this workbook, I'm wondering: is there a way to set the master sheet up without having to manually change the reference sheet name in each cell? For instance,

Say I have cell A1 on the master sheet linked to Cell D1 on Sheet A (So, cell A1 on the master sheet will have the formula =SheetA!$D$1)
I want cell A2 on the master sheet to link to Cell D1 on Sheet B (=SheetB!$D$1)and cell A3 on the master sheet to lint to Cell D1 on Sheet C (you get the idea) and so on... Is there a way to do this quickly? Like dragging...(That obviously doesn't work or I wouldn't be here asking) or do I have to manually change the sheet name for every reference? I have several columns that I have to reference back to different sheets and to manually put each in is ridiculous. It defeats the whole purpose...
Thanks for any help!!

Thank you!!

**JBeaucaire** · 11-04-2009, 03:30 PM

If the sheets are really called SheetA, SheetB, etc, then that's a bit of a problem. Excel can use ROW() and COLUMN() functions to increment values numerically quite easily as a formula is copied down/across. Slip those into an INDIRECT() formula and you're in business.

For instance, if A1 were to return D1 from Sheet1, the normal formula would be:
=Sheet1!D1

In an INDIRECT(), it would change to:
=INDIRECT("Sheet" & ROW() & "!D1")

The ROW() = 1 if the formula is in row1, so that would resolve back to the original formula above.

Now, if you copy that down, the next ROW() part would resolve to "2" so the reference would be Sheet2...and so on.

If you can name your sheets numerically/sequentially, then this takes care of it.

NOTE: INDIRECT() is a volatile function, so you don't want 1000s of these formulas if you can avoid it. You'll notice performance issues on your sheet if you do. But in reasonable doses, it's fine.

**rsamboragal** · 11-04-2009, 04:00 PM

Thank you so much for your response. The sheets are actually named the last name of each customer. I suppose that would pose a problem given what you explained. I do think I have a solution for that, so...I'll try it! Thank you so very much!

"Dammit Jim! I'm a pre-med student not an office assistant! (Okay I'm an office assistant to pay for med school...)"

**rsamboragal** · 11-04-2009, 04:03 PM

Nope. Still confused. I think I'll just solve this by throwing the computer away. There. All better.

Seriously though...I'm really stuck. Can you pretend I'm 5 and walk me through it? My brain hurts.

**rsamboragal** · 11-04-2009, 04:19 PM

Originally Posted by JBeaucaire

Excel can use ROW() and COLUMN() functions to increment values numerically quite easily as a formula is copied down/across.

But can it increment the values for the Sheet names? In other words, if I have "Sheet26" "Sheet27" "Sheet28" etc...Can I go to cell "A1" on my master sheet and put in a formula, (=Sheet26!$D$1) and then drag it down so that it automatically increments the Sheet number (=Sheet27!$D$1) etc?
I don't understand the "row" example you gave me. I don't want the row number to change. Just the Sheet number.

**JBeaucaire** · 11-04-2009, 05:32 PM

Originally Posted by rsamboragal

Can I go to cell "A1" on my master sheet and put in a formula, (=Sheet26!$D$1) and then drag it down so that it automatically increments the Sheet number (=Sheet27!$D$1) etc?

The INDIRECT() would be the only way I know to do this easily. Perhaps this is easier to read, in A1:

=INDIRECT("Sheet" & Row(26:26) & "!D1")

At least in that version, you can see the "26" in there. If you copy THAT down, the next value would equate to 27, etc.

**rsamboragal** · 11-05-2009, 11:02 AM

IT WORKED!!!!!!!!!!!!!!!!!!!!!!!!!!

**doing a little dance**

IT WORKED IT WORKED IT WORKED!!!!!!!!

Thank you thank you thank you soooooooooooo MUCH!!!!!

**JBeaucaire** · 11-05-2009, 11:06 AM

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)