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]

I'm still not quite with it but here's an example of a conditional formatting formula with A2:F27 selected:

=AND($G2="Unpaid",$F2>27)

The $ sign before the column reference makes it absolute.

 

[Guntherm]

I'm still not quite with it but here's an example of a conditional formatting formula with A2:F27 selected:

=AND($G2="Unpaid",$F2>27)

The $ sign before the column reference makes it absolute.

It would Be for each line.

I am not explaining it right, for that i am sorry.

Column Shows day due (1,3,4,10,15 etc)
Column G Shows Upaid, and Paid

Conditional Formating of A2:F2

If today is greater than F2 and G2 is unpaid, then Make A2:F2 turn Red.

This would happen for each row from 2 to 27.

Maybe that will make more sense. LOL

So would I change the forumla like this?

=AND(G2="Unpaid",$F2>Day(NOW()))

And put that in the conditional format with A2:F27 selected?

Edit:

=AND(G2="Unpaid",F2>Day(NOW()))

I tried that, but it only turns column A Red for ones not paid. Not A2 through F2

 

[Andrew Poulsom]

Notice the $ signs to fix the column references:

=AND($G2="Unpaid",$F2>Day(NOW()))

 

[Guntherm]

Notice the $ signs to fix the column references:

=AND($G2="Unpaid",$F2>Day(NOW()))

Yea I had them taken out thinkign it wouldn't apply to the next lines.

Worked perfect. Thank you very much sir!!!

For whatever reason.. it still works even though I didnt change column F

I have:

3rd
4th
9th
15th
15th
16th

and it is still picking up and truning row red.

 

[Andrew Poulsom]

It won't work with text entries.

 

[Guntherm]

Is it possible to add a button to Info sheet that allows you to click and it applies payment to the Payments Sheet and pulls up a list of items in

Payment! A3:A27 and apply it based on what we did with the correct month?

 

[Andrew Poulsom]

Sorry, I don't understand any of that.

 

[Guntherm]

On Sheet Info

Add a Command Button that:

Pulls up a list taken from Payments Sheet Column A2:27

Select Item in List

And then allow you to enter in a Dollar amount to be filled into the correct Month that it currently is. i.e like yesterday Column K(for Sept)

 

[Andrew Poulsom]

You would need a UserForm for that. Here's an example:

http://www.contextures.com/Excel-VBA-ComboBox-Lists.html