Forum Discussion
Formula Help
I have a workbook that produces 2 percentages when data is feed into it from another workbook. In this workbook I type in a specific cell the name of the workbook which includes a date. When I open this workbook the name I typed in, the data pulls into the workbook that produces the percentages. I am trying to create a formula that will pull the percentages and the date of the workbook name and place them in a calendar on the date as depicted in the file name that was typed in. The calendar is an excel calendar.
The attachment UnitAvailabilityDetails02_07_2023 is where the counts are coming from based on the category the info is in. It supplies the numbers that get feed into the Monday Morning Report workbook. The Monday Morning Report Calculation workbook is where the data feeds into and where the File Name resides that gets changed whenever a new UnitAvailabilityDetails report gets produced. The attached calendar is where I'm trying to get the percentages and date from the file name recorded to. This is the formula help I a looking for.
Can I get any help with this formula?
- It appears the Monday report makes calculations but those calculation will change based on the date/file you are using. And now you want to 'capture' the calculation into your calendar file on the date it was done/referencing. The problem is that worksheet function always update based on the current data and will not 'save' the prior data. So you open the files for Monday Jan 2 and it could in theory fill in Monday Jan 2 and then you open files for Mon Jan 9 and it could fill in Jan 9 but the data in Jan 2 will go away. Basically you need to just copy and paste the values to make them permanent. Alternatively you could have a macro do that step for you but seems a bit much for a simple copy paste (you could/should make it easier by adding a formula in the Monday Report that creates the cell text exactly the way you want to paste it into the calendar)
- How do I use the countif function ignoring hidden cells in excel?
- abdulhman_al_bogami you should really start a new thread as this question doesn't have to do with this thread. In answer, I don't think you can. You should use COUNTIFS and add all the conditions you need to filter down to only what you want
Carl_61 If you have the original source files like UnitAvailabilityDetails02_07_2023 where each is dated and the dates are predictable (i.e. every monday) then you might be able to have a formula in each cell that would 'point' to a corresponding filename and skip the monday report file that will have values changing each time. You would probably want to use INDIRECT or a macro to make entering all those formulas easier.
