In Excel, how do I get a cell to contain the results of a formula, but not the formula itself?

General Questions
#1

I’m using vlookup to autofill columns in a spreadsheet. Boss is using Excel to store records of data entry errors. Each operator has an op#, opid, manager, and name. I just enter the op# and have all other 3 columns autofill based on vlookup formulas.

Only problem is that sometimes an operator leaves, and the op# is recycled a few months later. When I update the vlookup table, the spreadsheet will retroactively assign all old errors under that op# to the new employee. The problem would be solved by just autofilling with the results of the formula, but not the formula itself. That way I can change the info in the vlookup table without the database part the spreadsheet changing.

Surely there is some simple way to do this I’m just not able to find online.

#2

Use a cell reference? That is, if the formula is in E4, make F4 (=E4)?

#3

I don’'t know how to do it automatically, but you can manually copy the cell (or cells), and then do a “Paste Values”.

Try it a few times. Maybe you can do it for the entire worksheet, and then write a macro to automate it.

#4

Nope. If the data that E4 is using changes, then F4 will change too.

#5

The simplest is to not recycle the id numbers. That’s database 101 - have a unique key for each record, even if it’s a key that the end user never sees. If the op# is a real-life number that must be recycled, then just create a new number to uniquely and permanently identify each operator

Other than that, my only recommendation is that you can copy data and then do a Paste Special to copy only values and not formulas. That keeps the numbers and strips out the calculations that lead to it so that it won’t update any more.

#6

Could you include a “date of error” field, a “date of hire” field, and just return the errors that occurred for that ID’s term of employment?

But yeah - stop recycling ID numbers.

#7

you’re going to need to dive into VBA

#8

This is the correct answer.
Proper data management is covered under the rules of normalization.
The key should be unique and it should not represent any real world data.

#9

Thirded.

[ol]
[li]Select the data range you don’t want to change when you put in the new op# [/li][li]Right click > copy[/li][li]With the same range still selected (i.e. don’t do anything)[/li][li]Right click > select ‘Paste Special’[/li][li]Click in the third option down (Values) > click Okay[/li][/ol]

#10

Well, looks like they’ll just have to enter it all in the long way. But just out of curiosity…

Say we didn’t recycle op#'s, and Superman’s manager changes from Jane to Bob. I update the vlookup table to reflect this change. I would run into the same problem; all of Superman’s previous errors would change from Jane to Bob, which we wouldn’t want.

So is this type of autofilling something that people just don’t do with databases? Seems like it would be useful

#11

In excel, Autofil at your peril

One way to do it is to put the contemporary (of that week) list of managers in a position down in the bottom or off to the side. Change the list there. In the formula refer to the list of managers in the current worksheet.

To start a new week, copy the last week to the new… so that the managers list (etc) is copied too, and the person entering data only has to consider the changes week to week (not create a whole new list.)

There might be lookups done to verify these lists, eg to highlight the change from week to week, so as to help verify there are no accidental changes in this reference data.

The data may still be able to be cross corelated across the week by week worksheets to form monthly, yearly (etc) yearly summaries … more formula and so on. You can keep the data in the format ready to push into a proper database for proper data analysis…