Hi
<colgroup><col width="99" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3620;">
<col width="279" style="width: 209pt; mso-width-source: userset; mso-width-alt: 10203;">
<col width="289" style="width: 217pt; mso-width-source: userset; mso-width-alt: 10569;">
<col width="151" style="width: 113pt; mso-width-source: userset; mso-width-alt: 5522;">
<col width="402" style="width: 302pt; mso-width-source: userset; mso-width-alt: 14701;">
<tbody>
</tbody>
| I need to extract the totals of separate sheets to a summary sheet in one workbook. The total row is not always on the same row in every sheet although the set-up is basically the same. | ||||
| I have tried to use a combination of INDIRECT & ADDRESS but the required information needs to be referenced both by row and column so I nested MATCH into the ADDRESS formula | ||||
| The ADDRESS formula works but I can't get INDIRECT to read it as a cell ref, see below details: | ||||
| FX | Written Out | With Cell references | Returns | Purpose |
| INDIRECT | INDIRECT(Sheet name & "! Total Cell Ref ") | INDIRECT(A1&"!B6") | 79 | Returns the amount in the total row which is located in cell B6 in sheet 1 |
| ADDRESS | ADDRESS(Row number, Column number) | ADDRESS(6,2) | B6 | Returns in text value the cell address or reference to the cell containing the total for sheet 1 data |
| MATCH | MATCH(Lookup value,lookup array,0) | MATCH("Total",INDIRECT(Sheet 1&"!A:A"),0) | 6 | Returns the row number to be used in Address fx |
| MATCH | MATCH(Lookup value,lookup array,0) | MATCH("2018",INDIRECT(Sheet 1&"!3:3"),0) | 2 | Returns the column number to be used in Address fx |
| So I want to do something like: | ||||
| INDIRECT(A1&"!ADDRESS(MATCH("Total",INDIRECT(Sheet 1&"!A:A"),0),MATCH("2018",INDIRECT(Sheet 1&"!3:3"),0))") | ||||
| Where my end result should be 79 which is located in Sheet 1 Cell B6 | ||||
| BUT: | ||||
| Excel will only accept | ||||
| INDIRECT(A1&ADDRESS(MATCH("Total",INDIRECT(Sheet 1&"!A:A"),0),MATCH("2018",INDIRECT(Sheet 1&"!3:3"),0))) | ||||
| And it gives me a #REF error | ||||
| Any ideas would be appreciated |
