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

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)

wrote in message
ups.com...
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

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 wrote:
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

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)

wrote in message
oups.com...
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 wrote:
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

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

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)

wrote in message
oups.com...
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