Getting sheets to talk to one another

Tonya Sloan

I have two different sheets and I need them to automatically populate cell data when data is entered. I have sheet A (my department sheet) that has many columns and rows (many of which won't apply to B) and I have sheet B (another department sheet) that has a lot less columns and rows. I have set-up a column in each sheet that will host a unique ID, so that the IDs match (what is in sheet A will be in sheet B) for search capabilities. When someone enters data in specific cells of sheet B, I need sheet A to be populated. When I enter data in specific cells, which will be different cells, of sheet A, I need sheet B to be populated. I started small by having sheet A run a formula to pull in existing content from a cell in sheet B. But I can't get it to work. I've tried VLOOKUP (=IFERROR(IF([CAR code]@row = "", "", VLOOKUP([CAR code]@row, {CAR/CORPORATE TRACKING R1}, 7, false)), "")), which I understand takes a lot of power to run and I'd like to use something less taxing so I even tried INDEX and INDEX(MATCH), but I can't get it to work either. I'd like to set-up multiple cells within both sheets to auto-populate when data is entered so both departments are sharing details that are needed. For this first test cell, the column is a date column in sheet A and it is a date column in sheet B. I have used many formulas in my many years using Smartsheet, but this one has got me stumped and I know it's simple.

Question: How can I get the formula to work as a one directional function?

Thank you for any assistance you can provide.

Galen T Garrison

Hi @Tonya Sloan ,

I am curious about the answer, as I would like to accomplish the same. Not finding a solution, I use the Move and Copy automations between sheets to accomplish similar. However, doing so duplicates the columns so that both sheets match. In other words, if sheet A has column TicketID and sheet B does not, when you move or copy a row from A to B, Smartsheet will add Ticket ID to B. The converse applies as well. Through automations, you can update a field in either Smartsheet to identify which row of similar data is the most current row when running reports, other automations, etc.

I do hope there is a simple and easy way to accomplish what you want, as I too would like to do it.

Brian Wilson DC

Good morning, @Tonya Sloan and @Galen T Garrison . From reading Tonya's description it sounds like they would like to be able to accumulate data from across three different sheets. With sheets "A" and "B" being data sources and that data being compiled into sheet "C". The common reference point across these three sheets being the "Unique Identifier" which corresponds to the data. I've put together three sheets with five rows each to show what I think you are requesting.

For the first two data sheets it is important to note that the "Date" column actually needs to be a "Date Type" column. It can't be a column that just has dates typed in.

Data Sheet A

Data Sheet B

You will see that on sheet C I was able to INDEX(Match) the data that you were looking for in Data Sheets A and Data Sheets B. Please note that I have set the "date" columns on Data Sheet C as "Date Type", but I did not restrict to dates only. This is because we need to enter in the formula in these cells.

Data Sheet C

To pull the data into each corresponding cell I used INDEX MATCH. For the data from Data Sheet A, my formula is:

=INDEX({Data Sheet A - Start Date}, MATCH([Unique Identifier]@row, {Unique Identifier Row - Sheet A}))

For the data from Data Sheet B, my formula is:

=INDEX({Start Date - Sheet B}, MATCH([Unique Identifier]@row, {Unique Identifier Column - Sheet B}))

Essentially, INDEX the range of start dates on the sheet that I would like to pull the data from. If I have a MATCH of my unique identifier at the same row on my data sheet C as I do on the sheet that I am indexing the start date ranges, then pull that information into Sheet C.

Please let me know if this answers your question.

Regards,

Brian

Galen T Garrison

Thanks, @Brian Wilson DC ,

If I read Tonya's post correctly, there are two sheets, where your solution, albeit very clever, introduces a third. In my scenario, I have a many-to-one ratio of many Smartsheets updating via Copy Row automation to a single Smartsheet that uses a Jira connector for updates to and from Jira and sends updates to the individual Smartsheets among the many via copy/move automation. While I would love being able to automatically update specific columns within existing rows using automation, I don't know how to do that (yet) without manually linking the cells.

Thank you very much for your excellent and detailed response above. I learned from it. :-)

Brian Wilson DC

If there are just two sheets then they will simply need to use the corresponding formulas from the third sheet in the appropriate sheet that they would like them in, if I am thinking about this correctly. So if sheet A wants to pull in data from B, use the Data B formula. Then reverse the formula for Sheet B to pull in Sheet A. The principle should still work.

In my mind I thought they were trying to aggregate everything into another location not just mirror each other.

Tonya Sloan

@Brian Wilson DC Thank you for your quick response. There are only two sheets and not three. Is there still a way to make what I need work?

I can confirm that they are “Date” type columns and not a Text type with dates typed in.

The unique identifiers for each sheet are in different column locations within the overall sheets. Meaning these unique identifiers are not the first column within the sheet, much like you would need for an Excel VLOOKUP formula.

Thank you for the not restricted to “dates only” within the “Date Type” setting. One of the sheets had the "restrict to dates" only setting enabled.

I tried the =INDEX({Data Sheet A - Start Date}, MATCH([Unique Identifier]@row, {Unique Identifier Row - Sheet A})) formula you gave within my Sheet A cell to pull from Sheet B the date that I want to display (based on the date I placed in Sheet B in the identified column) and it didn't return a result. Based on my naming, this is the formula I entered into Sheet A cell where I want the data to display: =INDEX({CAR/CORPORATE TRACKING date}, MATCH([CAR code]@row, {CAR code}))

I'm hoping there is a solution. Thank you for your assistance.

Tonya Sloan

@Brian Wilson DC and @Galen T Garrison

I finally got it to work with this code: =IFERROR(INDEX({CAR/CORPORATE TRACKING date}, MATCH([CAR code]@row, {CAR code}, 0)), "")

I had to add an additional extender to get it to work, but Eureka!!!

Now I need Sheet B to be able to check against two different Sheets (Sheet A and Sheet C) and return a value/date, IF the unique ID in either Sheet A or Sheet C matches the unique ID in Sheet B. I believe it is a twofer formula so that it checks each sheet and returns the one single value that matches the unique ID.