Forum Discussion
Create a MACRO to save previous values of a changed Row in new ones (creating a database in Excel)
Aless1275 OK, I'm not sure the bigger context of what you're doing and think other solutions may work for you. Second, I ignored the code you sent as you said it doesn't do what you want. So in the attached I added a very small macro that will copy the row from "Inputs" to "Database". While I was at it, since it appears you want this as some sort of audit trail I also added timestamp and user to that record and also made it a table so you can easily sort or filter those records. I hope this helps but if I knew more about the context I might suggest some other solution(s). For example your insistence of the newest value on top is trivial to do but also easily performed using sorting of the table (at least now that I added the timestamp). Well, best of luck.
Aless1275 OK, I'm not sure the bigger context of what you're doing and think other solutions may work for you. Second, I ignored the code you sent as you said it doesn't do what you want. So in the attached I added a very small macro that will copy the row from "Inputs" to "Database". While I was at it, since it appears you want this as some sort of audit trail I also added timestamp and user to that record and also made it a table so you can easily sort or filter those records. I hope this helps but if I knew more about the context I might suggest some other solution(s). For example your insistence of the newest value on top is trivial to do but also easily performed using sorting of the table (at least now that I added the timestamp). Well, best of luck.
I will now try to implement it in the model. To give you a better picture of what I need, I'm a student working on an economic model for a Renewable-energy power plant. In this model there are some parameters as input that can be modified (in the input sheet), like the size of the plant, its type etc... These values are used in other sheets that i made which give as a result some economic parameters. So, to see how these results change depending on the input variables, i needed some way to store the results after performing the change in the input, without having it overwritten. This to then create tables/graphs and analyse which impact these input variables have on the economic feasibility of the project. If you think there is a better way to do it i'm totally open to learn more from you! Thanks a lot again
- well it sounds like you are doing a hunt and peck sort of operation trying to find good/bad values. I would suggest you create a table in which each column is calculated based on previous columns (i.e. instead of your formulas being in 1 cell they are in a column in your table) and then you can enter a whole column of numbers or a series.
Alternatively with the new dynamic array capabilities of Excel you can have a single formula work on a column of numbers and return an array or results (e..g. =A1:A20 + 5 will return an array of numbers/cells that are exactly 5 more than the cells in column A using only 1 formula in cell B1 for example)
That all said if your formula and calculation are very complicated and require significant processing then maybe it is better to not tax excel using so may spreadsheet formulas but then maybe you should consider having VBA actually calculate all the values and paste then in.
Hope that helps and best of luck with that project.- I will try also these solutions, thanks a lot for your help, it has been super useful!!!