Worksheet Change - Help!

[worzell]

Hello there,

I am working on a spreadsheet and need some help with code. The idea is
this:

CellA1 CellB1 CellC1
CellA2 CellB2 CellC2

Cells A1 & A2 have a list using data validation. The other cells have
an IF & Vlookup formulas.

The problem I have that one of the options in A1 & A2 is to manually
enter data and once this is done obviously the formulas get lost.
Basically I need the other cells to reset with the formulas if Cells A1
& A2 don't equal 'manually enter data'.

I am pretty sure I need to use VBA worksheet event code but I'm not
really sure where to start.

Any help would be greatly appreciated.

Many thanks,

Worzell

 

[Bob Phillips]

Sorry, I am not clear.

Why '... obviously the formulas get lost ...'? How can you manually enter
data in A1, A2, it must conform to the DV rules must it not?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[worzell]

Apologies, I didn't make it clear. There is a list of options for in
A1, one of which is manually enter data. If this is selected cells B1 &
C1 show enter data and are to be overtyped.

Once this is done however I then lose my formulas in B1 & C1 (which I
expected). What I need is some code so that if I then go back to cell
A1 and change it from the DV list the formulas reinstate themselves in
cells B1 & C1.

Does this make sense?!

Many Thanks,

Ant

 

[Bob Phillips]

Can you show the DV rule, and what are the formulae in B1 and C1.

You could use worksheet change code on A1 such that if it gets set to a non
manually entered value, you re-instate the formulae, but for that we need
the DV rule and values, and the formulae.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[worzell]

Hi Bob,

Worksheet change code on A1 is what I was hoping for!

Data Validation for A1 is a list from source: =$K$7:$K$17

The list is:
bespoke
pattern 1
pattern 2
pattern 3
pattern 4
pattern 5
pattern 6
pattern 7
pattern 8
pattern 9
pattern 10

When any of the patterns are selected the below formulas gather the
required information. When bespoke is selected cells B1 & C1 show input
data. As discussed when I then input data I lose the formulas. Ideally
I would like some worksheet change code to reinstate the formulas if
another option is selected in Cell A1.

Formula for B1 is: =IF($B$7="<Input Data>","<Input
Data>",IF($B$7="bespoke","<User
Input>",(VLOOKUP($B$7,$K$7:$M$17,2,FALSE))))

Formula for C1 is: =IF($B$7="<Input Data>","<Input
Data>",IF($B$7="bespoke","<User
Input>",(VLOOKUP($B$7,$K$7:$M$17,3,FALSE))))

Many Thanks,

Ant

 

[Bob Phillips]

Hang on, what is <Input Data> and <User Input>? You can't have a formula
and a user entering in a cell, one or the other.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)