Use cellcontent as worksheet name in formula - Excel - Forum Excel, VBA, VSTO, Exceltips, Excelhjälp

2023-01-20 09:43

Medlem sedan: 2023-01-20

1 inlägg

Hi, I would like to use the cellcontent (e.g. D11) as worksheet name in the formula:

=VLOOKUP($B13,Population!$A$10:$Z$70,MATCH(D$10,Population!$A$10:$Z$10,0),0)

thanks for your suggestions! /Robert

2023-01-20 10:54

Medlem sedan: 2024-06-02

0 inlägg

ICIT just asked the same Question (2 before yours)

You just paste the parts and create a text string with the address:

=D11&"!$A$10:$Z$70"
or
CONCAT(D11,"!$A$10:$Z$70")

Next step is to use INDIRECT to let Excel know the string is an address

INDIRECT(D11&"!$A$10:$Z$70")
and
INDIRECT(D11&"!$A$10:$Z$10")

And put it in your formula:

=VLOOKUP($B13,INDIRECT(D11&"!$A$10:$Z$70"),MATCH(D$10,INDIRECT(D11&"!$A$10:$Z$10"),0),0)

Or, with the row locked:

=VLOOKUP($B13,INDIRECT(D$11&"!$A$10:$Z$70"),MATCH(D$10,INDIRECT(D$11&"!$A$10:$Z$10"),0),0)

Not tested, i don’t use English formulas. But now you have the LEGO-blocks needed)

Sida 1 av 1