Need help with referencing totals from multiple worksheets

[mgdll]

I've been looking around forums and videos for this solution and I'm sure there's something super obvious I'm missing but I just can't figure out how to do this.

I have a document with 35 different worksheets. Each worksheet has the same table in the same position and there is a cell that contains the total value of each sheet. What I'm trying to do is create a summary page with various data points from the sheets. One is a table that shows the total for each sheet. I can do this manually and type = and then click the tab, click the cell and enter. But there are 30+ sheets. I tried doing it for the first three sheets and then dragging the autofill but that didn't work. All solutions I've seen are really to sum all the totals and have only 1 cell with the totals. That's not very useful in this case.

Thanks in advance.

 

[Fluff]

Hi & welcome to MrExcel.
If all the sheets consecutive you could use something like

=vstack(Sheet1:Sheet10!B30)

 

[mgdll]

Hi & welcome to MrExcel.
If all the sheets consecutive you could use something like

=vstack(Sheet1:Sheet10!B30)

Hi, I'll try it but all the sheets are named by country. Not sure if that makes a difference. Thanks for the response

 

[Fluff]

You just need to change the sheet names to suit & the cell reference.

 

[mgdll]

You just need to change the sheet names to suit & the cell reference.

I tried it. It seems like I have to do it 35 times unless I'm missing a step? I was looking for something like copying one one data point from another sheet and then just dragging the plus to fill the rest but for different sheets.

 

[Fluff]

You just do it once & it will spill across.
What is the formula you used?

 

[mgdll]

You just do it once & it will spill across.
What is the formula you used?

Ok, IT WORKED!!!! Thank you so much.

=VSTACK('Collection Data:Venezuela'!L1)

That was the formula I ended up with.

Again, thank you

 

[Fluff]

Glad you sorted it & thanks for the feedback.