I am trying to get to the final Phase of a task I am on and I have to ask for assistance.
Using formulas others have assisted me with, I need to place them in a separate Workbook, that when opened draws on other Workbooks to provide me answers to my inquiry. What I mean is I created a separate Workbook called "Percentages". I want to place the below Formulas in the Percentages Workbook. The problem is however, the Workbook Names generated by the program from which the Reports come from. Here are the names produced by the program that produces the reports:
Copy of UnitVacancy10_13_2021.xlsx
Copy of Unit Avalability Report 12 Oct 21.xlsx
Yes, Availability is spelt wrong. The word is generated from the program that produces the report and the report name.
The issue is the dates as they are shown and incorporated into the Workbook names are based on the dates the report was produced. This date will change each different day the report is run. So the question is how I account for the always changing report dates in the Workbook name. Another words the Reference Name as shown in the names above.
I need the below Formulas to be placed in the “Percentages” Workbook with reference to the other Workbooks but at the same time, modify the formula Date part.
Formula for Copy of Unit Avalability Report.xlsx
=TEXT((317-MID(INDEX(A:A,MATCH("Total Pending Down Unrented:*",A:A,0)),FIND(":",INDEX(A:A,MATCH("Total Pending Down Unrented:*",A:A,0)))+2,100)-MID(INDEX(A:A,MATCH("Total Down:*",A:A,0)),FIND(":",INDEX(A:A,MATCH("Total Down:*",A:A,0)))+2,100))/317,"00.00%")
Formula for Copy of UnitVacancy Repot.xlsx
=TEXT(1-TRIM(LEFT(SUBSTITUTE(INDEX(D:D,MATCH("Grand Total Count",A:A,0))," ",REPT(" ",255)),255))/317,"00.00%")
Percentages.xlsx is the Workbook name I need to have the above formulas in.
It is most likely that the two “Copy of” reports as shown above will be run one after the other and followed-up by opening the Percentages.xlsx workbook.
Thank you to anyone that can help me figure this out.
Carl
@Carl_61
