I want to use vlookup to pull data from 1 sheet to another, but it isn't working, & I don't know why

teb

Hi @Genevieve P. =JOIN(COLLECT({Bill Number}, {Unique Value}, "yyy"), ", ") If the unique value is not the row(s) from where I want the data pulled, I don't know what it would be.

I thought the unique qualifier was me telling the sheet to use which column number and what row numbers. Aside from being able to use the column and row numbers to identify the desired data, there is not another way to do this as far as I can tell.

I can use the specific bill numbers as the unique value, but at this point, it is seeming easier to just enter the data by hand. What is the difference in me entering the data by hand into a second sheet and me entering the data by hand into a formula to pull into the second sheet. Either way, I am still entering all of the data by hand somewhere, and I am not saving any time. I must be missing something.

I thought Smartsheet could pull large amounts of data from one sheet to another, data that didn't have a common name, such as different invoice numbers, customer numbers, etc. When a customer receives a monthly statement, the monthly statement often has multiple invoices. I would need the multiple invoice numbers from multiple rows in one sheet to be pulled into one row in one cell of the second sheet.

I have enrolled in a class for formulas through Smartsheet University. Maybe that will help. I reached out to Smartsheet support last week. The representative said the issue would need to be escalated because he did not know how to construct a formula of this type and that I should hear something Monday or Tuesday, but I have not heard back since.

I appreciate your help. I just don't know what to do next. Thank you.

Genevieve P.

Hi @teb

The range where you specify "yyy" is doing exactly that: telling the formula what row number to bring in. However there has to be something in the grid of the sheet which identifies the row numbers to include and the ones to exclude. Something to filter down and ignore other data.

If only you know what rows to include based on reading the data, then yes, that would have to be manual work: formulas and sheets won't be able to make a judgement call on your behalf. They need identifiers and markers to read and evaluate, based on conditions and criteria you specify. Does that make a bit more sense?

The cleanest way I can describe it is using a Filter.

Looking at Sheet 1, how would you put a Filter on the sheet so only the 3 values you want would appear and the other rows are hidden. If there's something you can put in (eg. client name, or status), then we can use that in a formula. If it's a manual process of simply "knowing" what rows you want to show, then it would need to be a manual process of selecting those rows for the filter (perhaps using a checkbox column).

Lets use the example you wrote about above:

When a customer receives a monthly statement, the monthly statement often has multiple invoices. I would need the multiple invoice numbers from multiple rows in one sheet to be pulled into one row in one cell of the second sheet.

You have identified 2 criteria: month and customer.

As long as those two criteria are housed somewhere in the grid of Sheet 1, we can "filter" the rows to find all of the Invoice Numbers based on that Customer and that Date:

=JOIN(COLLECT({Invoice Number}, {Customer Column}, "Customer", {Date Column}, MONTH(@cell) = 4), ", ")

Alternate options are to create a Report to filter down your rows, but it wouldn't gather data into one cell for you.

Let me know if you still need help; if so, it would be useful to see more of your sheet, not just the data points you're trying to join together, but why you're joining them together and what the criteria is for each grouping.

Thanks!

Genevieve

teb

@Genevieve P. I used invoices and statements as example; however, that is not what I am doing. It might be best if we could do a Zoom where I could share my screen. I do not have permission from my employer to share anymore data than I already have in a public venue. I do have permission to share on a private Zoom meeting, though. Would you be willing to do that?

Genevieve P.

Hi @teb

I would suggest at this point booking a core application Pro Desk session if your plan has access to it. These are 30-minute coaching sessions over screen share, so you'll be able to show both sheets and identify how you are grouping the data that you want to appear in Sheet 2.

Here's the link to book a Pro Desk session: https://www.smartsheet.com/pro-desk

Cheers,

Genevieve

teb

Hello @Genevieve P. If my company has that option, I will use it. I reached out to the IT department to find out. Again, I appreciate your assistance. You have been very generous with your time.