# Modifying INDIRECT

1. ## 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.

2. ## 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.

3. ## Re: Modifying INDIRECT

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.

4. ## Re: Modifying INDIRECT

OK thanks for this. It works perfectly first time. I am beyond impressed!

Problem solved!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1