Multiple Formulas Causing File Size Increase

  • Morning all.


    I am working with 4 seperate workbooks where 4 different people enter data into cells.


    I have a 5th workbook that needs to have all the data from the 4 individual workbooks bought across so I can see all data. Like a master file.


    basically, in each cell of the master, I have the following code.


    =IF('[loans - 0708 - officer 01.xls]07-08'!B5="","",'[loans - 0708 - officer 01.xls]07-08'!B5)



    I then have the code for each officer.
    As there are 24000 lines collectively for the financial year, the size of my master is 37mb, even when its empty, obviously cause each cell still have a formular in it.


    Is there ANY way to copy across the data from other workbooks without having to have every single correpsonding cell have this kind of formular to view the code? Also, copy it without having to open the other workbooks.



    HELP !!


    Tayler

    Tayler [the blue fox] :thanx:

  • Re: Copy Values From Another Workbook


    Please don't use code tags for formulas.


    In the Workbook Open event, have the formulae added enmass to the required range and then some Copy PasteSpecial code to convert them to values.

  • Re: Multiple Formulas Causing File Size Increase


    Do you need the data gathered into your master as the users enter the data to their workbook or periodic updates to your master?


    Does your master have a worksheet for each user or is it consolidated into one master worksheet?


    Can you post a small sample of your master and one user workbook?

    [FONT="Arial Black"][COLOR="blue"][SIZE="4"]Bill[/SIZE][/COLOR][/FONT]
    Tip: To avoid chasing code always use Option Explicit.

  • Re: Multiple Formulas Causing File Size Increase


    It will be periodical that the master file will be accessed, but at least once a day.
    The master has one sheet only, all four workbooks get put onto that.


    I would prefer not to have the master need to open the 4 files to copy the data to.


    And the only drama I have is that B5 on the officer will not be B5 on the master.


    If the copy was able to be done, the copy would need to put them on the newest line of course, so when the sort happens, it will not mess any of the other coloumsn from previous lines.


    I have tried to water my sheets down to be able to send them.
    I hope this is still going to help as 48kb is a very small file.

  • Re: Multiple Formulas Causing File Size Increase


    Why are you using the IF functions??


    =IF('C:\Lending\Tracking\MAPS\mortgage - loans\[loans - 0708 - officer 01.xls]07-08'!X5="","",'C:\Lending\Tracking\MAPS\mortgage - loans\[loans - 0708 - officer 01.xls]07-08'!X5)


    Why not just


    'C:\Lending\Tracking\MAPS\mortgage - loans\[loans - 0708 - officer 01.xls]07-08'!X5)


    If the cell on the user’s form is empty the function will just reference in an empty cell on the master.


    This would reduce the size of your file.


    Dave’s suggestion is to
    Step 1. In an empty master sheet past the formulas to a range of cells the size of the range used by the user’s worksheet. Since the formulas are linked it will reference in all the data on the user’s sheet. This will temporally increase the size of the master workbook.


    Step 2. Copy the range of cells in the master workbook, then paste special the values over the same range. This will convert the formulas to the data values that were referenced into the master worksheet by the formulas and drastically reduce the size of your master workbook..


    This could be done manually or via a VBA procedure.


    Another option to read closed exel files would be to use ADO code. This would be more difficult for you to write the code and with as much data as you have take considerable more time to run compared to the paste formulas-copy and Past values method above.

    [FONT="Arial Black"][COLOR="blue"][SIZE="4"]Bill[/SIZE][/COLOR][/FONT]
    Tip: To avoid chasing code always use Option Explicit.

  • Re: Multiple Formulas Causing File Size Increase


    I use the IF function as if there is no data on the cell in the officer's workbook, then it gives a 00 Jan 00 in the cell that is date formatted or a 0 if the cell is general.


    I use the if, so that if the officer cell is blank, then it too will return a blank value.


    I dont see how it can give a blank cell?

    Tayler [the blue fox] :thanx:

  • Re: Multiple Formulas Causing File Size Increase


    Quote

    I use the IF function as if there is no data on the cell in the officer's workbook, then it gives a 00 Jan 00 in the cell that is date formatted or a 0 if the cell is general.


    Sorry, you are correct. Since Dave’s suggested method uses your formulas on a temporary basis, I guess it doesn’t matter.

    [FONT="Arial Black"][COLOR="blue"][SIZE="4"]Bill[/SIZE][/COLOR][/FONT]
    Tip: To avoid chasing code always use Option Explicit.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!