Excel formula help

[GimmeSlack12]

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)

 

[Tomorrow]

I have about 30 worksheets each with their own data.

I'm trying to get everything on to one worksheet

You can't do a simple copy and paste? 😕

Maybe I don't understand the problem.

 

[GimmeSlack12]

You can't do a simple copy and paste? 😕

Maybe I don't understand the problem.

The problem when I c&p the 'Address 1' remains 'Address 1' and doesn't update to 'Address 2' 'Address 3' and so on. Thats where the problem lay.

 

[GGJstudios]

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​

 

[GimmeSlack12]

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.

 

[GGJstudios]

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:

​

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.

 

[GimmeSlack12]

Start with a column of the numbers you want, then use the CONCATENATE function to create the formulas:

View attachment 256853​

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

 

[GGJstudios]

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

See the last line of my post that I just added.

 

[GimmeSlack12]

See the last line of my post that I just added.

Duh. I knew I was missing something right in front of me. Thanks for helping out Studios!

 

[GGJstudios]

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.

 

[GimmeSlack12]

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.

Ha, VLOOKUP and HLOOKUP are infinitely useful!