Return Multiple Rows Using VLOOKUP and COLLECT

Adam Rosenzweig

Good morning,

I am working on a formula which will return multiple rows of data from a different sheet based on criteria.

For context, I am looking to view all the cells from sheet A (defined in a range) where there is a value like "BA Edit" in sheet B. I have tried doing this several ways, with no success.

=if(CONTAINS({BA Edit 2}, "BA Edit"), {BA Edit 2}@row) is #unparseable

=IF(CONTAINS("BA Edit", {BA Edit 2}), {BA Edit 2}, "False") is #invalid column value

=VLOOKUP(CONTAINS([Primary Column]@row, {BA Edit 2}), 1, 0)) is #unparseable (note [Primary Column]@row has the value of "BA Edit"

=COUNTIF({BA Edit 2}, =(CONTAINS("BA Edits", {BA Edit 2}))) returns a value of 0, despite there being approximately 40 in the range.

Any help is appreciated!!

Thank you all,

Adam

Paul Newcome

I'm not sure I follow exactly what you are trying to do. Are you able to provide some screenshots with mock data manually entered that shows your desired end result?

Adam Rosenzweig

Thank you Paul, for getting back to me. I am not sure I can provide a screen capture that would help, but let me try and better explain the context.

I have two sheets:

• project plan sheet (sheet A)
• data mining sheet (sheet B)

In sheet B, I would like to display all the tasks from sheet A that are related to a certain job function (BA Edits, for example).

Does that make more sense?

Adam Rosenzweig

Here is a mock-up

Paul Newcome

Have you looked into a report?

Adam Rosenzweig

The report is the endgame. First I need to take the scrubbed data and perform calculations on each row, then I would further filter into a report.

Paul Newcome

What types of calculations are you trying to run? Could you run those calculations on the main sheet? Are you able to give a broader explanation of what you are working on? There may be a different solution other than attempting to use formulas just to pull base data from multiple rows into a separate sheet.

Adam Rosenzweig

Thanks for sticking with me Paul!

I didnt describe what I was doing very well. For this particular use case, I am building a sheet which displays the predecessor/successor relationship between tasks.

As an example, what I am attempting to do:

• is create a sheet that displays only the tasks that contain the text "BA Initial Draft" (in column A below) and brings with it associated data from the row (Columns B-E, which I am currently doing with a formula based on Column A).
• Then I would want to automatically populate the successor task which will always contain the text "Requirements Review" on the same row based on the data in column A.

This way I no longer have to manually adjust the sheet when new "BA Initial Draft" data is entered.

I hope this makes some sense 🤣

Paul Newcome

Ok. But why are you pulling it to a separate sheet in the first place?

Adam Rosenzweig

I guess I could put it all in the master sheet, but I feel like I would have the same issue with formulas in my auto population of the successor task in a different column.

Paul Newcome

As long as you have a column that contains some unique data for each row, you should be able to adjust your formulas. Are you able to mock up a sheet that would show the end result and provide your formulas that you are currently using to pull the data in the different columns?

Adam Rosenzweig

I was working on this on and off all day. If I try pulling the information into the same sheet (new column), my vlookup isnt working.

=VLOOKUP(Predecessors@row, [Row ID]:[Row ID], 1, 1)

Adam Rosenzweig

Paul, thank you for your help, but I am going to work on a different way of working my problem. I will post here when I figure something out.

Paul Newcome

Ok. Sounds good.

Adam Rosenzweig

For what its worth, I sent this over to smartsheet customersuccess and they're still trying to figure it out :)