Automatically grab formulas from previous row when adding new data

[excelerino]

Hi all,

I am working with multiple sheets in one workbook. The mainsheet (with about 50 columns) is used for data entry, the other sheets consist of a replication of the first 10 columns followed by formulas for calculation based on relevant info grabbed from the remaining columns in that mainsheet. When a new entry is added in the mainsheet, this is reflected in the other sheets and the calculations are then done without further input. I´ve done this by basically just dragging down the formula in the non-mainsheets a bunch, which means a lot of 0s pop up as there are calculations being done on non-existent data. I was wondering if there is a nicer way to do this.

Basically what I´d like to know is if there is a way to create a macro that says if data is added to the mainfile, then grab the formulas in place in the non-main file from the last row and drag them down 1, i.e. if I have 380 entries in the mainfile and add row 381, in the non-main file grab the formula from row 380 and increment by 1 to automatically reflect both the new input from the mainfile and do the calculations. Secondly, how best to make this a dynamic range? I already have a macro in place that will automatically update my pivottables/charts if there are changes on existing data, but it does not take into account new entries.

Thanks for any and all help on both of these topics!

 

[MrJoosten]

Hello Excelerino,

Maybe putting your main data in a table would do the trick?
I'm afraid I do not have the VBA knowledge to help you any further .

Goodluck with the issue anyways!