Oct 12 2021 08:12 AM
Oct 12 2021 08:12 AM
I don't know English enough, but I will try to write quite good
I wrote @mtarler in comments for the same topics.
I use Excel 2019
I need a macro for my job files.
There is Excel book(For example: PLAN)
There is second Excel book(TV)
In PLAN there is a cell(B5), and it contains formula, every day a cell will be change.
I want to save 14 meanings in TV(and also I want to have opportunity in macros to change it, for example 30 meanings). One day-one meaning, and after 14 days I want to macros deletes last meaning. It likes history of happening. It likes a calendar of happenings, and I can change number of days.
In TV I want to have opportunity change row, which I need.
For examle: If 29.09.2021 is last day(for example 14th days) and tomorrow I want to delete 29.09.2021 and Last day will be 30.09.2021.
And if even today my cell is equal to yesterday cell I also want to save it in my history list.
Also I want to macros can update data automatically from PLAN.
I attached 2 files Excel.
I do hope you understand me))
How can I make it?
Oct 13 2021 05:37 AM
Oct 13 2021 05:44 AM
Oct 13 2021 06:15 AM
@Maksim757040 i still don't know what you really need but here is an example of what I mean.
In the 1st sheet is a data entry table with date (you can enter today's date by holding CTRL and typing ; ). Then maybe a couple columns of data (e.g. units sold, price/unit) then a column that has a formula to give you that final value you are interested in.
On the 2nd sheet I have an input cell to easily change how many you want to view the last X number from the previous sheet).
Oct 13 2021 06:53 AM
@mtarler I explain,
I work in a construction company. For example there is one work - Pile driving. Every day on a construction site workers drive piles and 1st day they drive 5 piles, 2nd day they drive 10 piles and etc. After I compare the actual work with the plan and change the percentage (percentage of completion) every day. the percentage - this is a formula and it changes automatically. And I want to save every the percentage every day. I want to save every day and meanings into my calendar.
And sometimes I want to see history of 30 days, sometimes history of 14 days.
And I want to date changes automatically every day(because it's a formula =NOW())
Today 13.10.2021(A1) - 5 piles(B1). Tomorrow I want to see new date in A2 - 14.10.2021 and new B2 - 6 piles. This is a history of 2 days. In B1, B2 ... etc. there is formula and meanings update automatically from other files Excel. But the range is limited because the column E2(for example) contains today's value. And we have for example range between A2 and E2 (5 columns-5days). E2 should be always today's value. If columns A2-E2 contain the value of the latest dates, then tomorrow I want to see E2 = dates tomorrow, D2 = dates today, C2 = dates yesterday and values for each date, respectively
And I want to change value of history's days(30days or maybe 14days).
Excel book TV should show history of days, but meanings should update from Excel book PLAN.
And if now day comes I want to Excel make a new columns
Oct 13 2021 10:58 AM
@Maksim757040 So I renamed a couple columns and the second sheet and also transposed the data on the second sheet ("TV") so it is horizontal. conceptually it is all the same but maybe closer to what you need? As for "automatically" creating the date, I recommend just having them hit CTRL ; to insert todays date but it could be done using a circular reference and change the sheet settings or using a macro (but then we need to change the file to xlsm and permit macros).
see if the attached is close.
Oct 13 2021 09:14 PM
It's close to what I want, but yes, I want to use macros for automatically processes to create the new date.
I created the sheet "DATE" where there are my today's date and info.
This meanings sholud be only in their own cell. NOW(A1), Info(A5).
Okey, next step: there is sheet "History". I don't mind this sheet, it's great to save my history. I understood and accepted your advice. But I want to in "History" automatically updated new date and info from "DATE". For example: A2=DATE!A1 and D2=DATE!A4 (This is my data from which automatically save) and this data goes from up to down. It means that A2 always is today's date and D2 always is today's info(This update happens automatically from the "DATE" sheet, tomorrow it will be one line lower (A3 and D3, respectively))
And finally step:
I want to see on sheet "TV" next: the date and info of the work must be horizontally located. Today's date and info have to be in last column(for example it can be K) and previous dates and info have to go from right to left. And also there is one moment, that column (which is to the righ, for example L) will be contained other info, which don't connect with info in range from K to A.
Also I want to be able to change number of days which I want to show and it should be happened next: I input number of days, which I want to show and Excel hides days and info, which don't fall into the desired range (info in this range has to disappear(and doesn't show error) or columns must be removed)
I'm sure I need macros because there are processes that should run automatically.
Oct 14 2021 02:10 PM
@Maksim7570 i think the attached does everything you want. i added a button to "ADD DATA" because if I made it automatic on update then every time someone made a mistake they have to go into the history page and delete that row. They still do if they make a mistake but at least they have to actually hit the button before it adds that data.
You say you need the macro because things are done automatically but that isn't true at all. The ONLY thing this macro is doing is copying the data into the table on the history sheet. The creation of the table on the TV page including the automatic update of that table based on the # of day you want to show is all done by the sheet formulas.
Oct 15 2021 01:18 AM
Your decision with the button looks great, but I have one problem)
My Excel doesn't recognize these formulas and maybe there are other decisions? other function or macro?
Oct 15 2021 08:42 AMSolution
@Maksim7570 I'm sorry, you did say you have Excel 2019. see attached:
I created formulas for 30 columns to see up to 30 days but you could fill right for more if needed.
That said, in previous post you said you wanted another value to the right of those cells and you said for example the data is in A:K and you would have something in L but that would have to be in AF or so to have up to 30 columns available for the data. Or incorporate that cell information as an alternative formula/data in the formula I created based on it being X columns more than then total requested...
Oct 17 2021 08:18 PM
Oct 17 2021 08:20 PM