If in excel you have an example like



How do I make it so that when the mazda is typed in A2, in B2 the price is automatically looked up? I've tried so many different ways and can't get it. I can't find my text book and google is not helping.

that works only with programming vba.
you cant do that in excel alone.
because that would mean you could pick the right of a cell - which you cant as far as i know
you could write it for every cell singlehanded
=if($A$2=a4;b4;if($A$2=a5;b5;if($A$2=a6;b6;if($A$2=a7;b7;if($A$2=a4;b4;if($A$2=a8;b8;if($A$2=a9;b49;if($A$2=a10;b10;"")))))
(dont know if theres enough brackets at the end)


you would need to have an on change script and then go like


dim i as integer

if Range="A2" then '<-- you need the target range of the change - im not sure what it is called exactly
for i=4 to 99 do
if cells.value(1,i)="" then
cells.value(2,2)=""
exit for
end if

if cells.value(1,i)=cells.value(1,2) then '<--- i always get those mixed up , maybe its i,1 and 2,1
cells.value(2,2)=cells.value(2,i)
end if

end for '<-- not sure if that is the correct syntax
end if

end sub



you should be able to go from here.
if not hit me up
if yes
still hit me up with some fgs :-)

A user helped me, I was doing it correctly just messed up column number in the formula 'cos I didn't even know it was column number lol. http://forums.d2jsp.org/topic.php?t=73106783&f=27&p=493478599

As far as VBa programming goes, it's a lot simpler to use R1C1 referencing from my experience.

r1c1?
please elaborate

wow i didnt even know excel had this functionality

this is awesome!!

File -> Options -> Formulas -> R1C1 reference style

Instead of sheet1.cells(A1) you get sheet1.cells(1,1) where (1,1) = (row, column)
So (5,1) would be A5 but when you're doing loops you can just use for loops to advance in cells for rows and columns easily

As for the original question:

Place this in the WORKSHEET that you are using, not in modules.

It targets 3 specific cells with the if elseifs and if any changes are made to those cells it will step in and in this case run the subprogram I have listed there.

Simply write a sub program to call with a bunch of if elseifs to write in the cells you want (in relative location, i.e. 1 cell to the right of the cell that was changed) and it will work as soon as you write in the cell in question and exit the cell.

Just another way to do it seeing as you already found a way.

This post was edited by Dontrunaway on Aug 26 2015 12:05am

dude, you don't need a macro

Just use either a vlookup or index/match.

Type in the following formula into the cell (might have to modify slightly based on the number of rows)
=VLOOKUP(a2,$A$5:$B$7,2)