Become a MacRumors Supporter for $50/year with no ads, ability to filter front page stories, and private forums.

GimmeSlack12

macrumors 603
Original poster
Apr 29, 2005
5,406
14
San Francisco
I really am at my rope's end on this. I downloaded my vibration data from tons of measurements and it puts everything in its own Worksheet. So for one file I have about 30 worksheets each with their own data.

I'm trying to get everything on to one worksheet and am doing this by simple cell references example: ='Address 1'!A1

Now to make my life easier I want to be able to have the Worksheet number a variable so I can do a 'fill right' and all the data is pulled from each sheet. I have a summary worksheet that has the measurement numbering that I want to reference, this row is just numbers (1, 2, 3...).

What I want is: ='Address <row number>'!A1

This will allow me to do a fill right and the worksheet names will fill in and pull the correct data. Help!
(please ask if this doesn't make sense)
 
If I understand correctly, you want to create formulas, where each formula refers to a different worksheet, which are numbered? Like this?
Row:.......Column A
1........='Worksheet 1'!A1
2........='Worksheet 2'!A1
3........='Worksheet 3'!A1
4........='Worksheet 4'!A1
5........='Worksheet 5'!A1​
 
If I understand correctly, you want to create formulas, where each formula refers to a different worksheet, which are numbered? Like this?
Row:.......Column A
1........='Worksheet 1'!A1
2........='Worksheet 2'!A1
3........='Worksheet 3'!A1
4........='Worksheet 4'!A1
5........='Worksheet 5'!A1​

Correct. That's what I want. I looked at the INDIRECT function but got confused, and I don't have the time for VBA at the moment.
 
Correct. That's what I want. I looked at the INDIRECT function but got confused, and I don't have the time for VBA at the moment.

Start with a column of the numbers you want, then use the CONCATENATE function to create the formulas:
ScreenCap 7.PNG
Create the first formula, then drag to copy to the other rows.
When you're done creating them, copy them and Paste Special > Values.
You may have to click in the formula bar for each one and press enter to make the created formula active.
 
Start with a column of the numbers you want, then use the CONCATENATE function to create the formulas:
Create the first formula, then drag to copy to the other rows.
When you're done creating them, copy them and Paste Special > Values.

That works great, but I actually want the value from the specified worksheet. I was hoping to get "865.55" instead of ='Address 1'!K5
 
Duh. I knew I was missing something right in front of me. Thanks for helping out Studios!

Happy to help! I added it to my post late, which is why you didn't see it before. Concatenate and VLOOKUP are two of my favorite Excel formulas.
 
Register on MacRumors! This sidebar will go away, and you'll see fewer ads.