Go Premium for a chance to win a PS4. Enter to Win

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

Updating Column Based on Criteria within Fields

Hi there, I have been trying to wrap my head around this for a bit and am wondering if Crystal would be better for this but prior to moving in that direction I thought I'd ask the experts!

I have attached a file of sample data and here is the summary:

>If someone is deceased using deceased date within 07/01/2015 to 06/30/2016 AND the CodeDate is in the same range, update the field list to "In Memoriam Current"
>If someone has deceased = Yes and no deceased date but the CodeDate is between 07/01/2015 and 06/30/2016 the update the list column to In Memoriam Current.
>If someone is deceased using deceased date prior to 07/01/2015 and the CodeDate is less than 07/01/2015 then update the column list to "In Memoriam"
>If someone has deceased = Yes and no deceased date and CodeDate is less than 07/01/2015 then update the column list to "In Memoriam"
>all others would be "Current"

Can excel do this or did I just confuse everyone? I'm curious because I normally use crystal and really don't want to make a ton of different reports but would like to automate this for future.

Thanks very much in advance -
CS
EE-Test.xlsx
0
rporter45
Asked:
rporter45
  • 3
  • 2
1 Solution
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Maybe this?

=IF(AND(M2>=DATEVALUE("07/01/2015"),M2<=DATEVALUE("06/30/2016"),OR(AND(G2>=DATEVALUE("07/01/2015"),G2<=TODAY()),AND(F2="Yes",G2=""))),"In Memoriam - Current",IF(AND(M2<TODAY(),G2<TODAY()),"In Momoriam","Current"))

Open in new window

0
 
rporter45Author Commented:
So far that looks like it will work - I had a minor tweak to a cell letter but other than that - it looks ok so far. I will test live on the entire group and will let you know!  thanks - CS
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome!
0
 
rporter45Author Commented:
Perfect thanks - after seeing this I really have to see what else excel can do to make life easier!

thank you - CS
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome again!
There are endless possibilities, you only need to explore them. :)
0

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.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now