Selective Input using VLOOKUP against two cell's data.

Lochlainn

New Member
Joined
Jun 10, 2014
Messages
2
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
 
Last edited:

Lochlainn

New Member
Joined
Jun 10, 2014
Messages
2
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.
 

Forum statistics

Threads
1,082,176
Messages
5,363,577
Members
400,751
Latest member
wheddingsjr

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top