Cross Sheet Data entry

RAC

When entering data on one sheet I want to have data auto populate into another sheet without having to have a vlookup formula in each cell of the sheet.

Jeff Reisman

https://community.smartsheet.com/discussion/88438/cross-sheet-data-entry

A few ways to do this:

Cell-linking functionality - You can select a range of cell from your source sheet to populate the destination sheet. However, this is limited in how many you can link this way, and can get slow and messy, and doesn't always work great when dealing with data-entry.

INDEX/MATCH - If you have an auto-number system column in your source sheet, you can pre-populate a column in the destination sheet with the same numbers, and use INDEX/MATCH column formulas to lookup the data from the source. This is based on matching the values between the auto-number column in the source sheet and your pre-populated numbers in the destination. Changes to existing rows on the source sheet would update the matching row on the destination sheet too.

Smartsheet Automation to Copy Rows - Create a new Automation workflow in the source sheet to copy new rows over to the destination sheet. This would be a static copy, so changes to existing rows on the source sheet would not reflect in the destination sheet.

RAC

Thanks, this was helpful, working through to see what will work for my team

Jeff Reisman

https://community.smartsheet.com/discussion/88438/cross-sheet-data-entry

A few ways to do this:

Cell-linking functionality - You can select a range of cell from your source sheet to populate the destination sheet. However, this is limited in how many you can link this way, and can get slow and messy, and doesn't always work great when dealing with data-entry.

INDEX/MATCH - If you have an auto-number system column in your source sheet, you can pre-populate a column in the destination sheet with the same numbers, and use INDEX/MATCH column formulas to lookup the data from the source. This is based on matching the values between the auto-number column in the source sheet and your pre-populated numbers in the destination. Changes to existing rows on the source sheet would update the matching row on the destination sheet too.

Smartsheet Automation to Copy Rows - Create a new Automation workflow in the source sheet to copy new rows over to the destination sheet. This would be a static copy, so changes to existing rows on the source sheet would not reflect in the destination sheet.

RAC

Thanks, this was helpful, working through to see what will work for my team

RAC

A couple of questions on this:

When we added an auto number row it did not start with the number we wanted which was one, any ideas?

On the destination sheet do we need to populate the numbers manually or add an autonumber column on that sheet as well?

Jeff Reisman

https://community.smartsheet.com/discussion/comment/321033#Comment_321033

I have found, when adding an AutoNumber column to an existing sheet which has had rows added, removed, etc, it may not start at 1. I don't know any way around this other than to create a new sheet with the autonumber column, and copy your existing rows (without the automnumber column) into the new sheet.

On the destination sheet you need to populate the numbers manually or by formula. By formula may be easiest, start with a value in row 1 matching your starting automnumber from the source sheet, then row 2 has a formula to add 1 to the value above it, something like:

=[NumberColumnRow]1 + 1

Then Ctrl-c Ctrl-v the formula into the rows beneath it, and the row reference ([NumberColumnRow]1) should update automatically for each row ([NumberColumnRow]2, [NumberColumnRow]3, etc).