CODEWITHSUNDEEP
excelexcel-formulaworksheet-function
ChickenWing
ChickenWing

Reputation: 703

Unsure of how to use cell data to get data from another worksheet

This is a weird sort of function that I've never tried to use before, so I'm not sure how to go about asking without using an example.

Essentially, I have a column of strings that represent datasheets in this .xls file. Each row has data in it that corresponds to the datasheet named in said column. In case my explanation is insufficient, here's an example:

Worksheet 1 : Worksheet 1 Value 1 : Worksheet 1 Value 2 : .......
Worksheet 2 : Worksheet 2 Value 1 : Worksheet 2 Value 2 : .......
etc...

At the moment, the spreadsheet requires you to manually update the worksheet reference for each column to populate the data, i.e. for every cell, I need to manually fill out the worksheet name in this equation:

='Worksheet 1'!B111

This works, but is obviously suboptimal, as I have a large number of worksheets. What I WANT to do is be able to do is be able to fill out Cell 1 (the worksheet name) and have every other cell use the worksheet name as its reference, like so:

Column A      : Column B : Column C : ...
"Worksheet 1" : =A1!B111 : =A1!B34  : ...
"Worksheet 2" : =A2!B111 : =A2!B34  : ...

First question: Is this possible?

Second quesion: How?

Sorry if this has been answered already, but I could barely figure out how to ask the question in the first place.

Upvotes: 0

Views: 2522

Answers (1)

teylyn
teylyn

Reputation: 35990

You can use the INDIRECT() function. With the text Worksheet 1 in cell A1 you can use

=indirect("'"&$A1&"'!B111")

Since the sheet name can contain spaces, you need to start the text of the Indirect with a single quote, append the cell with the sheet name, then append the closing single quote, the exclamation mard and finally the cell reference.

If all cells in column A refer to B111, just copy the formula down and change the sheet name in column A. Copy the formula across and adjust the cell reference for B111 to whatever is desired before copying down.

If you want to pre-populate a worksheet with these values, fire up the macro recorder, create a new sheet, go to an existing sheet and copy and paste everything into the new sheet, then stop the macro recorder. That will give you a starting point for the code you need.

Upvotes: 1

Related Questions