sheet name in formula

Oaktree

MrExcel MVP

Joined

Jun 20, 2002

Messages

8,120

Office Version

1. 365

• Sep 8, 2006

• #2

Assuming A1 is your sheet with the data validation list from which you choose A, B, C, or D, have B1 =INDIRECT(A1&"!B6")

 

Upvote 0

M

methody

Well-known Member

Joined

Jun 17, 2002

Messages

857

• Sep 8, 2006

• #3

thanks perfect

 

Upvote 0

M

methody

Well-known Member

Joined

Jun 17, 2002

Messages

857

• Sep 9, 2006

• #4

follow up

Hello
Sorry about this but on a similar theme I still have the same 4 sheets A B C D. Each sheet has a table in the same place with 2 columns. On a separate sheet I select the sheet using data validation withn a cell (in A1). I also select a value in the cell below (A2) which will be in the first column of the tables in the 4 sheets. If I select A in the cell A1 and 'Tom' in cell A2, I want the value to the right of 'Tom' in the table which appears in sheet A.

I know it is probably a combination of INDIRECT and VLookup but I just can't quite get it.

 

Upvote 0

D

Domenic

MrExcel MVP

Joined

Mar 10, 2004

Messages

21,577

Office Version

1. 365

Platform

1. Windows

• Sep 9, 2006

• #5

Assuming that on each sheet A5:B100 contains the table, try...

=VLOOKUP(A2,INDIRECT("'"&$A$1&"'!A5:B100"),2,0)

Hope this helps!

 

Upvote 0

M

methody

Well-known Member

Joined

Jun 17, 2002

Messages

857

• Sep 9, 2006

• #6

lovely!

 

Upvote 0

M

methody

Well-known Member

Joined

Jun 17, 2002

Messages

857

• Sep 20, 2006

• #7

similar thing

Hello again
I hope this thread is still open

On a similar vein, I have a base sheet. In a particular cell (B1) I have a data validaton list from which I can select the name of 4 other sheets. In each of these sheets the key range is C6 through to C26. When I select the sheet name from B1 I want the data validation cell in B4 to be populated by the appropriate cells in the range C6 to C26. I am able to do this but if only the cells C6 to C8 are filled I only want them to appear in the data validation cell.
I presume it is a combination of INDIRECT and DYNAMIC ranges using offset but I can't get it to work.
Anybody any idea

 

Upvote 0

D

Domenic

MrExcel MVP

Joined

Mar 10, 2004

Messages

21,577

Office Version

1. 365

Platform

1. Windows

• Sep 20, 2006

• #8

Assuming that C6:C26 contains numerical values, try...

=INDIRECT("'"&$B$1&"'!C6"):INDEX(INDIRECT("'"&$B$1&"'!C6:C26"),MATCH(9.99999999999999E+307,INDIRECT("'"&$B$1&"'!C6:C26")))

If the ranges contain text values, replace...

9.99999999999999E+307

with

REPT("z",255)

Hope this helps!

Edited: Corrected the references to match those of the orignal post...

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Sep 20, 2006

• #9

Or you could try:

=OFFSET(INDIRECT("'"&B1&"'!$C$6"),0,0,COUNTA(INDIRECT("'"&B1&"'!$C$6:$C$26")),1)

 

Upvote 0

D

Domenic

MrExcel MVP

Joined

Mar 10, 2004

Messages

21,577

Office Version

1. 365

Platform

1. Windows

• Sep 20, 2006

• #10

In this case, since INDIRECT is already being used, I should have used OFFSET instead of INDEX, as Andrew has astutely done...

 

Upvote 0