How do I automatically pull a value from a cell in one sheet into another sheet?

Hi. I have two sheets:

Sheet 1 - a Work Order Submission Log

Sheet 2 - a Project Log

When a work order is submitted to Sheet 1, a Work Order # is generated in the Auto-Number column of that sheet. Each Work Order # is unique, but they all have the same suffix ("_wo").

I would like the Work Order # from Sheet 1 to automatically appear in Sheet 2, under the same column name. The criteria is merely that it exists: if it shows up in Sheet 1, it should show up in Sheet 2.

I assume I want to use a column formula, perhaps using the INDEX function with cross sheet referencing? Or can I set up cell linking to occur automatically? I can't seem to make any of my attempts work.

Thanks for any and all help!!

Find more posts tagged with

Formulas

Cell linking

Accepted answers

All comments

Amanda Alv

Hey there,

Quick question- is there already an existing row in Sheet 2 for the new work order and only the Invoice number should be inserted? Or can the entire row from Sheet 1 be copied over to Sheet 2, once an Invoice number has been populated?

If you can copy the entire row, hiding the unwanted columns in sheet 2, a simple workflow automation would solve it. If not...that's a different story!

E. Cooper Moore

Hi! Thanks for responding!

There is not already an existing row in Sheet 2. The row in Sheet 2 would be generated when the auto-number got entered from Sheet 1, kind of like a new form submission.

I don't think the entire row from Sheet 1 should be copied over, as Sheet 2 shares only a few data points (columns) with Sheet 1. Sheet 2 has additional columns that need to get filled out after the data from Sheet 1 is entered.

In reading some other posts about this topic, I am thinking that what I want to accomplish might not be possible, even though it seems like a simple concept. What do you think?

Andrée Starå

Hi @E. Cooper Moore

I hope you're well and safe!

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.

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

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

E. Cooper Moore

Hi @Andrée Starå.

As I mentioned already, I thought an INDEX function might be the way to go (VLOOKUP does more than I need), but it is unclear to me if I can make it work. What would the actual formula be, so that the Work Order # that is automatically generated in Sheet 1 (in an Auto-Number column) will automatically appear in Sheet 2, thereby creating a new row in Sheet 2?

I do not understand what you are proposing about multiple helper sheets. Why would I copy rows from a source sheet into one helper sheet, and then into another helper sheet, and then into the destination sheet? I want only one value (from one column) automatically copied from Sheet 1 into Sheet 2. Perhaps the two helper sheets will allow me to whittle down the values in the columns to just that one value, which will end up in the destination sheet? Would you mind applying my specific situation to what you are suggesting, so that I can better understand it?

Thank you!

Andrée Starå

@E. Cooper Moore

Happy to help!

The simplest method would be to have an auto-number column in the Source sheet and then a manual number that you fill with as many rows as you need in the Destination sheet. You'd then connect the two with an INDEX/MATCH formula.

Try something like this.

=
INDEX({ColumnWithTheValueYouWantToShow}; MATCH(CellThatHaveTheValueToMatch@row; 
{ColumnWithTheValueToMatchAgainsTheCell}; 0))

Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

More info.

https://help.smartsheet.com/function/index

https://help.smartsheet.com/function/match

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Jon Mark H

@E. Cooper Moore

I would just set up an automatic workflow so that when a row is added in the first sheet - it automatically gets copied to the second sheet -

I did this worklow:

When rows are added, where Work Order ID# is Any Value, Copy Rows - to Page #2

Then make a column formula to tie together the 2 sheets based on this, now automatically shared, WO#, as Andree spelled out.

Andrée Starå

@Jon Mark H

That would work as long as you have all rows in the same position and don't move it around, and as long you're not using any hierarchy. Otherwise, you'd need to add conditions to make that work better.

Sign in to join the conversation!

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.