INDEX LOOUP Workaround

System

This discussion was created from comments split from: Keeping rows updated once they are copied to another sheet..

APicard_AZ

I have a need that when the production schedule is changed, my sheet that adds 8 more columns of calculations also gets changed. I'll talk with our production schedule owner, if i can put the calculations on the main sheet that would solve it, mostly.

but it would be neater if Production controlled their sheet and QC controlled a facsimile with these additional calculations - maybe an index lookup is the only way?

-Alison

Andrée Starå

https://community.smartsheet.com/discussion/comment/483322#Comment_483322

Hi,

I hope you're well and safe!

1️⃣ As you mentioned, you could use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.

2️⃣ Another option would be to use so-called helper sheets. In short, copy the row to a helper sheet, use my previous method to get the values you need to another helper sheet, and then copy/move the row from that sheet to the main destination sheet.

To connect them row by row, you'd use an Autonumber Column in the Source sheet and add a so-called helper column to manually add the row ID on as many rows as you need in the Destination sheet.

Would that work/help?

I hope that helps!

Be safe, and have a fantastic day!

Best,

Andrée Starå | Smartsheet Expert Consultant & Partner / CEO @ WORK BOLD

✅ Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Awesome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!

Marcela

Hello @APicard_AZ!

Just tagging you to make sure you don't miss Andree's response!

Paul Newcome

@APicard_AZ I would suggest putting it all in one sheet and then having two reports (one for production and one for qc). The reports will allow users to interact with the sheet but still allow you to only display the columns that are needed for each.

APicard_AZ

Excellent suggestions, and in my looking i did see a way to delete replicated rows through a RANKEQ/etc helper column to move/delete those extras.

Paul your answer is DEFINITELY the simplest - the only issue is right now the sheet is an excel, and i'm copying into smartsheet which makes a blanket 'update' ..

you know as i say that, then whats stopping me from just making my 'production' smartsheet have extra columns?? Thank you for the suggestions friends!
lets see what i can finagle and report back ;)

Andrée Starå

https://community.smartsheet.com/discussion/comment/483425#Comment_483425

Excellent!

Happy to help!

✅ Remember! Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Awesome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!

Paul Newcome

Happy to help. 👍️

APicard_AZ

single sheet with a few reports/views off of it definitely works best in my case :)