Using ADDRESS in INDIRECT

[Kiriko]

Hi


[TABLE="width: 915"]
<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>[TR]
[TD="width: 1220, bgcolor: transparent, colspan: 5"]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.[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 5"]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[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 3"]The ADDRESS formula works but I can't get INDIRECT to read it as a cell ref, see below details:[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]FX[/TD]
[TD="bgcolor: transparent"]Written Out[/TD]
[TD="bgcolor: transparent"]With Cell references[/TD]
[TD="bgcolor: transparent"]Returns[/TD]
[TD="bgcolor: transparent"]Purpose[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]INDIRECT[/TD]
[TD="bgcolor: transparent"]INDIRECT(Sheet name & "! Total Cell Ref ")[/TD]
[TD="bgcolor: transparent"]INDIRECT(A1&"!B6")[/TD]
[TD="bgcolor: transparent"]79[/TD]
[TD="width: 402, bgcolor: transparent"]Returns the amount in the total row which is located in cell B6 in sheet 1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]ADDRESS[/TD]
[TD="bgcolor: transparent"]ADDRESS(Row number, Column number)[/TD]
[TD="bgcolor: transparent"]ADDRESS(6,2)[/TD]
[TD="bgcolor: transparent"]B6[/TD]
[TD="width: 402, bgcolor: transparent"]Returns in text value the cell address or reference to the cell containing the total for sheet 1 data[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]MATCH[/TD]
[TD="bgcolor: transparent"]MATCH(Lookup value,lookup array,0)[/TD]
[TD="bgcolor: transparent"]MATCH("Total",INDIRECT(Sheet 1&"!A:A"),0)[/TD]
[TD="bgcolor: transparent"]6[/TD]
[TD="width: 402, bgcolor: transparent"]Returns the row number to be used in Address fx[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]MATCH[/TD]
[TD="bgcolor: transparent"]MATCH(Lookup value,lookup array,0)[/TD]
[TD="bgcolor: transparent"]MATCH("2018",INDIRECT(Sheet 1&"!3:3"),0)[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="width: 402, bgcolor: transparent"]Returns the column number to be used in Address fx[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 2"]So I want to do something like:[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 4"]INDIRECT(A1&"!ADDRESS(MATCH("Total",INDIRECT(Sheet 1&"!A:A"),0),MATCH("2018",INDIRECT(Sheet 1&"!3:3"),0))")[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 3"]Where my end result should be 79 which is located in Sheet 1 Cell B6[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]BUT:[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 2"]Excel will only accept[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 4"]INDIRECT(A1&ADDRESS(MATCH("Total",INDIRECT(Sheet 1&"!A:A"),0),MATCH("2018",INDIRECT(Sheet 1&"!3:3"),0)))[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 2"]And it gives me a #REF error[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 2"]Any ideas would be appreciated[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

See if this works
Assumes the sheet name in A1 doesn't contain spaces (like Sheet1 for example). Otherwise you should use single quotes around A1.

=INDEX(INDIRECT(A1&"!A:Z"),MATCH("Total",INDIRECT(A1&"!A:A"),0),MATCH("2018",INDIRECT(A1&"!3:3"),0))

M.