Update data in one sheet from another sheet that updates daily?

jmhoward

Hi there,

I have separate product sheets (70 of them!) for each product a division creates. Each row in the sheets has a unique line item number. The first 23 columns contain data pulled from one of our systems, including the line item number. The remaining 26-40 columns contain data that's managed manually.

I have another single sheet that contains the same data points as in those first 23 columns in the product sheets. This data is ripped and replaced daily. I'd like to use a formula to update the 23 columns in all 70 product sheets with the latest data, based on using the line item number as the lookup. This way I avoid altering all the data that's updated manually in the other columns. But I am stumped on how to do this.

Any ideas? Many thanks!

Paul Newcome

Try an INDEX/MATCH.

=INDEX({Master Sheet Column To Pull Over}, MATCH([Parent Company]@row, {Master Sheet Parent Company}, 0))

Just change that first cross sheet reference for each of the different columns you want to pull over.

Paul Newcome

Are you wanting to update the single "Master" sheet based on the manual updates made in the individual sheets, or are you wanting to update the individual sheets based on the manual updates in the Master sheet?

jmhoward

Hi @Paul Newcome,

Thanks for responding! It's a little weird.

In the 70 product sheets, columns 1-23 should update daily from the single master sheet. Columns 24 and after should remain in place in the product sheets unchanged, unless someone manually adjusts a value. These manual columns are for task assignments and task statuses for each of the product milestones. The manual columns are not in the master sheet that's updated daily.

A line number can only appear on one product sheet.

The master sheet, containing only columns 1-23, contains all the line item numbers. It's updated automatically each morning. In a perfect world, each morning a formula would push any updates to columns 1-23 on the master sheet to all the product sheets, using the line item number as the lookup.

For example, line item number 12345 is on the Product A sheet and has 40 columns. Each morning, the formula checks columns 1-23 and updates any data that's changed.

Does that make more sense? It's confusing, for sure.

Paul Newcome

Are you able to provide screenshots for context. Maybe some sample data with not quite as many columns so it can all be shown from left to right?

jmhoward

Hi @Paul Newcome,

Sure! Below is an example product sheet:

Everything up until the "F USD" column would connect to the daily update sheet. The columns after are either manually updated or a column formula. Some of the columns with formulas have data that refers to columns that should be updated from the daily report (those first columns through F USD).

This is the example daily update report, which receives data from a source outside of Smartsheet:

The the data in the daily update report is replaced each morning with any updates that occurred in the external system. For example, the F USD amount or the LI State could change in the external system and we want that reflected in the product sheets.

Ideally I'd like the product sheets to look up the data from the daily update so that the product sheets always have the most updated data, without affecting those manual columns.

Does that help?

Paul Newcome

Try an INDEX/MATCH.

=INDEX({Master Sheet Column To Pull Over}, MATCH([Parent Company]@row, {Master Sheet Parent Company}, 0))

Just change that first cross sheet reference for each of the different columns you want to pull over.

jmhoward

Thank you so much @Paul Newcome! We ended up reworking the process a bit, but we still ended up needing to update data based on the line item number. I used this formula and it worked like a dream!

=INDEX({Product Family}, MATCH(LI@row, {ESGBTXT LI}, 0))

Jackie

Paul Newcome

Happy to help. 👍️