This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I have the attached workflow that uses a macro to look through our job directories find spreadsheets that contain a sheet called Master Budget and then pull the information from it. The problem i'm having is that the gyms have now changed the template to the attached Dummy Budget 1 format.
In cell B1 is the Job number that i need to pull in order to run joins off after we have the information. How can i pull that number out with the Prefix TP. they have made it a custom format so that it doesnt recognize the TP no: at the start. see below.
I would like to pull the information in to a data table that would also take figure from the Grand total column beginning in column F12. If the table could be outputted as below.
The directory will have to run through about 500+ of these sheets but they should be in the exact same format as these.
They did more than just change the format of the TP No. The entire file is different. Attached is the original file (Budget Original) and the current one. The key to the whole process is first multi-row formula that determines the row type, H1, H2, etc. Here the formula that's used
if isempty([Row-1:Row Type]) then
elseif [F5] = "GRAND TOTAL INC. WASTE" then
elseif [F2]="MARK-UP (%)" then
elseif [F5] = "GRAND TOTAL INC. MARK-UP" then
elseif [F1] = "1" then
As you can see, It's looking for specific strings in specific columns. These have changed. For instance, the first else looks for "GRAND TOTAL INC. WASTE". This header is gone. As is the "Mark-UP (%)" label. "Grand Total Inc. Mark-up" is now "Grand Total"
Before you had 4 header sections before the data started. Now you have only 3.
Modify the multi-row formula to find these 3 sections using the corresponding start header text.
For the formatting problem, I've added a Multi-row in the Job... container that adds TP NO: if the row after it contains "Quote No:".