Formula help needed

[chamdan]

Hi,
I have this enclosed workbook in which in Cell $H4 I want to insert a formula that will concatenate the value located in cell $B4 and pickup the content of the worksheet having the name as listed in Cell $B4 and get the content of the Cell $I$4 of that worksheet and place it in the the Cell $H4 of the Status Summary.

I tried the following:

Code: Select all

="="&OFFSET(INDIRECT("$H" & ROW()),0,-6)&"!$I$4"

But it is not picking the content of the corresponding Cell in the worksheet name that corresponds to the Cell Value found in the "Status Summary" sheet.

Your help in achieving this would be appreciated.

Regards,

Chuck

[Rudi]

Hi,

Since your formula is inside a table, a structured reference for cell H4 would look like this:

=[@[BIN '#]]&": "&INDIRECT([@[BIN '#]]&"!I4")

Alternatively, if you prefer standard references for cell H4, you can use this:

=$B4&": "&INDIRECT($B4&"!I4")

[chamdan]

Rudi,

Thanks but it did not work. See the attached workbook.

[Rudi]

The formula is correct if the value in B4 is intended to be the name of the sheet (in this case: Template).
That is what I gathered from your opening post; that you supply the sheet name in B4 and concatenate it with the value from H4 to determine the status for each sheet.

Please clarify what you need in B4 then?
TX

[chamdan]

Sorry Rudi I realised that the worksheet BIN001 was not created yet. I did the modification and tried your formula but I had to make a change to it to work.
The formula worked with:

Code: Select all

=INDIRECT($B4&":"!$I$4)

See the enclosed workbook.

Cheers!

Chuck

[Rudi]

Ah... so I see you only wanted the Status of the sheet and not to concatenate it as the OP stated. 100%

BTW:
The formula in your workbook is correct: =INDIRECT($B4&"!I4"), but the one you posted above (with the colon) is not. (Just mentioning this for the sake of others browsing this thread.)

[chamdan]

Rudi,

Thanks for correcting my mistake. I have another problem, which I cannot see why it is not working. In Column C of the Status Summary worksheet, the formula is not working while it is for the remaining ones. Would you mind having a look at it?

Thanks in advance.

Chuck

[Rudi]

Hi,

Everything of the formula is correct... The cell is just formatted as text.
If you select cell C4 and format it to General on the Home Ribbon, then double click in the cell and press Enter, it will calculate.

[chamdan]

Wow! I wouldn't figure out that.

Thank you!

Chuck