Two columns & cross fill input

[adambjorken]

Hi,

I have a spreadsheet where I have column A and column B.

In column A I want a price with V.A.T (=B1*1.25) and in column B i want a price without V.A.T (=A1*0.8).

What I mean with that is that if I enter a value in column B I want Excel to automatically fill in the value in column A.
And if I enter a value in column A I want Excel to automatically fill in the value in column B.

Can you get this type of cross referencing to work in any way?

Probably a noob question but couldn't find anything on Google or at least I'm not searching for the right terms...

Appreciate your help!

/Adam, Sweden

 

[bellman101]

Hi Adam,

With formulae you're always going to run into issues with circular references.

You could use a 'WorksheetChange_Event in VBA though:

Private Sub Worksheet_Change(ByVal Target As Range)
    If IsNumeric(Target.Value) And Target.column = 1 then Target.offset(0,1) = Target.Value *1.25

    If IsNumeric(Target.Value) And Target.column = 2 then Target.offset(0,-1) = Target.Value *0.8

End Sub

Just paste that into the code window of the relevant worksheet​ (i.e. NOT in a module).

EDIT: I actually got the 1.25 and the 0.8 the wrong way round, but you get the idea.

Cheers
JB