Reduce file size??

[Cberry]

My company uses a spreadsheet to track loan payments and splits for several different companies with varying number of loans. Each loan is on a seperate row and formulas run from D-AX on sheet one and A-AF on sheet two. The spreadsheet has two tabs and about nine thousand formulas on each tab. I set it up this way so that I would have enough rows to account for 250 participants. I use all the formulas to make sure that the splits among sources equal the total amount submitted as payments and don't have any penny rounding errors. The second tab is just the final product of the first tab cleaned up for presentation (=not(isblank(A1), A1,"")) or something like that.

My question is: is there any way to populate each row of both tabs with the formulas only if there is data in column A of the first tab? For instance, if I input a name in A200, excel populates D200-AX200 with the correct formulas.

The file as it stands now is 3.9 mb and I would really like to reduce the size as much as possible and still have the ability to account for new loans as they are added.

 

[acw]

Hi

Below is a worksheet event macro that should get you started. I've assumed that the sheet names are sheet1 and sheet. Row 2 in each sheet has a formula that will be used as the source formula.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count = 1 And Target.Column = 1 Then
      Range("D2:AX2").Copy Destination:=Cells(Target.Row, "D")
      Sheets("sheet2").Range("A2:AF2").Copy Destination:=Sheets("sheet2").Cells(Target.Row, "A")
    End If
End Sub

If you make a change to the value in sheet1 column A, it will put the formulas into the relevant cells on both the sheets.

HTH

Tony

 

[Cberry]

Thanks, I'll play with it and see if I can get it to work.