Link to home
Start Free TrialLog in
Avatar of Matthew Gaver
Matthew GaverFlag for United States of America

asked on

Incorporate INDIRECT into a larger formula

J1741_VisitTracker-_05Dec18_EE.xlsmHello!  I have the following formula which works no problem:

=IFERROR(OFFSET(‘SheetXX’!$E$5, MAX(IF(NOT(ISBLANK(‘SheetXX’!$E$5:’SheetXX’!$E$34)), ROW(‘SheetXX’!$E$5:’SheetXX’!$E$34),0))-ROW(‘SheetXX’!$E$5),-2),"")

It looks at a sheet, finds the last date entered in a column, and returns the value of the cell 2 to the left of that last date.
What I'd like to do is refer to SheetXX using an Indirect reference, which by itself, also works no problem

=INDIRECT("'"&$K$6&"'!$A$6")

so if the Indirect formula returns the correct sheet name, I thought it would make sense to replace 'SheetXX', with the indirect formula exactly as written.  
That's not working, no matter how many semi-coluns I add or change places in:

IFERROR(OFFSET(INDIRECT(“‘“&$K$6&”‘!$A$6”)!$E$5, MAX(IF(NOT(ISBLANK(INDIRECT(“‘“&$K$6&”‘!$A$6”)!$E$5:INDIRECT(“‘“&$K$6&”‘!$A$6”)!$E$34)), ROW(INDIRECT(“‘“&$K$6&”‘!$A$6”)!$E$5:INDIRECT(“‘“&$K$6&”‘!$A$6”)!$E$34),0))-ROW(INDIRECT(“‘“&$K$6&”‘!$A$6”)!$E$5),-2),"")

Any help is apprecaited.

Thankks
Matt
Avatar of Norie
Norie

Matt

What does =INDIRECT("'"&$K$6&"'!$A$6") return?
Avatar of Matthew Gaver

ASKER

It returns the value of the sheet name I want to reference in the other formula (Because the sheet name is based of the value of the cell in A6)
I'll upload a copy of the spreadsheet later if it helps.  I just need to de-identify some stuff.
Matt

I think you are going to need another INDIRECT.

For example to refer to E5 on the sheet INDIRECT("'"&$K$6&"'!$A$6") returns you would need something like this.

INDIRECT(INDIRECT("‘"&$K$6&"‘!$A$6")&"!$E$5")
Can you use the "CELL()" function to return the sheet name and address in one go?

User generated image
in my first screenshot, the formula "=CELL("address",mySheet!A1)" returns "[Book1]mySheet!$A$1", a reference to a second sheet I created called "mySheet".

The formula below that, in C4, contains an INDIRECT formula referencing the formula in C2. This correctly returns the word "test", which is what I typed in A1 of the second sheet.

User generated image
Thought #2:

Your formula:

INDIRECT(“‘“&$K$6&”‘!$A$6”)!$E$5

suggests that the value in K6 is itself a sheet name, and that A6 on that referenced sheet is another sheet name. In which you need to use two INDIRECTs as Norie suggests, if you cannot use the CELL("address") idea above.
Norie - thanks for the suggestion,  this formula is way out of my league, and I can't really follow whats going on with the INDIRECT formula's so I'm not sure if there's a syntax error or it just doesn't work.  I tried this:

=IFERROR(OFFSET(INDIRECT(INDIRECT("'"&$K$6&"'!$A$6")&”!$E$5") !$E$5, MAX(IF(NOT(ISBLANK(INDIRECT(INDIRECT("'"&$K$6&"'!$A$6")&"!$E$5") !$E$5:INDIRECT(INDIRECT("'"&$K$6&"'!$A$6")&"!$E$5") !$E$34)), ROW(INDIRECT(INDIRECT("'"&$K$6&"'!$A$6")&"!$E$5") !$E$5:INDIRECT(INDIRECT("'"&$K$6&"'!$A$6")&"!$E$5") !$E$34),0))-ROW(INDIRECT(INDIRECT("'"&$K$6&"'!$A$6")&"!$E$5") !$E$5),-2),"")

resulted in the error: there is a problem with this formula...excel thinks its a formula...to get around this type an apostrophe...

I'll try the other suggestions now.
HI Neil - thanks as well for the suggestion.  I tried this
https://www.experts-exchange.com/questions/29128782/Incorporate-INDIRECT-into-a-larger-formula.html?anchorAnswerId=42751839#a42751839

Just to see if the formula would return anything and I got the same error.


IFERROR(OFFSET(INDIRECT(M4)&”!$E$5") !$E$5, MAX(IF(NOT(ISBLANK(INDIRECT(M4)&"!$E$5") !$E$5:INDIRECT(M4)&"!$E$5") !$E$34)), ROW(INDIRECT(M4)&"!$E$5") !$E$5:INDIRECT(M4)&"!$E$5") !$E$34),0))-ROW(INDIRECT(M4)&"!$E$5") !$E$5),-2),"")


But unless I'm missing something, even if the above formula worked, the negative would be that I have to type that formula for every new sheet created, which I'm trying to avoid.  As it is, I've generated a list of all 40 sheets that I eventually excpect to have using the name manager: =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"") and referring to that using this formula: FERROR(INDEX(SheetNames, J3),"").
Neil regarding this:

uggests that the value in K6 is itself a sheet name, and that A6 on that referenced sheet is another sheet name. In which you need to use two INDIRECTs as Norie suggests

The value in K6 is indeed a sheet name, as is the value in A6.  Do I not need a reference to both cells?  K6 and A6 are one in the same sheet name.
So the formula quoted above:

=INDIRECT("'"&$K$6&"'!$A$6")

This will return the contents of A6 on the sheet named in K6.

So if K6 contains Sheet1 and A6 contains Test, the result of the Formula will be Test, the equivalent to =Sheet1!$A$6

I am assuming that is not what is required.

Similar suggestion to Neil, you may be able to use the ADDRESS function:

=ADDRESS(Row Number, Column Number, Abs Num, Address Type, Sheet Name)

This might be instead of INDIRECT if you are using the address within a formula, if you are trying to return the contents of the address then it would be still within an INDIRECT.

Example:
=ADDRESS(5,1,1,1,"Sheet1")

Result: Sheet1!$A$5

All parameters of the ADDRESS function could be formula driven, so in your example your sheet name is in $K$6 so would be:

=ADDRESS(5,1,1,1,$K$6)

Another alternative, looking at your formula, is K6 the start of the name for a range of sheets and then A6 is the remainder of the name, eg K6 has Sheet and A6 has 1 so that the combination gives Sheet1

As mentioned before, a sample file would be very helpful.
Thanks Rob - I thought I had uploaded a sample file, but did something wrong (sorry - still getting used to this forum).  Anyway, I'll look through your comments, in the meantime, sample file uploaded with the original question.
Sorry, can't download sample file as its an xlsm; work firewall won't let me download macro enabled for security reasons. I can take another look later at home unless somebody has cracked it in the meantime.
Another alternative, which will still need adjusting to make the sheet name dynamic but see if this does what you need to start with:

=INDEX(SheetXX!$C$5:$C$34,MATCH(MAX(SheetXX!$E$5:$E$34),SheetXX!$E$5:$E$34,0))

The MAX will look at range E5:E34 and find the highest value. The MATCH will then return which row (relative to row 5) the date is on. The INDEX will then give the contents of that same row from column C (two to the left).

Does the range of rows 5 to 34 need to be dynamic as well?
With dynamic sheet name:

=INDEX(INDIRECT(K6&"!$C$5:$C$34"),MATCH(MAX(INDIRECT(K6&"!$E$5:$E$34")),INDIRECT(K6&"!$E$5:$E$34"),0))
Hi Rob - you seem to understand exactly what I'm trying to do with the formula you suggested.  I have a formula that does exactly that but far longer:)

IFERROR(OFFSET('SheetXX'!$E$5, MAX(IF(NOT(ISBLANK('SheetXX'!$E$5:'SheetXX'!$E$34)), ROW('SheetXX'!$E$5:'SheetXX'!$E$34),0))-ROW('SheetXX'!$E$5),-2),"")

I'm trying to fiddle with your dynamic formula now...because I think i am using the syntax of the indirect formula wrong.
See attached, Index sheet with links to different sheets. Is this what you are trying to achieve?

Column M is dynamic rows, based on count of column E; the only fixed point is C5, the top of the range that you want to return.

Column O has ranges 5 to 34 hard-coded in the INDIRECT
INDIRECT-OFFSET.xlsx
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.