Automatically copy to new rows to new rows as raw data is added on another worksheet

Hi everyone,

This is my first time using this forum, and my macro skills are super basic so really hoping someone can help me out with this little problem...

I basically have two worksheets: the first one is called "Residential_Inputs" and it only contains raw data. The first set of data filled starts from F8 and I will keep adding new rows and columns over the years. Basically, I will be entering raw data to this sheet every quarter.

The second sheet is called "Residential_Calculations" and will consist of one simple calculation which is: Residential_Inputs F8/Residential_Inputs E8.

Because the number of rows in my "Residential_Inputs" sheet will keep changing as I enter raw data every quarter, I do not want to have to copy/paste the formula or link the data everytime I enter new data.

Is there a way I can use a macro so that everytime I enter raw data into a new row in the "Residential_Inputs" sheet that formula is automatically entered into a new row in the "Residential_Calculations" sheet?

Would appreciate any help I can get on this! Thanks!!!

Sure!

So basically I would just input data into the first sheet, and for every new row of data that I input, I would like for the same row to be replicated into the second sheet with the formula automatically so I don't have to do it manually over the course of time...

Thanks!!

Hi,

Can you share your workbook with us? If so manually add the results you expect to see on the Calculations sheet so we can be cleaar as to exactly what you want

1. Are you expecting to see as many rows in the Residential Calculations sheet as there are in the Inputs sheet?
2. Are you adding new blocks of data below the existing data?
3. Is there a particular reason why you want a second sheet with just a single formula when you could just add another column to the Inputs sheet which gets updated automatically as you add new data.
4. Is it really a single formula on the Calculations sheet or a formula that is copied down all the rows (see 1 above)

One way would be the Sheet Calculations sheet Activate event.

Something like

Please Login or Register  to view this content.

Hi,

When you post a question without attaching a workbook you should ensure what you say faithfully describes your workbook.
You originally said that the Calculations sheet would be 'one simple calculation'. OK I think we might deduce that what you meant was one column with a simple calculation.

Now you've posted a workbook it seems that you want a calculation in at lest two columns judging by an entry in column G.

To avoid us all wasting our time would you confirm precisely what you are expecting to see.

One suggestion. If you are likely to have any sort of requirement to compare or analyse/summarise results by Date, Area, Property type etc. then you should change the layout of your Inputs sheet by adding a fifth date column and a sixth Amount column to your first four.
Then list all dates in one large 6 column database. You will then be able to use a Pivot Table which will be far more use.

The Pivot table suggestion I mentioned will be less messy than your current layout believe me.
Just try it with a sample of data. A Pivot table has a particular functionality that will automatically group dates by quarter as well as a lot of other useful stuff.

OK,

If you have any difficulties with your PT just let us know and upload it. They really are a beautiful construction and should you have other management type information requests to satisfy you'll save yourself hours of time.

Been there and got many T shirts.

..forgot to mention that you'll still need to use a macro to add the calculated, now a 7th field to your Inputs sheet. And rather than put the macro in the sheet activate event which would fire every time you looked at the data. Put it in a module level procedure and just add a button to the worksheet and add the macro to the button.

So something like

Please Login or Register  to view this content.