I currently have an Excel spreadsheet, with macros, that works "as designed". Users are happily using it, but only one person can update it at a time.
I need to move it to multi-user 'concurrent' updating. I have one specific question though .... there are some sheets which the user can input values to achieve output they want, and we don't want those values to get auto-updated and everyone else get them. That would confuse them (& me!).
What I mean is -
1. The data they enter is on one worksheet (which is held in a defined Excel table structure). That's fine, as we need to "share" the updates between people as they happen. Key fields are a unique ID for each [row], and a location where that item can be found.
2. There are however two other sheets which allow the user to print labels or details for the selected row of data they have been updating.
3. There are also other sheets which give them a "pictoral" view of the data - where the item on that line is located. They can double-click on these locations and very easily update the location information back on the main sheet where that item is stored.
BUT ....
4. Currently the primary data worksheet automatically updates the key selection field in the other two sheets so that when the user toggles to them they see the "labels" or "details" laid out for printing. That "key selection field" value in turn will cause lookups/references to pull the data about it from the primary data table into fields in that worksheet into the appropriate layout, including for one field showing the value as a barcode.
....
Problem when multiple users update the spreadsheet will be - how can I ensure they see their "key selection field value" & the consequential values from the lookups/references and not the values that someone-else causes to get displayed (in the next second or so)?
e.g. I want to print labels for the item I just updated. But when I go to the "label" sheet I could end up being shown the label belonging to another user who updated a different item line a second or so after I did <sad face>
I thought about using global variables to hold the values, but that seems an extreme way to have to handle it & probably just as fraught with the same issues anyway! That ignores how messy the coding could get too.
Does someone know a nice clean way to allow multiple users to update the data in one worksheet of the workbook, but not affect the values held in any other worksheet? In simplistic terms the ability to tell Excel to save only the main data worksheet & to never reflect the "changes" that will happen in any other sheet & thus not mess other users up presenting someone-else's information?
Hope my explanation of what I want to achieve is understandable!!
I need to move it to multi-user 'concurrent' updating. I have one specific question though .... there are some sheets which the user can input values to achieve output they want, and we don't want those values to get auto-updated and everyone else get them. That would confuse them (& me!).
What I mean is -
1. The data they enter is on one worksheet (which is held in a defined Excel table structure). That's fine, as we need to "share" the updates between people as they happen. Key fields are a unique ID for each [row], and a location where that item can be found.
2. There are however two other sheets which allow the user to print labels or details for the selected row of data they have been updating.
3. There are also other sheets which give them a "pictoral" view of the data - where the item on that line is located. They can double-click on these locations and very easily update the location information back on the main sheet where that item is stored.
BUT ....
4. Currently the primary data worksheet automatically updates the key selection field in the other two sheets so that when the user toggles to them they see the "labels" or "details" laid out for printing. That "key selection field" value in turn will cause lookups/references to pull the data about it from the primary data table into fields in that worksheet into the appropriate layout, including for one field showing the value as a barcode.
....
Problem when multiple users update the spreadsheet will be - how can I ensure they see their "key selection field value" & the consequential values from the lookups/references and not the values that someone-else causes to get displayed (in the next second or so)?
e.g. I want to print labels for the item I just updated. But when I go to the "label" sheet I could end up being shown the label belonging to another user who updated a different item line a second or so after I did <sad face>
I thought about using global variables to hold the values, but that seems an extreme way to have to handle it & probably just as fraught with the same issues anyway! That ignores how messy the coding could get too.
Does someone know a nice clean way to allow multiple users to update the data in one worksheet of the workbook, but not affect the values held in any other worksheet? In simplistic terms the ability to tell Excel to save only the main data worksheet & to never reflect the "changes" that will happen in any other sheet & thus not mess other users up presenting someone-else's information?
Hope my explanation of what I want to achieve is understandable!!
