Multiple Cell Function

A

awayinlondon

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!
 
G

Gary''s Student

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
 
Top