Overwritting Values

tedc

Board Regular
Joined
Oct 31, 2003
Messages
194
I have a cell that relates to a lookup table C32 that looks up A62
The lookup table values that appear in cell C32 from time to time need to be overwritten with user-inputted values.
i.e. 5 (from lookup table) to be replaced with say 'Open Class' or whatever, yet still be able to reference the lookup table with future inputs.
Could a Command button function be written to do this in VB and what would that be?

Thanks in advance

Ted
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi Ted:

Please describe clearly ...

1. what is the value being LookedUp, and what cell is it in
2. what is the range of the LookUp Table -- I think you mentioned it starts iwith cell C32 -- please specify the cell at the other end of the range that constitutes the LookUp Table
3. You mentioned something about 5 values to be Looked up -- what is this 5 in reference to?
4. what particular problem are you having now
5. what have you tried
6. what result did you get
7. and what do you think is the right result -- with an explanation as to why you think that is the right result
 

tedc

Board Regular
Joined
Oct 31, 2003
Messages
194
Hi Yogi
1) the value being looked up is a dog class relating to its age
this is in cell A62

2) the range of the lookup table is A65:B98 (C32 is where the value appears)

3) '5' is a class number of a dog relative to it's age. Nothing more

4)Sometimes the user may need to overwrite the walue that the lookup table produces.
For example. A dog who is 18 months old would be entered in class 5. Also the same dog could be entered in 'Open Class'. The user would need to be able to type 'Open Class' in C32 thus over riding the number '5'.
Having done that- the lookup reference, in this case =A62 would be lost.
5) Nothing. I am a bit of a newbie
6) Nothing tried
7) To be able to overwrite a value in cell C32 with any text or number without deleting the reference to the lookup table when another dog is entered

I hope this makes sense :)

Ted
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi Ted:

It begins to make sense now. You still need to clarify -- if the LookUp value is in cell A62, how does cell C32 come into the picture. Please show the LookUp formula you are using, showing ...

what value are you using for LookUp in cell A62
Show a few of the rows in the LookUp Table A65:B98
and show how cell C32 gets fed or how it receives a value

Using VBA, I don't see a problem -- overwriting the value produced by a VBA procedure. However do you have to keep track of whether the value produced by VBA procedure has been written over?

In any event, if you were simply looking for some feed back that whether it is feasible using VBA -- yes it is.

In a formula based approach, you can use formulas in one column (which can optionally be hidden) -- and then in another column, you choose whether to use the value that has been computed, or something else.

If you do need to discuss this further, please post back -- and let us take it from there.
 

tedc

Board Regular
Joined
Oct 31, 2003
Messages
194

ADVERTISEMENT

Hi Yogi

C32 is where the results of the lookup table appear
A63 is the dogs age (in months) this relates to the first colum in the lookup table
The second column in the lookup table is the dogs class

ie A65 shows a 3 : B65 shows a 1 (A65 age in months: B65 Class 1)

Column A B
3 1
4 1
5 1
6 2
etc

this formula for the lookup tables
A62=VLOOKUP(Main!A63,A65:B98,2)

I am looking for the code that would accomplish this on activating a command. In this perhaps a command button

Ted
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi Ted:

The result of your VLOOKUP is in cell A62. Your final output is in cell C32. Then you do not need VBA or a hidden column or any thing like that.

If I have undestood you correctly -- then in cell c32, if you want to use the result of the formula, use

=A62

and if you want something else, instead of writing =A62, write over it what ever you want to write instead.

Next time, in cell C32, again if you want to make use of the formula, write

=A62

or provide your own writeup.

Did I get it right? Would this approach work for you. I would rather key-in =A62 instead of having to click on a CommandButton.

Please post back if this can work for you -- or you would still like a CommandButton -- that will write =A62 in cell C32.
 

tedc

Board Regular
Joined
Oct 31, 2003
Messages
194

ADVERTISEMENT

Thanks for you prompt reply Yogi
If this program was for my use only I would have no problems typing in that reference
But as this will be a give-away to virtually conputer illitertate dog showers and breeders I need to make it a simple an fool proof as possibe
So that's the reason for the command button code

Ted
 

tedc

Board Regular
Joined
Oct 31, 2003
Messages
194
Thanks for your input Yogi
I simply wrote a macro to that effect and added it a command button.
I would't have thought of how to do that without your input

Ted
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,560
Messages
5,765,091
Members
425,258
Latest member
brentmitchell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top