Automation / VLOOKUP help

cochrank

Hello!

My initial gripe comes from wanting to copy only certain cells from a row into another sheet. I've read a bunch of the threads for create a helper sheet, add dummy columns, and then use VLOOKUP, but I just cannot get it to work for myself and I'm uncertain what I'm missing!

I have Sheet A that contains all my raw data. Sheet A has an automation when "Fail" is entered in Column "Pass/Fail," it copies an entire row onto Sheet B. Then I have Sheet C, which I want to use the VLOOKUP formula to find data from several columns of Sheet B.

For context, each "Fail" is tied to a failure # and Sheet C's purpose is to investigate that failure, so that's why I want to copy so much information (Part #, Vendor Name, Failure Date) to Sheet C to prevent transcription errors.

At this moment I went back to the starting blocks and have removed the dummy columns. Can someone please help educate me which sheets the dummy columns should go on and how I can use the VLOOKUP formula for this scenario?

Thanks so much!

DKazatsky2

Hi @cochrank,

Is the purpose of Sheet B only to hold the failed rows as a middleman to Sheet C? If so, I very simple workaround is to just hide all the columns in Sheet B you are not interested in for the investigation. The columns would still exist, they just wouldn't be visible unless unhidden.

Hope this helps,

Dave

cochrank

That's a really great idea, however, I just tried this and each time the automation is run, the columns unhide themselves.

Amit Wadhwani

Hi @cochrank

The failed items from Sheet A get copied to Sheet B, which has some additional columns to record investigation related information. The challenge that remains is columns being unhidden automatically. One way of doing this could be to create a report out of Sheet B, that only shows the columns that you want, and you can work off the report. You would miss on some features, but you should be able to do most of the work.

If you still prefer to do it using formula, I would recommend below

1. Automation that copies all the data in Sheet B.
2. Add an Auto Number column to Sheet B (assuming 1 to 1,000)
3. Add an Auto Number column to Sheet C (assuming 1 to 1,000)
4. Create a cross-sheet reference to Sheet B in Sheet C https://help.smartsheet.com/articles/2482644-create-cross-sheet-references?
5. You can the use VLOOKUP (or Index Match preferably) to get data against each serial number into Sheet C as new data gets added to Sheet B.

Let me know if you would need some help getting this built.

cochrank

Hi @Amit Wadhwani I can't quite get the failure number from Sheet B to Sheet C. I believe I'm missing an automation somewhere :(

Amit Wadhwani

Hi @cochrank

You don't need an automation there. Let's connect on LinkedIn and I can help you on a screenshare.