Basic spreadsheet query

[Steve]

Hi
I've designed a basic spreadsheet for our social fund. In column A1 is the
members names and along the top is the weekly dates for the year. The
members pay a £1 a week and this is totalled in column AY. Sometimes the
members are absent and sometimes they pay in advance. Is it possible to do
something with column AY to show at a glance whether they're their in
arrears, in credit or just up to date?


TIA
Steve

 

[Norman Harker]

Hi Steve!

Here's my layout.

I have names in column A
In B1 I have 1-Jan-2004 (your base date may be different)
In C1 I have:
=B1+7
Copied across to AX1
In AY1 I have:
=(TODAY()-B1)/7+1
This gives me the number of weeks that should be paid by today.
In A2 I have:
John
In B2:AX2 I have John's contributions
In AY2 I have:
=SUM(B2:AX2)
In AZ2 I have:
=IF(AY2<$A$Y1,"In Arrears",IF(AY2=$A$Y1,"Up to date","In Advance"))
This can be copied down for other members.


You'll have to adapt but the basic principles are there of a fairly
simple approach
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Steve]

Hi Steve!

Here's my layout.

I have names in column A
In B1 I have 1-Jan-2004 (your base date may be different)
In C1 I have:
=B1+7
Copied across to AX1
In AY1 I have:
=(TODAY()-B1)/7+1
This gives me the number of weeks that should be paid by today.
In A2 I have:
John
In B2:AX2 I have John's contributions
In AY2 I have:
=SUM(B2:AX2)
In AZ2 I have:
=IF(AY2<$A$Y1,"In Arrears",IF(AY2=$A$Y1,"Up to date","In Advance"))
This can be copied down for other members.


You'll have to adapt but the basic principles are there of a fairly
simple approach
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Hi Norman

It worked fine, thanks very much.
Do you know if it's also possible to change the font colour for each state
of their account
Maybe red for in arrears, blue for credit etc
Cheers
Steve

 

[Rifleman]

Hi Norman

It worked fine, thanks very much.
Do you know if it's also possible to change the font colour for each state
of their account
Maybe red for in arrears, blue for credit etc
Cheers
Steve

Conditional formatting.

 

[Norman Harker]

Hi Steve!

You can use conditional formatting and it is just a case of deciding
how much you want to highlight.

For example, you could highlight the range A2:AZ2

Format > Conditional formatting
First drop down "Formula Is:"
Type the formula as:
=$AZ2="In Arrears"
Press the format button and select the format (e.g. Pattern Tab select
red)
OK
Add button
First Drop down "Formula Is:"
Type the formula as:
=$AZ2="In Advance"
Press the format button and select the format
OK
Add button
First Drop down "Formula Is:"
Type the formula as:
=$AZ2="Up to Date"
Press the format button and select the format
OK
OK

You can now select A2:AZ2
Copy
Select all of the data entry range (e.g. A2:AZ30)
Edit > Paste Special
Check Formats
OK

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Steve]

Hi Steve!

You can use conditional formatting and it is just a case of deciding
how much you want to highlight.

For example, you could highlight the range A2:AZ2

Format > Conditional formatting
First drop down "Formula Is:"
Type the formula as:
=$AZ2="In Arrears"
Press the format button and select the format (e.g. Pattern Tab select
red)
OK
Add button
First Drop down "Formula Is:"
Type the formula as:
=$AZ2="In Advance"
Press the format button and select the format
OK
Add button
First Drop down "Formula Is:"
Type the formula as:
=$AZ2="Up to Date"
Press the format button and select the format
OK
OK

You can now select A2:AZ2
Copy
Select all of the data entry range (e.g. A2:AZ30)
Edit > Paste Special
Check Formats
OK

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Hi Norman
Again it worked fine, thanks again very much.

regards
Steve

 

[Norman Harker]

Hi Steve!

Thanks for acknowledgement.

We usually try and pre-test solutions offered but... we call it
Murphy's Law.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.