=VLOOKUP($A$1,INDIRECT("'"&B11&"'!$A$1:T312"),7,0)
2nd Reference
-
- 5StarLounger
- Posts: 1177
- Joined: 22 Jul 2013, 18:29
2nd Reference
I have the attached that references a specific sheet and with a VLookup reference and displays the corresponding value connected to column 7. Can you assist in modifying the formula to combine an HLookup reference within the same sheet rather then 7?.. thanks
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: 2nd Reference
Which cell would you want to look up? Let's say it is B1, and you want to look it up in B1:T1 in the other sheet.
=INDEX(INDIRECT("'"&B11&"'!$B$2:$T$312"),MATCH($A$1,INDIRECT("'"&B11&"'!$A$2:$A$312"),0),MATCH($B$1,INDIRECT("'"&B11&"'!$B$1:$T$1"),0))
=INDEX(INDIRECT("'"&B11&"'!$B$2:$T$312"),MATCH($A$1,INDIRECT("'"&B11&"'!$A$2:$A$312"),0),MATCH($B$1,INDIRECT("'"&B11&"'!$B$1:$T$1"),0))
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1177
- Joined: 22 Jul 2013, 18:29
Re: 2nd Reference
I would need to reference 2 values H and V.. I don't know how to incorporate the H without changing the indirect.. I need the formula to use the indirect as it automatically references a sheet name.. Please Let me know if you better info.. For the purposes of this formula you could reference any cell, column or row.. I'll just adjust it
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: 2nd Reference
Please try to explain as clearly as you can what exactly you want to accomplish. A screenshot and/or sample workbook might help.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1177
- Joined: 22 Jul 2013, 18:29
Re: 2nd Reference
Sorry about the confusion.. let me try and explain again
If on sheet1 i have a range of A1:N10
A1:A10 has values 1,2,3,4..14
A1:N1 has values A,B,C,D..N
for example, within my formula if i select (2) and (B), both are variable, I need a formula that retrieves B2 and automatically references (Sheet1) as in the formula above.. thanks again
If on sheet1 i have a range of A1:N10
A1:A10 has values 1,2,3,4..14
A1:N1 has values A,B,C,D..N
for example, within my formula if i select (2) and (B), both are variable, I need a formula that retrieves B2 and automatically references (Sheet1) as in the formula above.. thanks again
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: 2nd Reference
Would this sample workbook provide some guidance?
You do not have the required permissions to view the files attached to this post.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 5StarLounger
- Posts: 1177
- Joined: 22 Jul 2013, 18:29
Re: 2nd Reference
Yes.. perfect thank you