CODEWITHSUNDEEP
excel
Ted
Ted

Reputation: 27

Use a value in a cell as part of a sheet reference

Hopefully this is simple and clear enough:

Sheets in spreadsheet are months: 01 ; 02 ; 03 ... 12

I have a chart output for all the sheets (months) that references specific cells in each sheet: 

January      February     
='01'!$M$22   ='02'!$M$22   ...

When I add a new month I am copying the formula and replacing the month number in the formula. How can I write a formula that references a value in a cell for that month.

             January                February     
             ='Cell Value'!M$22$    ='Cell Value'!M$22$
Cell value    01                    02                      ...

I want this so I can simply carry over the formula when I add a new month and not have to manually change the sheet value (01, 02, 03 ... 12)

Thanks for any insight.

Upvotes: 0

Views: 83

Answers (1)

Grade 'Eh' Bacon
Grade 'Eh' Bacon

Reputation: 3823

This is simply done with the INDIRECT() function.

Give INDIRECT the address of a cell (calculated by simplifying an expression, such as pulling your sheet name string), and it resolves as the directed cell value.

So to get M22 of a given sheet, with sheets named "01"-"12" for months, type:

=INDIRECT($C$4&"!$M$22")

EDIT - Note - I forgot the "!" in the first instance of this formula

Also I've removed other methods of getting the sheet name, which will not work as your code is on a summary tab, not the sheet itself.

Upvotes: 1

Related Questions