Nested IF or VLOOKUP?

[Legacy 297749]

Hi!

First post! And also incredibly difficult!

I am creating a formula that changes the content of a cell based on the current date.

I have 2 tabs. The first tab with have the cell that contains the formula I am having difficulty with and the second tab contains a table with all the data.

The table in tab 2 (Credit Cards) contains the following headings Date (Not the current date, but the same date each month), Balance Carried forward (contains a forumla that get the value from the previous balance) Payment (contains a formula that changes the value based on a value in tab 1) Balance brought forward (=SUM(BAlance carried foward-payment).

Now the cell in tab 1 should return a value based on what day it is today. I have created a nested IF that goes something like this:

=IF(AND('Credit Cards'!B4<=TODAY(),'Credit Cards'!B5>TODAY()),'Credit Cards'!E4,IF(AND('Credit Cards'!B5<=TODAY(),'Credit Cards'!B6),'Credit Cards'!E5,IF(AND('Credit Cards'!B6<=TODAY(),'Credit Cards'!B7),'Credit Cards'!E6,and so on)

This is insanely difficult to debug and I have tried looking into turning this into a vlookup so that it is simpler.

Is this possible?

 

[Alex0013]

Hello Henry,

Welcome to the forums! Do you think you could post a sample of your data on your two tabs? Also, explain what you are wanting the formula to do? It would make it easier to find a solution if we can visibly see what you are trying to do.

Just looking at your formula right now, in your AND() statements, you just have like 'Credit Cards'!B6. If that cell doesn't contain 'TRUE' or 'FALSE' or '1' or '0', I'm not quite sure what you are evaluating there.

Regards,
Alex

 

[Legacy 297749]

Hi Alex,

I have attached the sample of the tabs below.

As for the formula I want it to display the a value from the last column from the table in Tab 2 based on what day it is today using the =TODAY() function so that everytime I open the workbook the value should update.
The AND function was used so that I could basically say if today is greater than or equal to the date in 'Credit Cards'!B6 AND also less than (but not equal to) the date in 'Credit Cards'!B7 then display the value that is in the same row as 'Credit Cards'!B6 but in the B/F column so that would be E6.

And of course as the date changes this formula would eventually become invalid as =TODAY() would not fit the formulae criteria hence the nested IF function to keep it going until a specified date where I would close the formula with =IF(TODAY()>'Credit Cards'!B24,"Complete","?") as todays date will eventually be higher than the date in the table making this return the True value of "Complete".

Tab 1

	Summary
Account	Total	Payment
Account 1	Formula goes here	£500

<tbody>
</tbody>

Tab 2

	Account 1
Date	Balance Carried Forward	Payment	Balance brought forward
13/08/14	-£7300	='TAB1'C3	=SUM(B3,C3)
13/09/14	=SUM(D3)	='TAB1'C3	=SUM(B3,C3)
13/10/14	=SUM(D4)	='TAB1'C3	=SUM(B3,C3)

<tbody>
</tbody>

Note that the cell value is based on the position on this page not my actual workbook. So first column is A and first row is 1.

 

[Alex0013]

Hello Henry,

Sorry for the late response, I've been on vacation

Try using the following formula, I think it should work based on what you are trying to do if I understand correctly.

=IF(TODAY()>MAX('Tab2'!A:A),"Complete",VLOOKUP(TODAY(),'Tab2'!A:D,4,TRUE))

The IF() evaluates whether TODAY() is greater than the max date in Tab2 (the latest date of your table), and if so, marks "Complete". Else, it does a VLOOKUP() for the date returning column 4 (Balance brought forward). Make sure to use "TRUE" in the VLOOKUP(). TRUE makes it look for a relative answer instead of an exact one, so it will pick the date you want. (It looks for the lowest answer closest to TODAY(), so it won't turn to the next value until the day of the payment as intended.)

Let me know if this works for you or if I'm misunderstanding something.

Thanks!
Alex