Help Transforming a spreadsheet in to readable data

[chelseariver90]

Hi,

We have hundreds of spreadsheets with budget information as a tab but formatted in a way the estimators use to view it. We want to pull all this information into one table but the way it is laid out and separated across so many spreadsheets it is difficult to do. I've attached the Spreadsheet below, basically, the values just get copied and pasted in to the tab by the estimators every time a job is awarded. If it could be made in to one table recognized by the Job Number from Column C cell 1 with columns for all of the categories and subcategories in column C but also with the costings for the Site & Workshop for each category too.

Hope this makes sense.

Thanks

[danilang]

Hi @chelseariver90 

I did indeed upload the incorrect package.  I've updated the previous post with the new one  F5 Error (fixed).yxzp

Dan

[chelseariver90]

Thanks Again Dan it is working and reading in all the sheets 🙂 You're a legend.

Just wondering if there is away so that it only reads the first 46 rows and to column H of each sheet just incase anyone puts anything else in to the sheet that shouldnt be there? I tried using the sample tool for the first 46 rows but then it only does it for the first sheet it reads in and doesnt give me the other sheets when i have it in there after the macro tool.

Thanks a lot.

James

[danilang]

Hi @chelseariver90 

If you put the Sample in the main workflow after the macro, make sure to group by FileName in the config.  this will give you the first 46 rows of each sheet.  

Here's an updated version.  In the template for the Dynamic Input, you can specify that you only want to read 46 rows

To limit the columns, I added a Dynamic Select tool that only returns the first 8 columns and the FileName

 

Both of the changes are in the Budget Sheets macro

Dan