Help with nested if statements

[GUINNESS85]

First time trying to do a nested if statement, can someone help me out? Guessing my " ' or ) are not right...

I am getting a #REF! error

=IF(J41=(INDIRECT("'VVINSTAC"&F3&"'")),0,IF(J41=(INDIRECT("'INSTAC"&F3&"'")),0,IF(J41=(INDIRECT("'REMAC"&F3&"'")),J44,IF(J41=(INDIRECT("'AUDITAC"&F3&"'")),J44,""))))

J41 is just displaying the active worksheet name via
=REPLACE(J40,1,89,"")
J40 is the filename

 

[Dannyh1]

Hi,

What is F3? (I'm quessing you may me missing some !'s)

 

[GUINNESS85]

Hi,

What is F3? (I'm quessing you may me missing some !'s)

F3 is a text value (an aircraft number ie 998RT) that is not a constant I only want the txt value for the indirect I am not trying to return a specific cell value. I am guessing that is what you are refering to because normally
=(indirect("'vvinstac"&F3&"'!J4") looks like that.

So, if the worksheet name is vvinstac998rt I want to display 0.
if the worksheet name is instac998rt I want to display 0.
if the worksheet name is remac998rt I want to display value in J44
if the worksheet name is auditac998rt I want to display value in J44
and finally if the worksheet name is none of the above i want to display nothing.

 

[Dannyh1]

still not quite sure what it is trying to do.

Is "vvinstac" a sheetname & you want to look up aircraft number in cell F3 on that sheet?

 

[GUINNESS85]

still not quite sure what it is trying to do.

Is "vvinstac" a sheetname & you want to look up aircraft number in cell F3 on that sheet?

Possible sheet names are:

VVINSTAC998RT
INSTAC998RT
REMAC998RT
AUDITAC998RT

but the 998rt will change because I am working with 500 different airplanes. Each engine has its own file and the above sheet names correspond to whether the engine is being installed, removed or just audited. The aircraft number that the engine is on (or being removed from) is in cell F3. Is there a way i can attach an engine file on here so you can look at it?

 

[Dannyh1]

I don't think you need Indirect as you are not actually referencing those sheets, you are just asking if J41 = that sheet name - is that right?

in which case:

=IF(J41="VVINSTAC"&F3,0,IF(J41="INSTAC"&F3,0,IF(J41="REMAC"&F3,J44,IF(J41="AUDITAC"&F3,J44,""))))

 

[GUINNESS85]

I don't think you need Indirect as you are not actually referencing those sheets, you are just asking if J41 = that sheet name - is that right?

in which case:

=IF(J41="VVINSTAC"&F3,0,IF(J41="INSTAC"&F3,0,IF(J41="REMAC"&F3,J44,IF(J41="AUDITAC"&F3,J44,""))))

Yes, that seems to work for all my page options. Thank you!

 

[GUINNESS85]

Running into another issue maybe you can help me again or anyone else. lets say i have 3 sheets in my workbook and they are named as follows;
VVINSTAC998RT, REMAC998RT, INSTAC999LT.

I want to get the value in I3 from VVINSTAC998RT to display on INSTAC999LT. But here is the problem I am running into...I normally just use =(INDIRECT("'VVINSTAC"&F3&"'!$I$3")) where F3 is the name of an aircraft (998RT) But that uses the F3 from the current page.