Parsing cell formula to return associated value

[J Williams]

I have 2 sheets in my workbook. Sheet1 is a stock list (item and price in
adjacent column):

A B
1 Screen 200
2 Speakers 50
3 Keyboard 20
etc.

Sheet2 contains items manually selected from Sheet1 in column A:

A B
1 =Sheet1!A1 ??
2 =Sheet1!A99 ??

I want B1 on Sheet2 to contain the price associated with the formula in A1.
I can't do a straightforward drag cell formula from A1 to B1 (to get
=Sheet1!B1) because the user can change the cell reference in A1 and I want
B1 to automatically update with the correct price. Similarly B2 should
contain the price associated with the formula in A2.

I think this requires a user-defined function, e.g. B1 would contain
=getPrice(A1).

Function getPrice(itemCell As range) As Integer
item = itemCell.Formula
End Function

and parse the formula to get the price from Sheet1, same row, column+1, but
don't know how to proceed. Any help much appreciated. Cheers.

 

[JulieD]

Hi

you can use the VLOOKUP function to do this ...
here's a copy & paste of an answer i wrote to a similar question awhile
back -
---
se a combination of data / validation to create the drop down list and
VLOOKUP function to populate the related information

assuming that in Sheet2 you have the following

.......A..... B

1..Item Value

2..Item1 10.00

3..Item2 15.00

4...Item3 20.00

now select from A1 to the end of your list (A4, in the above example) and
choose from the menu,

Insert / Name / Create - ensure Top Row is checked and click okay - you've
created a named range called "Item" (or whatever the title in A1 is)

now select from A1 to the end of the list for all columns (B4 in the above
example) and click inside the name box (left of formula bar) and type
MyTable and press enter - we've created a second named range.

Now click in the cell in Sheet1 where you want your drop down list to appear
and choose Data / Validation from the menu - in the settings tab, choose
List from the Allow drop down box and then click inside the white box under
this and press the F3 key - this will bring up a list of your range names,
choose "Item" (or whatever your first range was called) and click Ok. You
will now have a drop down list in this cell.

Now click in the cell where you want the related information to appear and
type

=VLOOKUP(A1,MyTable,2,false)

where A1 is the cell reference with your drop down list in it - you can

use

the F3 key for the MyTable bit too.

now choose an item & see the related information appear ... delete the item
and you'll get

a #NA error - this can be supressed by embedding your VLOOKUP in an IF
statement e.g.

=IF(A1="","",VLOOKUP(A1,MyTable,2,false))

where A1 is the cell reference with your drop down list in it

 

[J Williams]

Thanks Julie. A simple VLOOKUP did the job.

Hi

you can use the VLOOKUP function to do this ...

 

[JulieD]

you're welcome - thanks for the feedback

Thanks Julie. A simple VLOOKUP did the job.