Indirect

TheSubject

New Member
Joined
Feb 16, 2016
Messages
13
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!!
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,509
Office Version
  1. 365
Platform
  1. Windows
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:
Excel Formula:
=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:
Excel Formula:
=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

New Member
Joined
Feb 16, 2016
Messages
13
Hey, thank you so much for replying, that didn't fix it though.

Okay so, E2 reads "040120" and is a formula of
Excel Formula:
=TEXT(A2,"ddmmyy")
G2 is "11" and the formula in is is:
Excel Formula:
=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:
Excel Formula:
=IFERROR(MATCH(P$1,INDIRECT($E2&"!$A3:$K3"),0),"")
to find the column of the corresponding employee on each dated page.


Thanks!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,509
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 16, 2016
Messages
13

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,509
Office Version
  1. 365
Platform
  1. Windows
I think this may be what you want:
Excel Formula:
=INDIRECT(E2 & "!" & ADDRESS(G2,P2))
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,509
Office Version
  1. 365
Platform
  1. Windows
You are welcome.

The secret is to build the string version of the range reference you want, i.e.
Excel Formula:
=E2 & "!" & ADDRESS(G2,P2)
and when you get it to look the way you want, then wrap it in the INDIRECT function.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,073
Messages
5,622,527
Members
415,905
Latest member
8765309

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top