using Indirect function

[ardykav]

Hi All,

I am trying to use an indirect function but am making an error somewhere. Can anyone please advise?

So cell AG2 has the number 25 in it, what i want is the function to look up the controls tab at cell A25.

=IF(H4=0, "",INDIRECT("'"&"Controls!$A"&AG2&"'!"&"C27"))

Does this make sense?

thanks in advance

 

[FDibbins]

=IF(H4=0, "",INDIRECT("'Controls!$A"&AG2))

 

[Steve_R]

Because you have no spaces in your sheet name, you don't need to use """

If I read it correctly, the Indirect part you need is:

=INDIRECT("Controls!A" & AG2)

That assume AG2 is on 'this' sheet, not Controls sheet.

=IF(H4=0, "",INDIRECT("Controls!A" & AG2))

If AG2 contains 25, the formula would evaluate as

=Controls!A25

Is that what you need?

 

[ardykav]

Because you have no spaces in your sheet name, you don't need to use """

If I read it correctly, the Indirect part you need is:

=INDIRECT("Controls!A" & AG2)

That assume AG2 is on 'this' sheet, not Controls sheet.

=IF(H4=0, "",INDIRECT("Controls!A" & AG2))

If AG2 contains 25, the formula would evaluate as

=Controls!A25

Is that what you need?

Thats perfect thanks so much. So how do i then incorporate the Cell C27 look up?

 

[Steve_R]

What are you trying to do with C27? If concatenate with ControlsA25?

Example:
=INDIRECT("Controls!A" & AG2) & C27

will evaluate as

=Controls!A25 & C27
Insert a space between results and wrap that inside your If test:

=IF(H4=0, "",INDIRECT("Controls!A" & AG2) & " " & C27)

 

[ardykav]

What are you trying to do with C27? If concatenate with ControlsA25?

Example:
=INDIRECT("Controls!A" & AG2) & C27

will evaluate as

=Controls!A25 & C27
Insert a space between results and wrap that inside your If test:

=IF(H4=0, "",INDIRECT("Controls!A" & AG2 & " " & C27))

Yes thats right, that formula you had is perfect, all works great, thank you very much