We have 800 assets that are mobile between sites. Every site completes a return each month to say which assets are at each location. The return is on a Forms for Excel with the simple layout: Location (option), Asset 1 (number), Asset 2 (number), Asset 3 (number)...
I'm trying to create an output tab that lists all the asset numbers as rows, and the month/year as columns, and populates the reported location for each asset in each month (so when we need to do compliance testing we know where to look!).
I want the sheet to be entirely automated so we only have to interact when we need to find an asset.
How do I automatically turn the Form return for a number with the apostrophe into a usable number
Is this some Index/Match function or a Pivot table or something completely different?
The output form the form looks like this:
ID Start Completion email Name Location Asset1 Asset2 Asset3 ...