Two columns & cross fill input

adambjorken

New Member
Joined
Nov 17, 2016
Messages
12
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
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

bellman101

Board Regular
Joined
Aug 20, 2012
Messages
245
Hi Adam,

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

You could use a 'WorksheetChange_Event in VBA though:

Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,102,666
Messages
5,488,182
Members
407,631
Latest member
Mehezabin

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top