# Modifying INDIRECT

Printable View

• 02-15-2020, 12:13 AM
CDandVinyl
Modifying INDIRECT
Hi All

I'm using this

=INDIRECT(K6&"!"&I6)

to fetch the cell content from I6 on the named worksheet

Can someone advise as to how the formula can be modified to bring the content firstly from the left of the target cell , and then from the right of it? in this case it would return the content in H6 or J6 from the target worksheet.

I'm trying to build it into a larger formula where if H6 on the current worksheet contains the letter 'F' then it would show content from the cell to the right of the formula above. If H6 contains the letter 'G' then it would show the content to the left of the formula above.

Grateful for any help.
• 02-15-2020, 12:28 AM
MarvinP
Re: Modifying INDIRECT
Hi CDand Vinyl,

We really need a sample workbook to see what you have and want. I'd hate to guess at an answer without seeing an example problem and answer you expect.
Figure out how to attach a sample workbook and it will help you get a good answer.
• 02-15-2020, 12:39 AM
hrlngrv
Re: Modifying INDIRECT
Quote:

Originally Posted by CDandVinyl
. . .
=INDIRECT(K6&"!"&I6)

. . . the cell content I need.

. . . if H6 contains the letter 'F' then it would show content from the cell to the right of the formula above. If H6 contains the letter 'G' then it would show the content to the left of the formula above. . . .

=OFFSET(INDIRECT(K6&"!"&I6),0,IF(COUNTIF(H6,"*F*"),1,IF(COUNTIF(H6,"*G*"),-1,#REF!)))

This returns a #REF! error when H6 contains neither F nor G.
• 02-15-2020, 01:04 AM
CDandVinyl
Re: Modifying INDIRECT
OK thanks for this. It works perfectly first time. I am beyond impressed!

Problem solved!

:)