Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 217
  • Last Modified:

add additional step to current if formula

Hi Experts excel 2007

I have the following of formula in cell b2
If (A2="", if (c2="","", c2), VLOOKUP (a2,'sheet2'! $ a $2:$b $20,2, False))

Which is fine...I need to add the following step...if g2 date is less then todays date then replace c2 with complete,  if g2 has no date then above formula.
0
route217
Asked:
route217
  • 9
  • 7
1 Solution
 
Naresh PatelTraderCommented:
=IF(G2<TODAY(),C2,If (A2="", if (c2="","", c2), VLOOKUP (a2,'sheet2'! $ a $2:$b $20,2, False)))
0
 
route217Author Commented:
Excellent feedback.
0
 
route217Author Commented:
Hi itjockey

The formula errors wlhen g2 is blank...it should return the value in c2..otherwise prefect.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Naresh PatelTraderCommented:
=IF(OR(G2="",G2<TODAY()),C2,IF(A2="",IF(C2="","",C2),VLOOKUP(A2,sheet2!$A$2:$B$20,2,FALSE)))

Open in new window

0
 
Naresh PatelTraderCommented:
Mr.route217

i dint write your formula in Excel sheet as there are many references of other sheet which i dnt have. So above formula i had written in Note pad so it may ask for auto complete. just click yes. Or provide me sample file.


Thanks
0
 
route217Author Commented:
Hold on...could be my fault

If g2 is blank then f2, otherwise if g2 has date then h2..then rest of the formula. ..

Sorry
0
 
route217Author Commented:
Sorry..cannot upload sample file..
0
 
Naresh PatelTraderCommented:
Try this
=IF(IF(G2="",F2,IF(CELL("Format",G2)="D1",H2)),IF(A2="",IF(C2="","",C2),VLOOKUP(A2,Sheet2!$A$2:$B$20,2,FALSE)))

Open in new window

0
 
route217Author Commented:
Itjockey

Thanks but getting #value error
0
 
Naresh PatelTraderCommented:
ok don't provide me sample WB but list down your criteria for whole formula.

like...
1.If G2 is blank then F2,
2.if G2 has date then H2
3.your rest formula criteria.
0
 
route217Author Commented:
Firstly,  apologies ad I cannot upload. ..WB.

Based on this formula =IF(OR(G2="",G2<TODAY()),C2,IF(A2="",IF(C2="","",C2),VLOOKUP(A2,sheet2!$A$2:$B$20,2,FALSE)))

amend kindly do if g2 is blank the return h2..if g2 has a date then as normal as above.
0
 
Naresh PatelTraderCommented:
There are two parts of formula tell me which part execute when as formula is conflicting with criteria.

Part 1
If (A2="", if (c2="","", c2), VLOOKUP (a2,'sheet2'! $ a $2:$b $20,2, False))


Part 2
=IF(G2="",F2,IF(CELL("Format",G2)="D1",H2))

Thanks
0
 
route217Author Commented:
Hi itjockey. ..
Firstly thanks for all the excellent feedback...I have to post a new question..clearly with better wording.
0
 
Naresh PatelTraderCommented:
ok No problem but after you find solution pls post here formula or link for my understanding.


Thanks
0
 
route217Author Commented:
And once again appreciate the patience. ..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 9
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now