Using Column Based upon Month

[Guntherm]

Info Sheet - column (G2:G27) show status of item whether paid for current month or not.

Current Formula:

=IF(Payments!L3>0,"Paid","UnPaid")

The above formula works, however I have to change all the formula's every month to change to next colum in Payments Sheet

Payments Sheet:
A3:A26 has same Name of Item in Info Sheet

Is there some formula that can be achieved that will change the L3 in above formula to match the current Month?

In Payments Sheet Row 1 Has each 3 letter Month C1:N1
In Payments Sheet Row 2 has each Number for each month C2:N2

So the result would be that the above Formula for month of Oct would look at

=IF(Payments!M3>0,"Paid","UnPaid") (M3 being Oct column) and so forth down to row 26(one row for each payble item)

I am thinking that by using a formula that looks at the month number and then defining a Name for each Column that the formula can know which column to use

 

[Andrew Poulsom]

If the data is in A1:

=IF(INDEX(Payments!C3:N3,MONTH(A1))>0,"Paid","UnPaid")

 

[Guntherm]

If the data is in A1:

=IF(INDEX(Payments!C3:N3,MONTH(A1))>0,"Paid","UnPaid")

No, the (paid,Unpaid) sits in column G on the Info sheet.

The Info Sheet and Payments Sheet column A is the list of whm to pay.

 

[Andrew Poulsom]

You said:

Is there some formula that can be achieved that will change the L3 in above formula to match the current Month?

So if A1 contains today's date (28 Sep 2011) the formula will look at column K. Isn't that what you want?

 

[Guntherm]

You said:



So if A1 contains today's date (28 Sep 2011) the formula will look at column K. Isn't that what you want?

My apolgies sir. I am at a place I cant uploada file or picture.

Info Sheet -Column A is a list of Items
Info Sheet Colum G2:G27 It says either Paid or Unpaid.

Payments Sheet Column A is the same List of Items
Payments Sheet Column C-N is the month

The formula in Info sheet G3 Currently is:

=IF(Payments!L3>0,"Paid","UnPaid") Which corrasponds to to Column L which is Sep. When Oct hits, I have to redo all formulas in Info Sheet Column G to say:

=IF(Payments!M3>0,"Paid","UnPaid") to move and look at Oct.

There is no specific date anywhere, it's based on month.

 

[Guntherm]

You said:



So if A1 contains today's date (28 Sep 2011) the formula will look at column K. Isn't that what you want?

You are correct sir. I wasnt understandign what you were saying. I just need to add a field that has a date in for formula to look at...

Your a genius!

 

[Andrew Poulsom]

If month 1 is in column C then month 9 (September) will be in column K (not L) and month 10 (October) will be in column L (not M). If you can't put a date in a cell you can use:

=IF(INDEX(Payments!C3:N3,MONTH(TODAY()))>0,"Paid","UnPaid")

for the current month.

 

[Guntherm]

If month 1 is in column C then month 9 (September) will be in column K (not L) and month 10 (October) will be in column L (not M). If you can't put a date in a cell you can use:

=IF(INDEX(Payments!C3:N3,MONTH(TODAY()))>0,"Paid","UnPaid")

for the current month.

Perfect.. Its a rocking and rolling.. Same sheet, and If i need to start a new thread, I understand.

Info Sheet:

Column F has the following.
Day of month Item Payment due.

I want the sheet to conditional format A2:F2 to be red other wise leave alone.. thus highlighting a late item. Which of course need to apply for each line down to A27.

 

[Andrew Poulsom]

What's the condition that you want to apply for say A2:F2?

 

[Guntherm]

What's the condition that you want to apply for say A2:F2?

Opps.

Based on Column G "Unpaid" and past the day of month it says it's due.

Based on Column G "Paid" leave alone.

So if column G says Unpaid and it's past the due day of 27 then A2:F2 becomes Red. Of course this must be the same from rows A2 through Row A27