Find and replace with differing outputs

[tony7262]

Hi,

I'm in the process of setting up a new forecasting model and will have a summary that pulls input together from 50+ other countries individual tabs.
Every country tab will be identical in layout but obviously with differing numbers. And I would like the summary to pull from each of the tabs but obviously would like to avoid linking for each country.
In an ideal world I could link up one country, copy the formula over to all the other countries and then run a find and replace that changes the sheet reference from the initial country to whichever is required (attachment will hopefully explain this a bit better)
I tried using indirect formulas that do work with a list of the countries but the spreadsheet is becoming so slow as a result would rather avoid it if I could

Anyone got any ideas?

Thanks in advance,
Tony

[dangelor]

Deleted...

[oeldere]

How about all data on 1 sheet (e.g. with VBA) and after that a pivot table?

Edit