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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
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
 

Cberry

Board Regular
Joined
Jul 14, 2006
Messages
95
Thanks, I'll play with it and see if I can get it to work.
 

Forum statistics

Threads
1,141,734
Messages
5,708,168
Members
421,549
Latest member
Dtcfire

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
Top