Multiple Cell Function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

awayinlondon@googlemail.com - 29 Dec 2005 11:03 GMT

Hi,

I'm hoping that what I'm about to say is going to make sense!

What I want is a 'table' where once one field is a column is filled in
manually, all remaining fields are automatically populated.

Take a look at the following table for an example of what I am trying
to achieve (lets just assume that all months have 28 days to keep it
simple :-))

Electricity Phone Gas
Daily
Weekly
Monthly
Yearly

What I want is something where I can fill in any cell (ie
daily|weekly|monthly|yearly) and the other cells will be filled in. The
problem I'm having is I don't know which cell to use as a reference, as
I don't know which cell is going to be filled in when the spreadsheet
is being used!

I'd really appreciate any advice.

Thanks!

Reply to this Message

Gary''s Student - 29 Dec 2005 12:06 GMT

If your table looks like A1 thru D5:

    Electricity    Phone    Gas
Daily
Weekly
Monthly
Yearly

then enter this macro:

Sub Macro1()

' Macro recorded 12/29/2005 by gsnu245

If Not IsEmpty(Cells(2, 2)) Then
Cells(3, 2) = Cells(2, 2) * 7
Cells(4, 2) = Cells(2, 2) * 28
Cells(5, 2) = Cells(2, 2) * 365
Exit Sub
End If
If Not IsEmpty(Cells(3, 2)) Then
Cells(2, 2) = Cells(3, 2) / 7
Cells(4, 2) = Cells(2, 2) * 28
Cells(5, 2) = Cells(2, 2) * 365
Exit Sub
End If
If Not IsEmpty(Cells(4, 2)) Then
Cells(2, 2) = Cells(4, 2) / 28
Cells(3, 2) = Cells(2, 2) * 7
Cells(5, 2) = Cells(2, 2) * 365
Exit Sub
End If
If Not IsEmpty(Cells(5, 2)) Then
Cells(2, 2) = Cells(5, 2) / 365
Cells(3, 2) = Cells(2, 2) * 7
Cells(4, 2) = Cells(2, 2) * 28
End If
End Sub

Enter a value between B2 and B5 and run the macro. It will fill-in the
blanks for the remaining items in column B. As coded it only works on column
B, but it can easily be expanded for cols C and D

Signature

Gary's Student

> Hi,
>
[quoted text clipped - 22 lines]
>
> Thanks!

Reply to this Message

awayinlondon@googlemail.com - 09 Jan 2006 10:18 GMT

Worked perfectly.

Thank you very much!

Reply to this Message