Reduce file size??

Cberry

Board Regular
Joined
Jul 14, 2006
Messages
95
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.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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.

Code:
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
 
Upvote 0

Forum statistics

Threads
1,214,278
Messages
6,118,638
Members
448,844
Latest member
Jayee04e

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top