Pulling data across sheets to a summary sheet

Mistaree

New Member
Joined
Sep 2, 2014
Messages
2
Hello,

My workbook has 100+ sheets and I am trying to pull three particular data points from each sheet to a summary sheet. The data points I am trying to pull from each sheet are located in the same cells on each sheet, e.g. Total 2015 is in cell G40, Total 2020 is in G41 and Total 2025 is in G42 in every single sheet. Now in my summary sheet Column E ('Countries) has the name of all my sheets, e.g. Sweden, Norway, Finland, and in Columns F, G and H, I would like to retrieve the Total 2015, Total 2020 and Total 2025 from each sheet. Is there a simple formula for this or should I create a macro? I have been Googling for two days and getting more confused, but it feels like there should be a pretty straightforward solution, or? Any help would be very much appreciated.

Here's a little illustration of what I am aiming for

Countries Total 2015 Total 2020 Total 2025
Sweden 150 200 250 --> These three data points pulled from cells G40-G42 in Sheet "Sweden"
Norway 100 150 175 --> These three data points pulled from cells G40-G42 in Sheet "Norway"
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Thank you very much!

A follow-up question would be if I can combine this with an IF function. In Coulumn E with all countries, I have countries entered that do not have a separate sheet. By using an IF function I'd like Excel to give me "n/a" for those entries where there is no underlying work sheet for that particular country.
 
Upvote 0

Forum statistics

Threads
1,223,308
Messages
6,171,331
Members
452,396
Latest member
ajl_ahmed

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top