Changing excel formula to read same cells in different tabs

[Petehab]

Hi there,

I have created a template with session 1, 2 and 3 for my athlete's weekly schedules to be input for each week of the year. These are split into 4 weekly blocks in which I have 1 tab for each. i.e. Tab1 would contain weeks 1-4, tab2 contains week 5-8 and so on...

I am now creating a flat list to collect all the data from each tab into 1 table. Given each weekly template is the same all I need to do is change the tab name in the formula. For example I need to change '='Week Plan 1'!B$5' to '='Week Plan 2'!B$5'. I have A LOT of data to do this on and wondered if there was a macro I could run which would do this for me?



Thanks,

Peter

 

[Oscar Cronquist]

You can change the sheet name using Find and Replace.
Press CTRL + H to open "Find and Replace" dialog box.
Click Options to show more settings.
Change Look in: Formulas

 

[Petehab]

You can change the sheet name using Find and Replace.
Press CTRL + H to open "Find and Replace" dialog box.
Click Options to show more settings.
Change Look in: Formulas

Of course! Brain has officially turned to much, why didn't I think of that?! Thanks all the same