cell value based on selection from drop box
 

I have a worksheet with a drop box list, containing names of sheets (B2). I
want the cells in B3:F4 to carry a duplicate of the same cells from that
sheet name. Can anybody help me with a formula or macro for that purpose?

TIA
Esra

cell value based on selection from drop box
 

Esra,

I think you could use either of these in cell B3:

=INDIRECT("'" & $B$2 & "'!" & ADDRESS(ROW(A3),COLUMN(B2)))
or
=INDEX(INDIRECT("'" & $B$2 & "'!$A:$F"),ROW(),COLUMN())

Then copy, and paste in B3:F4.

HTH,
Bernie
MS Excel MVP


"Esra" wrote in message
...
I have a worksheet with a drop box list, containing names of sheets (B2).
I want the cells in B3:F4 to carry a duplicate of the same cells from that
sheet name. Can anybody help me with a formula or macro for that purpose?

TIA
Esra

cell value based on selection from drop box
 

Helps a great deal, thank you

Esra

"Bernie Deitrick" wrote:

Esra,

I think you could use either of these in cell B3:

=INDIRECT("'" & $B$2 & "'!" & ADDRESS(ROW(A3),COLUMN(B2)))
or
=INDEX(INDIRECT("'" & $B$2 & "'!$A:$F"),ROW(),COLUMN())

Then copy, and paste in B3:F4.

HTH,
Bernie
MS Excel MVP


"Esra" wrote in message
...
I have a worksheet with a drop box list, containing names of sheets (B2).
I want the cells in B3:F4 to carry a duplicate of the same cells from that
sheet name. Can anybody help me with a formula or macro for that purpose?

TIA
Esra

cell value based on selection from drop box
 

Hi,

Here is another approach:

=INDIRECT(B2&"!"&CELL("address"))

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Esradekan" wrote:

Helps a great deal, thank you

Esra

"Bernie Deitrick" wrote:

Esra,

I think you could use either of these in cell B3:

=INDIRECT("'" & $B$2 & "'!" & ADDRESS(ROW(A3),COLUMN(B2)))
or
=INDEX(INDIRECT("'" & $B$2 & "'!$A:$F"),ROW(),COLUMN())

Then copy, and paste in B3:F4.

HTH,
Bernie
MS Excel MVP


"Esra" wrote in message
...
I have a worksheet with a drop box list, containing names of sheets (B2).
I want the cells in B3:F4 to carry a duplicate of the same cells from that
sheet name. Can anybody help me with a formula or macro for that purpose?

TIA
Esra