Indirect

[TheSubject]

Hi all,

I've been looking through the previous posts on this and none seem to solve my issue.

What am I doing wrong with:

=INDIRECT(E2&"!&ADDRESS(indirect(G2),indirect(P2)")

It keeps returning REF! Errors.


Where E2 (and E3 - E53) contains week starting DDMMYY, corresponding to worksheets of the same name - checked, other indirects use the same reference and work.
G2 gives the row the data I'm looking for is (in this case normal hours), and P2 gives the column (in this case, an employee's name) a test formula of just =address(g2,p2)

There might be a better way - each page is a similar table of hours worked over the year, the complexity comes in that names change, columns were added, deleted, furlough row was added, etc etc, so the data I'm looking for moves around on each page.

Thanks in advance!!

 

[Joe4]

With "INDIRECT", you are dynamically building the range you want to look up.
When issues occur, I usually look to see if the "inner" part of my function is building a valid range reference (as it pertains to my workbook).

So, see what this returns:

=E2&"!&ADDRESS(indirect(G2),indirect(P2)"

and see if it creates a valid range reference.

I am guessing it probably doesn't, as you have ADDRESS and two INDIRECT functions inside your double-quotes.
Everything enclosed in double-quotes is treated as literal strings, and not calculations.

I am not sure, but you may need something like this:

=INDIRECT(E2 & "!" & ADDRESS(indirect(G2),indirect(P2)))

We would have to know exactly what is in cells E2, G2, and P2 to be sure.

 

[TheSubject]

Hey, thank you so much for replying, that didn't fix it though.

Okay so, E2 reads "040120" and is a formula of

=TEXT(A2,"ddmmyy")

G2 is "11" and the formula in is is:

=MATCH("HOURS AT STANDARD RATE",INDIRECT($E2&"!$A1:$A18"),0)

to find which row on the weekly sheets the standard rate is (as I said, the sheets changed so much over the year, the layouts don't match anymore).
P2 is "3",formula:

=IFERROR(MATCH(P$1,INDIRECT($E2&"!$A3:$K3"),0),"")

to find the column of the corresponding employee on each dated page.


Thanks!

 

[Joe4]

So what is it exactly that you want your final formula to return?
If you were to write it manually, what would it look like?

 

[TheSubject]

I want it on my summary page, to return a number from a weekly sheet, and to be draggable around my summary page, with data changing accordingly. The sheet will change depending where its dragged, and the cell row or column may vary sheet to sheet, as well as where I drag the formula. Draggability is super important for this.

In my mind, the formula is basically:

="page name(E2)"!& address(row reference(G2):column reference(P2))

where E column is dates DDMMYY and both G and P etc are numbers mapping the other pages using match function.

So when I drag up or down, all row references will drop as not protected $, so column E is for the sheet names, whilst dragging it left or right cylces between data (so G2 becomes H2 etc), and then starts again with a new employee, P2, Q3.

Because in the corresponding sheets the layout of the data moves around, so for each week the content needs finding.

Does this make sense?

thanks for your patience

 

[Joe4]

I think this may be what you want:

=INDIRECT(E2 & "!" & ADDRESS(G2,P2))

 

[TheSubject]

Perfect! Thank you so much for all your help

 

[Joe4]

You are welcome.

The secret is to build the string version of the range reference you want, i.e.

=E2 & "!" & ADDRESS(G2,P2)

and when you get it to look the way you want, then wrap it in the INDIRECT function.