Excel question

[crocogotter]

This is not specifically to do with BA, but for my analysis of results, so hopefully it's OK to ask here. I'm collating results from various worksheets within a workbook into a mastersheet. Each of the worksheets that I'm extracting data from is simply named by a number (1, 2, 3 etc).

Is there a way to generate the worksheet name so I can copy formula on the mastersheet rather than having to manually adjust each formula for each individual worksheet?

I realise this might not be very clear, so I'l give an example.

As I say, each worksheet is called 1, 2, 3 and so on. I want to extract data from, say, cell A1 in each worksheet for the mastersheet. I have a column of numbers in column A of the mastersheet, starting at 1. Column B is the extracted data. Thus, the formula for cell B1 is ='1'!A1 (cell A1 on mastersheet is 1), and for B2 is ='2'!A1 (cell A2 is 2), B3 is ='3'!A1 (cell A3 is 3) and so on. So effectively I want to generate the worksheet name in a formula using the numbers in column A to extract the data from the relevant worksheet.

I hope this makes sense.

If this can be done I'm sure it's quite straighforward but so far it's eluding me and my googling abilities.

[Deestar]

I think this formula in col B your Mastersheet should work if I've read your question correctly. Where the first A1 gives the name of the worksheets. This would obviously drag down and become a2, a3....etc

=INDIRECT(CONCATENATE("'",A1,"'", "!A1"))

It will only give the value in A1 in each worksheet 1,2,3 etc.

[bolpx001]

Select the required sheets by pressing ctrl and clicking the required sheet names
Enter your formula on the first sheet. And it should be in all selected sheets
I hope it helps Paul

[crocogotter]

Thanks Deestar, that's exactly what I wanted.

[bolpx001]

Sorry Folks, totally misread the question