INDIRECT Formula with an IF Then issue

[Eden397]

Hi,

How do I get this formula to work:

=IF((INDIRECT($B$3))&"!d6"="All",9999,0),=IF((INDIRECT($B$3))&"!d6"="222",go
to cell y20)

I want 9999 to show up if the word All is typed in a different worksheet.
Otherwise, if 222 is typed in the cell d6, I want another cell's value to
populate. Does that make sense? thanks, Eden 397

 

[T. Valko]

Maybe this:

=IF(INDIRECT("'"&$B$3&"'!D6")="All",9999,IF(INDIRECT("'"&$B$3&"'!D6")=222,Y20,""))

 

[Eden397]

So far, I have gotten this far but I need one more indirect statement with
222 typed in where "All" or "Quota" is located. I also don't understand the
"" that you wrote as coming before my $B$3. Thanks

=IF((INDIRECT($B$3&"!d6"))="ALL",9999,IF((INDIRECT($B$3&"!d6"))="Quota",(INDIRECT($B$3&"!Y8"))))

 

[T. Valko]

I also don't understand the "" that you wrote as

coming before my $B$3.

It's actually " ' ". This is needed if the sheet name contains a space. If
the sheet name does not contain a space it does no harm.

What should the result be if the cell = 222?

 

[Eden397]

If the cell contains "222" then the result will be the same as the "Quota"
cell number. It would be the same as the below statement but with "222"
instead of "Quota". I need one more If Statement, I guess?

=IF((INDIRECT($B$3&"!d6"))="ALL",9999,IF((INDIRECT($B$3&"!d6"))="Quota",(INDIRECT($B$3&"!Y8"))))

 

[T. Valko]

Try this:

=IF(INDIRECT($B$3&"!D6")="ALL",9999,IF(OR(INDIRECT($B$3&"!D6")={"Quota",222}),INDIRECT($B$3&"!Y8"),""))

 

[Eden397]

You, my friend, are awesome! I will get to sleep easily for a change
tonight! Thank you very much.

 

[T. Valko]

You're welcome. Thanks for the feedback!