use a reference sheet to gather data for another sheet the rows will differ between 2 sheets

BillyBossier

Hi All,

I've been pulling my hair out and I'm hoping someone has advice. Bing Copilot is running me in circles because it doesn't understand.

I have a sheet with SAP appeals that all steps have been completed (SAP completed sheet) going forward in the example. If the student turns in an appeal to the decision then they are added to a new sheet (SAPAC appeal)

I want to add a column formula to the SAPAC appeal sheet that looks for the student id on the SAP completed sheet and fills in a row. The example I'm working from (though I hope to repeat the process with several rows) will pull the row ID from SAP Completed sheet and put it in a column on the SAPAC appeal sheet based on a match to the student ID in the SAPAC sheet.

This is what I have so far and I'm not sure.

=VLOOKUP({[Completed SAP Appeal 242 moving forward]![[Student EID]]@row}, [Row ID], FALSE)

bonus points if you can tell me a way to transfer the documents and comments from that first column to the sheet :) I'm willing to use an automation but don't have the foggiest idea where to start to create it.

Jason Albrecht

Hi @BillyBossier,

Forgive me for asking a few clarifying questions and for requesting a few (redacted) screenshots, to help us visualise your requirement. Before asking the questions, I note the SmartSheet VLOOKUP syntax is:

=VLOOKUP(search_value,lookup_table,column_num,[match_type])

• from what you can tell, is your formula matching this syntax? I've not seen an exclamation mark used in Smartsheet formulas before?
• Is [Row ID] a column you created within the SAP sheet, or are you referring to the SmartSheet row number?
• I'm not sure I fully appreciate what data is being entered into the SAPAC sheet to trigger the VLOOKUP. Is the Student's ID entered (if so, is this via the Student completing a form to appeal the decision?), or is some other data being entered in the sheet? If it is some other data, what is it, how is it entered and is it already in the SAP sheet?

You may (or may not) find the following resources helpful. It can take some time to absorb, but it's well worthwhile:

VLOOKUP Examples: An Intermediate Guide | Smartsheet

YouTube: How to do a Vlookup in Smartsheet - Step by Step

YouTube: VLOOKUP Between Two Worksheets | How to VLOOKUP From Another Sheet

Jason Albrecht

Hi @BillyBossier,

Forgive me for asking a few clarifying questions and for requesting a few (redacted) screenshots, to help us visualise your requirement. Before asking the questions, I note the SmartSheet VLOOKUP syntax is:

=VLOOKUP(search_value,lookup_table,column_num,[match_type])

• from what you can tell, is your formula matching this syntax? I've not seen an exclamation mark used in Smartsheet formulas before?
• Is [Row ID] a column you created within the SAP sheet, or are you referring to the SmartSheet row number?
• I'm not sure I fully appreciate what data is being entered into the SAPAC sheet to trigger the VLOOKUP. Is the Student's ID entered (if so, is this via the Student completing a form to appeal the decision?), or is some other data being entered in the sheet? If it is some other data, what is it, how is it entered and is it already in the SAP sheet?

You may (or may not) find the following resources helpful. It can take some time to absorb, but it's well worthwhile:

VLOOKUP Examples: An Intermediate Guide | Smartsheet

YouTube: How to do a Vlookup in Smartsheet - Step by Step

YouTube: VLOOKUP Between Two Worksheets | How to VLOOKUP From Another Sheet

BillyBossier

Hi Jason its going to take me a bit to absorb this and see if it solves my issue.

In my mental picture I was going to add the EID to the SAPAC sheet and it will get the "row ID" I created a column for Row ID but it matches actual row number (based on another request to make it match). I'm hoping to use it to provide our Leadership level committee information on the original appeal in an easy to parse method. So for instance I would create a VLookup that looks in the SAP sheet and when it detects the row that has a matching Student EID and add the answer for the "Row ID" another column that gathers the semester of the appeal for instance. I will try to piece tother a test file.

Off to do a little training because I can really see the use of vlookup on other sheets if I can ever get it to work.

BillyBossier

Here are the promised image. top images is the new sheet where the VLookup is second image is the source sheet.

Jason Albrecht

Thank you @BillyBossier, for the extra information.

I can see @EmilyK is on the right track for Excel.

We will need to make some modifications for the formula to work in Smartsheet. Maybe try this:

=VLOOKUP([Student EID]@row, {Student EID to Row ID}, 4, false)

Note: {Student EID to Row ID} is the name I gave to columns I highlighted when I clicked on the Reference Another Sheet link in the VLOOKUP Help

Here are the steps to follow, if you want to understand how it's created (otherwise, copy and paste)...

1. In the top cell of the [Original Row ID] column in the SAPAC sheet, start typing =VLOOKUP(
2. When the search_value is highlighted yellow (as shown in the picture above), click on the top cell of the [Student EID] column in the same sheet. While my picture shows a different name, yours should now say [Student EID]@row (NB: you can simply paste this or type it as well). Now enter a comma "," to move to the next section of the function.
3. The lookup_table should now be highlighted yellow. You now need to click on Reference Another Sheet (in blue and underlined, per the picture above). A pop-up box will appear, ready for you to 'Search for a data source'. You want to locate the SAP sheet so that an example of it will appear in the right section of the pop-up window. Use the horizontal scroll bar (at the bottom) so that the [Student EID] column is over to the left. Hopefully you will see [Row ID] four(4) columns over (counting the [Student EID] column as #1). Keep the Shift button pressed on your keyboard while you use your mouse to first click on the [Student EID] column name and then click on the [Row ID] column name so that all four columns are highlighted blue. You can now name this range in the field under "Sheet reference name". This is where I named the range "Student EID to Row ID". To close the pop-up window and continue with your formula, click the blue "Insert Reference" button in the bottom right. Here's a video and help page, to help visualise the steps. Don't forget to enter a comma "," to move to the next section of the function.
4. Enter "4," to indicate the [Row ID] is the fourth column along from the [Student EID] column. Note the comma.
5. Enter "False" (without the quotation marks), and press enter.

You should now have formula provided earlier.

To now make this a Column Formula, right click the cell and choose the last option.

As for the bonus points regarding the automation of copying over the attachments, you'll want to look into Zapier or some other 3rd party API. As this post from 2019 discusses, there is currently no ability to automate copying the attachments to another row on another sheet. You can automate the whole row to be copied over, which includes the attachment, but it will add all the other (missing) columns from your source sheet to your destination sheet as well.

Let us know how you go.