Populate from Forms for Excel return by date and reference
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 ...
1 4/4/22 ### Email address removed Alan Loc-B '2 '742 '355
2 4/4/22 ### Email address removed Ben Loc-F '3 '51 '4
3 1/5/22 ### Email address removed Alan Loc-B '742 '4 '1
I'd like the output to look a little like this:
Asset Apr22 May22 Jun22 ...
1 Loc-B
2 Loc-B
3 Loc-F
4 Loc-F Loc-B
I hope that's clear enough, and I look forward to learning what you whizzes recommend!