Writing updated info across several worksheets

Copper Contributor

Excel 2010:

Am creating a worksheet for a coworker who enters equipment calibration dates and other details for all the calibrated equipment in our labs. He currently has one workbook with multiple worksheets. Some equipment is duplicated among the worksheets, so he has to go through every one of the worksheets manually to search for and make any changes as equipment comes due for calibration.

My goal is to make the process more streamlined, and have had success so far but have hit a brick wall.

Using VLOOKUP, I was able to find and display in a separate table every instance of an equipment tag # (our internal serial #) that he enters, that exist in any of the worksheets, along with all of the associated descriptors (last calibr date, next calibr due date, manufacturer, model #, etc.).  Hooray for VLOOKUP !

The brick wall is this:

After he enters any proposed changes/updates in the cells provided him in the displayed table, I can't figure out how to write those changes/updates to all the appropriate cells across all the worksheets. I know which worksheets actually have the asset # (determined with Vlookup), but I don't know which cell locations within each worksheet to insert the updated info. Seems like a simple task, but I just don't see it yet.

Some things explored or attempted: Replace, Find, Search, Substitute, Index, Match. None work without specifying the destination cell. 

Any help would be greatly appreciated.

 

6 Replies

Hello,

 

I'm not sure I fully understand the data architecture, but to me it seems to be backwards, or the wrong way around.

 

A logical architecture would be to have a list of equipment in one sheet, with equipment ID, calibration date, and all the details pertaining to that equipment. Any changes to any piece of equipment will only ever be made in that one table.

 

Then on another sheets, if you need to refer to equipment, enter the equipment ID and use VLookup to pull the desire equipment data from the equipment table. 

 

With this approach, any change made in the equipment table will immediately be reflected in the other sheets that reference the table data.

 

Does that make sense?

I think I understand what you're saying. Ingeborg is right though, structured this way is backwards.

 

If the data you're looking up is in a Table, then you can reference it and never worry about it. My assumption is the data being entered on these other sheets isn't in a Table, and keeps growing, thus you don't always have the most up-to-date range to reference. The easy fix is to make them into Tables. Can you do that? If not I would use a named range. Of course I recommend a good data structure, like Ingeborg suggested.

That makes three... like Zack mentioned if you can't use a table then go for a named range.  See the attached file for how to get a dynamic named range as a list or as a table.

 

You can use OFFSET() and COUNTA() to accomplish this in most cases:

 

OFFSET : https://support.office.com/en-us/article/OFFSET-function-c8de19ae-dd79-4b9b-a14e-b4d906d11b66

 

COUNTA: https://support.office.com/en-us/article/COUNTA-function-7dc98875-d5c1-46f1-9a82-53f3219e2509

I will post a scrubbed version of the workbook and my worksheet as it now stands shortly, along with a brief narrative. I don't think I explained it adequately in the original post. 

Thanks,

-Ron A.

Hello All,

The data structure is less than ideal and does need an overhaul, this is a known issue. My coworker inherited the workbook and the responsibility a few years ago, but is not comfortable using Excel. He has plodded along doing things the same way his predecessor did, watching the workbook expand along the way.

On my end, I am a little more comfortable using Excel but can only donate time as it becomes available, which isn't often enough. 

I will send a scrubbed and reduced version of the workbook to each of you via e-mail. Please let me know if you don't receive it.

Thank you all again,

-Ron A.

Ron,

This is a very typical situation. Can't count the number of times I've helped with inherited workbooks. Also, I didn't see a file.