Can't understand this INDIRECT formula

[wongc61]

Hello Excel Wizards! I have a tough question. I'm working with a former colleague's spreadsheet and it is FULL of this formula:

=INDIRECT("'"&A10&"'!c7") (exactly as typed)

It appears that this formula is trying to reference a cell or something, as the INDIRECT function does, but I cannot figure out what the ref_text or logical value is in this formula.

Please help. If you need more info or clarification, I'll try my best to give it.

Thanks!!

[NBVC]

The person is referencing cell A10 which should contain a sheetname in the workbook and then it pulls whatever is in C7 from that sheet.

See here: http://www.contextures.com/xlFunctions05.html#RefSheet

[wongc61]

Ah! The double quotations are used to make the reference a SHEET NAME! I see it now (i think). Man, does this help a lot. Thank You Very Much!!

[NBVC]

A sheetname in a formula is usually referred to like this:

Sheet1!

If the sheet name contains multiple words you need to enclose in single quotes like:

'Sheet One'!

The single work can also take the single quotes: 'Sheet1'!

in the formula above, it is concatenating a single quote, the string in A10 and then another single quote. The double quotes around the single quote is simply defining the single quote as a text string and on the other side it combines the single quote that closes the sheetname, along with the ! that defines it as a sheetname and the cell reference... and that is all hard-coded... the only variable being A10.

So it takes " ' " & A10 & " '!C7"