Author Topic: Excel Data Validation  (Read 1291 times)

danb

  • 5 O'Clock Shadow
  • *
  • Posts: 37
Excel Data Validation
« on: February 21, 2015, 07:20:40 PM »
Hello,

I have a financial spreadsheet. Some of my income sources are 12 Annual payments, while others are 26 Annual Payments.

Can someone help me figure out how to do the following:

If Income_ESI!D1 has a value of Extras!C2, then populate Income_ESI!E1 with the value from Extras!D2
If Income_ESI!D1 has a value of Extras!C3, then populate Income_ESI!E1 with the value from Extras!D3
If Income_ESI!D1 has a value of Extras!C4, then populate Income_ESI!E1 with the value from Extras!D4

I've attached the spreadsheet and am open to alternate ways of achieving the same goal

Once Income_ESI!E1 has the proper payment frequency value I'm going to use this number on Income_TOTAL to force each income contribution source to be in a monthly format

For example:
Cell F8 would have the following formula
=(Income_ESI!E1*Income_ESI!F8)+(Income_LLC!E1*Income_LLC!F8)+(Income_VA!E1*Income_VA!F8)


deborah

  • Walrus Stache
  • *******
  • Posts: 8627
  • Location: Australia or another awesome area
Re: Excel Data Validation
« Reply #1 on: February 21, 2015, 07:50:55 PM »
Income_ESI!E1  has the formula    IF(Income_ESI!D1 = Extras!C2,Extras!D2,0)      (I assume you want 0 if they are not equal)

ditto for the rest

danb

  • 5 O'Clock Shadow
  • *
  • Posts: 37
Re: Excel Data Validation
« Reply #2 on: February 21, 2015, 08:01:19 PM »
Thanks Deborah,

I think i might have not been as clear as needed.

When I put in

If I select "26xAnnual" from the drop down list for D1
and have E1 with the following formula: =IF(Income_ESI!D1 = Extras!C3,Extras!D3,0)
E1 Has a Value of 2.66666667 which is Great and what I want.

However, If I select  "12xAnnual" from the drop down list for D1
and have E1 with the following formula: =IF(Income_ESI!D1 = Extras!C3,Extras!D3,0)
E1 Has a Value of 0 which is not the desired outcome
The desired outcome would be 1.0000000000

How can i get both outcomes to be as expected?


deborah

  • Walrus Stache
  • *******
  • Posts: 8627
  • Location: Australia or another awesome area
Re: Excel Data Validation
« Reply #3 on: February 21, 2015, 08:16:03 PM »
=VLOOKUP(Income_ESI!D1,Extras!C:D,2,FALSE)

danb

  • 5 O'Clock Shadow
  • *
  • Posts: 37
Re: Excel Data Validation
« Reply #4 on: February 21, 2015, 09:25:55 PM »
Thanks! You have been very helpful.