Retrieve a value from another worksheet

M Johnston

I'm new to Smartsheet and could use help.
I want to retrieve a Code Description text field value that exists in another worksheet (Code Master) that is based on a Code field that is in both sheets. Then concatenate them as; Code "-" Code Description. I thought a VLOOKUP would work, but not getting results.

Andrée Starå

https://community.smartsheet.com/discussion/123039/retrieve-a-value-from-another-worksheet

Hi,

I hope you're well and safe!

Can you share some screenshots? (Please delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / 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 ⬆️ - Aweseome ❤️ - 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!

M Johnston

Here tis'! In old school programming, reading Budget Detail Worksheet taking 'Cat' and 'Obj' fields, chain out to Categories Master worksheet and grabbing the description to return a concatenated field of 'Category' created in Budget Detail Worksheet separated by a "-".

Paul Newcome

You would use something along the lines of…

=Obj@row + " - " + INDEX(COLLECT({Master Sheet Description Column}, {Master Sheet Category Column}, @cell = Cat@row, {Master Sheet Object Code Column}, @cell = Obj@row), 1)

M Johnston

I'm getting a #Invalid Operation message, even tried to put the Categories Master instead of just Master for the Index Collect.

M Johnston

My latest is below. Getting #Unparseable message. Need to get the OBJECT DESCRIPTION from the Categories Master sheet back to the Budget Detail Worksheet. Question too, how does the formula know to go to the Categories Master sheet?

=Obj@row + " - " + INDEX(COLLECT({OBJECT DESCRIPTION}, {CATEGORY}, @cell = Cat@row, {Object Code}, @row = Obj@row), 1)

Paul Newcome

You have an "@row" where you should have "@cell".

=Obj@row + " - " + INDEX(COLLECT({OBJECT DESCRIPTION}, {CATEGORY}, @cell = Cat@row, {Object Code}, @cell = Obj@row), 1)

M Johnston

Thank you Paul - this gives me an #Invalid Operation message. I just want to bring back the description as 2nd part of the concatenation. Is likely something small, and I'm still not sure how it knows to go to the Categories Master to collect the description… I'm an old school programmer and newish to SS.

Paul Newcome

What steps exactly are you taking and what are you selecting when you create each of the {Cross Sheet References}?

M Johnston

Sorry - but I do not understand the question as I'm only doing a copy/paste of your suggestion. I'm wanting to take the Cat and Obj columns from the Budget Detail Worksheet to get the corresponding line in the Categories Master (CATEGORY and OBJECT CODE) to bring back the OBJECT DESCRIPTION.

M Johnston

Tried again to 'speak' to Smartsheet AI and they say the below:

Paul Newcome

Take a read through this article. Smartsheet does support cross sheet references in formulas.

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

M Johnston

I have it figured out - and found that I had numeric data being uploaded as text. Thanks for the help!

Andrée Starå

https://community.smartsheet.com/discussion/comment/439900#Comment_439900

Excellent!

Glad you got it working!

✅ Please support the Community by marking your comment as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!