Forum Discussion

BenGoddard's avatar
BenGoddard
Copper Contributor
Apr 05, 2022

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.

 

  1. How do I automatically turn the Form return for a number with the apostrophe into a usable number
  2. 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!

No RepliesBe the first to reply

Resources