Overwritting Values

[tedc]

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

 

[Yogi Anand]

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]

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]

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]

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]

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]

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]

Yes
a command button code would be great

Ted

 

[tedc]

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

 

[Yogi Anand]

Hi Ted:

Great -- now let us keep EXCELing!