Excel – Dynamic Sheet Reference in Excel Formulas

microsoft excelworksheet-function

This is my spreadsheet:

enter image description here

I tried replacing the term Benco in the formula with the cell reference hoping I could drag the formula down and it would use the contents of the row containing the dealer names (which are the same as names of different tabs on my workbook). This doesn't work, Excel prompts me to locate the sheet titled "D4"… is there a way to use the contents of a cell to populate the sheet reference in an excel formula?

This would be really cool.

Best Answer

Yup, use the reference of the cell with INDIRECT:

Let's say Benco is in cell er... A2 (can't see your table's headings):

=SUMIF(INDIRECT("'"&A2&"'!C:C"), ...)

Which you can drag down.

Related Question