alternating 2 vlookups for 1 cell

[wizbasky]

I have a spreadsheet I am working on, and I have limited excel formula experience, i can do a Vlookup formula but thats about it.

what i was to do is this.

Imagine a 2 page spreasheet,

Page 1 / Cell (A1) = abc Cell (A2) = the vlookup foruma i guess


Page 2 = the list of codes & prices

normaly like this

Col 1 Col 2 Col 3
abc $100 $3.66
def $150 $5.88
ghi $125 $4.50
jkl $110 $3.99


Column 1 = codes
Column 2 = Each price
Column 3 = Lineal Meter price


I want to figure out a way to have a button on page 1, that you can click, that swaps between EA & LM prices.

Yes I could just put 2 columns next to each other and have both prices sitting next to each other, but it's messy, and makes the spreadsheet failry wide, I want to clean it up and make it neat looking.

Is it possible to make a button that swaps out the vloopup or some other forumula so it swaps between prices ( EA & LM)

 

[rs2k]

Wizbasky, welcome to the board'

You could use 2 buttons and add code to each button as below;

Sub EAprice()
    Range("A2").Select
    ActiveCell.Formula = "=VLOOKUP(A1,Sheet2!A1:C4,2,0)"
End Sub
 
Sub LMprice()
    Range("A2").Select
    ActiveCell.Formula = "=VLOOKUP(A1,Sheet2!A1:C4,3,0)"
End Sub

Or if you use a reference cell (I used G1 in this example) you can use a select case statement;

Sub Bothprices()
Select Case Range("G1").Value
Case Is = "EA"
    Range("A2").Select
    ActiveCell.Formula = "=VLOOKUP(A1,Sheet2!A1:C4,2,0)"
 
Case Is = "LM"
    Range("A2").Select
    ActiveCell.Formula = "=VLOOKUP(A1,Sheet2!A1:C4,3,0)"
 
    End Select
End Sub

HTH
Colin

 

[wizbasky]

Hello RS2K,

This...
Sub EAprice()
Range("A2").Select
ActiveCell.Formula = "=VLOOKUP(A1,Sheet2!A1:C4,2,0)"
End Sub


Plus the other one, looks liek it will do something, but how do i assign this code to a button? I have really basic skills.

 

[rs2k]

If you use a command button from the controls toolbar, right click>view code and paste it in there. If it's a command button from the forms toolbar, right click>assign macro and choose a amcro to attach to the button.

If you PM (private message) me your email, I will send you a sample workbook showing this, so you can disect were things go.

HTH
Colin