Excel sheet ranges

[alliswell]

Hi everybody,
I have 4 sheets, and renamed 1 to 3 sheets as Sam, Kylie, and John and all the sheets have data in A1:c5.

And in my 4th sheet in a17 i have Above sheet names as list (data validation > list).

Now i want is, in sheet 4, cell a17, i select sam then, the range a1:c5 from sheet same should get pulled in sheet 4 in same range a1:c5. And when i select other names from dropdown list in sheet4 cell a17 it shud do the same.

Thanks to all n regards

 

[James006]

Hi,
In sheet4, cell B17 you can test

=INDIRECT($A$17&"!"&SUBSTITUTE(ADDRESS(1,COLUMN()-1,4),1,"")&ROW()-16)

 

[alliswell]

Hi,
In sheet4, cell B17 you can test

=INDIRECT($A$17&"!"&SUBSTITUTE(ADDRESS(1,COLUMN()-1,4),1,"")&ROW()-16)

Instead in b17 i need in sheet 4 a1
I tried but giving #value error

 

[Fluff]

How about

=INDEX(INDIRECT("'"&$A$17&"'!a1:c5"),ROWS(A$1:A1),COLUMNS($A1:A1))

 

[James006]

Hi,
A tiny modification

=INDIRECT($A$17&"!"&SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,"")&ROW())

 

[alliswell]

How about

=INDEX(INDIRECT("'"&$A$17&"'!a1:c5"),ROWS(A$1:A1),COLUMNS($A1:A1))

Thankyou fluff. Great job. Love ya. Its working the way i need to. Thanks thanks thanks.

And james thanks yo you too. Your formula too worked but i needed it in A1 sheet4. I tried to alter hut failed. Thanks again to every one.

Love u all

HAVE A NICE DAY !

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[alliswell]

Thankyou fluff. Great job. Love ya. Its working the way i need to. Thanks thanks thanks.

And james thanks yo you too. Your formula too worked but i needed it in A1 sheet4. I tried to alter hut failed. Thanks again to every one.

Love u all

HAVE A NICE DAY !

James your second formula works the way i need too. Thanks bro. Love.