Excel "INDIRECT" function with parameter from "CONCATENATE"

0
votes

I am using an INDIRECT function to get value of a cell (B4) for many sheets in my workbook. At the moment I have to hard code the name of the sheets like this 

=INDIRECT("WCNEXP!$B$4")

Ideally I would like to find a way to compile the name "WCNEXP" with the help of the CONCATENATE formula, like this 

=INDIRECT("CONCATENATE(B18,C18)!$B$4")

but it does not work for some reason.

Is there another way to get the name compiled from 2 cells and use the INDIRECT Formula ?

Photo of the workbook

1
excelexcel-formulaformula
=INDIRECT(CONCATENATE(B18,C18, "!$B$4")) ? i.e. not the TEXT "Concatenate" (since you put it inside the double quotes). - FXD
Agree with FXD - an alternate solution would be to cut CONCATENATE all together: =INDIRECT(B18&C18&"!$B$4") - Prebsus
FXD that is genius, thanks a million! - Dodo
And what about the following case =IFERROR(COUNTA(WALCBU!$A$3:$A$115),"") . =COUNTA(CONCATENATE(B18,C18, "$A$3:$A$111") does not seem to work though. - Dodo

1 Answers

1
votes

Your code is almost good. the quotes must be placed after the concatenate function, and with the use of "&" to join them. 

=INDIRECT(CONCATENATE(B18,C18)&"!$B$4")