Basic spreadsheet query

  • Thread starter Thread starter Steve
  • Start date Start date
S

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
 
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.
 
Norman Harker said:
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.
Click to expand...


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
 
Steve said:
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
Click to expand...

Conditional formatting.
 
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.
 
Norman Harker said:
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.
Click to expand...

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

regards
Steve
 
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.
 
Back
Top