concatenate text into formula

[Gerry]

I'm trying to combine a text cell and a location into a formula

On Sheet1 I have column headers that equal worksheet names.

On Sheet1 in cell C6 the text is BHL, which is also a worksheet name,
D6 is EJH, also a workhseet name.

I have 15 columns and 15 worksheets. I'm trying to make it easy to
copy the formula across cells.

I want C7 to be formula =BHL!$C7, and D7 to be =EJH!$C7

In Cell C7 I've entered;

=(VALUE("="&((TEXT(C6,1)&"!$C7"))))

But I end up with #VALUE!

=(CONCATENATE("="&(TEXT(C6,1)&"!$C7")))

or

=CONCATENATE("="&C6&"!$C7")

But I end up with =BHL!$C7, but as text.

Thanks in advance

Gerry

 

[Max]

We can use INDIRECT ..

Put in C7:
=INDIRECT("'"&C6&"'!C7")
Copy C7 across

Perhaps better, if you're trying to take it out down the col as well,
viz:
In C7: =BHL!$C7, in D7: =EJH!$C7, etc
then in C8: =BHL!$C8, in D8: =EJH!$C8, etc
and similarly in C9, D9 ...

Place in C7:
=INDIRECT("'"&C6&"'!C"&ROW(A1)+"6")
Copy C7 across as many cols as there are headers in C6 across,
then fill down as far as required

Take care to ensure consistency. The sheetnames labelled in C6 across
should match exactly what's on the sheet tabs (except for case),
otherwise we'd get #REF!. Watch out for any typos, extra white spaces,
etc.

 

[Max]

Oops, slight correction to ..

Place in C7:
=INDIRECT("'"&C6&"'!C"&ROW(A1)+"6")

Formula in C7 should read as:
=INDIRECT("'"&C$6&"'!C"&ROW(A1)+"6")

(we need to fix it to the col headers in C6 across, ie use "C$6",
as we are going to copy the formulas down)