Beyond Vlookup ... Report?

Stefano Ferretti

Hi All,

I'm in a bit of a situation and I'm looking for ideas...

I have a .csv that I import into smartsheet.

It is quite a big file that contains a lot of data. I need to copy some of this data in different sheet depending on a customer name.

So to simplify (big time) let's say the below is my table

I have different sheet, one for each customer, and I want to copy the entries for each customer in their own sheet.

I immediately thought of Vlookup, but this has a couple of limitation (for me limited knowledge at least):

• it only goes left to right, meaning I would be only able to copy values on the right of 'Customer Name' ]
• I only managed to get the value corresponding to the first row it matches.

So if I use Vlookup to search for customer 'Example1' and give me the 'Data' on the same row, I will only get 'A' as a result, and never 'G'

My second thought was to create a report for each of the customer. All good here, but I need that data in a sheet as other, different columns are required.

So my question is: Can I copy or import rows from a report into a sheet? or are there other means to use Vlookup (or similar) for my task?

Thanks

Stefano

Paul Newcome

You can update cells in reports. The cell cannot be locked and cannot contain a formula, and the user must have at least editor permissions to the report.

Andrée Starå

@Stefano Ferretti

✅Please help the Community by marking your post with the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Paul Newcome

To get the results of a report into a sheet, you would need to export the report as an excel file and then import that excel file into a sheet.

In your example above, would you need each customer "Example 1" to be no its own row

Example 1........A

Example 1........G

or could you have it more along the lines of

Example 1..........A, G

Stefano Ferretti

Hi Paul,

thanks for the reply.

Exporting the reports (and then re-importing those ) wouldn't really make much sense unfortunately.

To your question, each row represents a separate project, so you would not have the

"Example 1 .... A,G"

Paul Newcome

Ok. Why can't the additional columns in the new sheet be in the original sheet?

Stefano Ferretti

not sure I follow...

what additional columns?

Paul Newcome

In your original post you said:

"My second thought was to create a report for each of the customer. All good here, but I need that data in a sheet as other, different columns are required."

Stefano Ferretti

ah, ok.

well, other users need to fill in those other columns, each user for a specific set of customers (reason why I have separate sheets), and they cannot write that data at 'source' so to speak.

I'm exploring an Index / Match type of formula that hopefully will allow me to go around this roadblock. will keep posted

Paul Newcome

Reports could work for this. You can specify which rows and columns are shown in the report and users can update reports which would update the "master sheet".

Stefano Ferretti

I thought you couldn't edit cell content in a report ? did I get that wrong?

Andrée Starå

@Stefano Ferretti

A report is basically another view of one or multiple sheets.

Stefano Ferretti

Hi Andrée ,

exactly, so I can use the report to show what I need (all the Customers column and Project column etc), but the users won't be able to add data unless they have access to the master sheet, and manually search the right row where to put the data.

Doable with small amount of data, but not if the master sheet has 1100 rows...

Paul Newcome

When building the report you can set it to show only rows that meet certain requirements, so you would not have to show all 1100 rows in the report.

Stefano Ferretti

Sorry, I think we're going off track.

No issue with creating / managing reports. My problem is that a report is no good, because I need other columns (that other users need to fill with data) , and I cannot add those to a report.

I could add the columns to the source master file, but it would do no good, because the other users would not be able to fill them in the report.

So Report is not the way to go

Paul Newcome

If you add the columns to the master file, you can include them in the report and users can update the report which in turn update the master file. Users CAN update cells in reports.

Stefano Ferretti

well, I tried, but it doesn't seem you can update a cell in a report ?

Paul Newcome

You can update cells in reports. The cell cannot be locked and cannot contain a formula, and the user must have at least editor permissions to the report.