Selective Input using VLOOKUP against two cell's data.

[Lochlainn]

Ok guys and gals. My brain is fried. Firstly, let me say I do not know VBA. I know most formulas. I have a rather intricate spreedsheet and I have 99.9% of the formulas finished. So here is my finial delima for today:

(E13) is the manual input Year in (YYYY) format

(E14) Has a dropdown of (Blank), Cumulative or Sequential

(E15) Has a dropdown of (Blank), First Quarter, Second Quarter, Third Quarter or Fourth Quarter

(E16) Needs to use a formula to look at (E14), determine if it is (Blank), Cumulative or Sequential, then look at (E15) to see what 'Quarter' it is. If (E14) says 'Cumulative' AND (E15) has a value other than (Blank), then it should enter 'January 1'. If (E14) is (Blank), then it should remain (Blank) no matter what (E15) has. If (E14) says 'Sequential', then it should should "VLOOKUP" column 2 of the same table that (E17) "VLOOKUP"s.

(E17) Uses this formula to enter the proper 'Ending Date' for the report:

=IF(ISNA(VLOOKUP((E15),E58:G62,3,0))," ",IF(VLOOKUP((E15),E58:G62,3,0)=0," ",(VLOOKUP((E15),E58:G62,3,0)&","&" "&(E13))))

Which comes out 'Month Day, Year'.

(E17) uses the Quarter dropdown from (E15) to determine the VLOOKUP range. If (E15) is blank, it leaves the cell blank. Otherwise is fills accordingly.



Currently, I am trying this formula:

=IF((E14)="Cumulative"&(ISTEXT(E15)),(F59)&","&" "&(E13),IF(ISNA(VLOOKUP((E15),E58:G62,2,0))," ",IF(VLOOKUP((E15),E58:G62,2,0)=0," ",(VLOOKUP((E15),E58:G62,2,0)&","&" "&(E13)))))

But, it's bypassing to the VLOOKUP and changing the 'Begining Date' to the start of each Quarter, even if (E14) says 'Cumulative'. So I tried this formula:

=IF((E14)="Cumulative",(F59)&","&" "&(E13),IF(ISNA(VLOOKUP((E15),E58:G62,2,0))," ",IF(VLOOKUP((E15),E58:G62,2,0)=0," ",(VLOOKUP((E15),E58:G62,2,0)&","&" "&(E13)))))

But that inputs the 'January 1' whenever (E14) says 'Cumulative' and does not wait for (E15) to have an input.

So what I would like for the formula to do is:

IF (E14)='Cumulative' AND there ISTEXT in (E15), then input 'January 1', else go to the VLOOKUP argument. Anyone think they can figure this out? I'm obviously very close, I just don't know how to make it check both cells before inputing the 'January 1' data.

Thanks a ton,
Loch

 

[jarjarbingie]

=IF(AND(E14="Cumulative", E15<>""), "January 1", VLOOKUP formula)

 

[Lochlainn]

Thank you jarjarbingie! Worked like a charm. I think I can finally put this report to bed. Now on to the next report and ledger combo. You guys are gonna love it. Makes this question look like child's play.