How do I keep original lookup value?

[CVAR83]

I use a few different workbooks to allow me to perform various inventory management tasks. One of the things that I do all the time is send items out for repair. The issue that I have is that the contract status for each individual item is constantly in flux (no contract, full service, value savings, risk share, bridge, loaner guarantee, etc).

I've got two main workbooks that I deal with, one for the actual inventory that displays all the values pertinent to each individual item in real time, another for a service order log, and I'm building a third for a contract status/change management tool. What I'm trying to do is set it up so that when I enter a service order for an item, it pulls its current contract status, but then does not go on to change it when the contract status inevitably changes (eg, no contract to full service), as no matter what the contract status is in the future, for now it is what it is and the log needs to reflect this.

I also cannot disable auto-update/refresh for formulas, as they have to constantly call data for other items all the time.

I know this overall task is better done in access or another database tool, but I have to work within the confines of the tools that the companies involved are willing to use.

Thanks in advance!

[Glenn Kennedy]

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please DO NOT attach a picture of an Excel sheet (I do not have the patience to re-type any/all your stuff before starting).

1. It does NOT have to be your real sheet - mock up a SAMPLE if you need to. But not 1000's of rows!!! It makes manual checking so tedious. Whatever you do... make sure that all confidential information is removed first!!

2. Make sure that your sample data are truly REPRESENTATIVE of your real data. For example, don't show text in a column if it's really a number. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

3. Make sure that your desired solution is also shown (mock up the results manually). To be honest, I am not interested in seeing a non-working formula... or a pile of blank cells. However, I am very interested in seeing your EXPECTED results in their EXPECTED location.

4. Try not to use merged cells. They cause lots of problems and are DEFINITELY best avoided!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[CVAR83]

Thanks for the quick response! I'll get that set up for you in the morning - it's going to take me a while to go through and strip out everything to create a template and still keep the workbooks intact/functional.

I'll preview it with a simple(ish) explanation now, though:

In my RMA workbook, I'm setting up a lookup in a single column that will reference another column in the same row ("O" for contract status, "C" for serial number) and looking into the inventory management workbook which will have the current contract status in the same sort of setup (column "G" for contract status, column "B" for serial number). I just want the RMA workbook to pull the contract status when I punch in the serial number the first time, and not to refresh that lookup after the initial entry.

Again, I'll post something in the next 12-16hrs, but I'm going to have to go through a few things to develop a genuine template with nothing customer/company specific in it.

[CVAR83]

Here it is. This is pretty simplified and condensed - and also completely fictional. All the formatting is the same, and I think I've shown where items need to be different.

[CVAR83]

I'm back... wondering if anyone has had any luck with this?

[Glenn Kennedy]

I missed your reply. Going out now,but will look back later.

[CVAR83]

Just checking in, don't want to be a pain - I've been working on this too. Again, thanks for even taking a look at this!

[Glenn Kennedy]

Apols. I forgot about you!!

So, for 3127988 (RMA_1, C2), you want the current value in Inventory_1, H4 to be returned in O2, but not to change if/when it is altered on Inventory_1?? if so, you will require VBA, as that is the only way that Excel can "remember" a previous value. There may be awkward work arounds... turing column O into a data tabel and setting calculation options to Automatic except for data tables... but that can get messy.

[CVAR83]

It can get messy fast that way. I thought I might have to do it in VBA and set it up as having a triggering update based upon change in a specific cell, but I wasn't 100% sure if that was going to be the case... Also wanted to avoid VBA because I suck at it.

Thanks for your help!