Dynamic Sheet Name

Hi guys,

I wondered if you could help my shorten this task. I've tried an Indirect formula but struggling to get it to work. I'm using the below formula.

=IF('[B5.xlsm]Week 1'!$S$14="","","='[B5.xlsm]Week 1'!$S$14")

I'm pulling numerical data from one workbook (B5.xlsm) to another to plot it. The source workbook has 52 sheets for each week. I can't seem to get the correct syntax for use with an Indirect formula. In the destination workbook, I have cells with the week already in place, a sole numerical integer beginning with "1" in B47 and working downwards.

Any advice? I essentially want to be able to not have to manually change each integer to the correct week as there's a lot of data to do it for, I want to be able to drag it down.

I've attached some data I've copied out of it. If you look at B47, I essentially want to be able to drag that down, incrementing the sheet name.

For this particular sheet, there's only one column, named "Metronidazole 5", however some sheets have several columns so it'll become incredibly tedious to have rename them all.

I've attached the bit that translates to the destination one I've already attached. The essentially premise of it is it acts as a form for the submission of some data which is then stored in some cells, hidden to the right, which are the cells that I will access from the destination workbook.

This is a little of an older version as the latest version is at work, but the main notable differences is this one doesn't have all 52 sheets, but the premise is notable, there will be 52 exact copies of this sheet, week 1, 2, 3, 4 etc.

Originally Posted by torachan

Why, Oh!, Why - 52 sheets ????? why not use one 'structured table' as a pseudo database - populated by data entered into a proper UserForm - that can be recalled to populate a report template. this requires minimum 2 sheets maximum 3 sheets.

Is what it is now and not the issue I'm looking to solve. I'm not going to go back to scratch after a lot of work went into it. It had to replace an existing, more traditional system and had to mimic it as closely as possible, which it achieves. If you'd like to suggest a solution to the question at hand, I'm all ears.