SOLVED

Formula autofilling values present on another worksheet

Copper Contributor

Hello, need your kind help please with a formula that I'm trying to create linking cells in two worksheets and involving typing ticker symbols for stocks

 

Sheet 1:

 

sheet1.png

 

 

 

Sheet 2:


sheet2.png

So, basically what I'm trying to achieve is when I type the ticker symbol in Sheet 1 in the cell opposite to "stock" ( call it K5) the Current price (K11) autofills using the prices in Sheet 2.

 

Thank You!

10 Replies

@radwansam_

In cell K11 of sheet 1 you can try

=VLOOKUP('Sheet 1'!$K$5,'Sheet 2'!$A$400:$B$403,2,FALSE)

or

=INDEX('Sheet 2'!$B$400:$B$403,MATCH('Sheet 1'!$K$5,'Sheet 2'!$A$400:$A$403,0))

 

@OliverScheurich  Thanks for your reply! unfortunately both are not working : both of them are popping up a window thats asking me to upload some file 

 

error1.png

 

@OliverScheurich also for completion i made a slight update to sheet2 where it pulls this data automatically from a google sheet 

 

sheet2.png

 

 

@radwansam_ 

Perhaps the names of your sheets are "Sheet1" and "Sheet2" instead of "Sheet 1" and "Sheet 2". According to your first post i assumed the names are "Sheet 1" and "Sheet 2".

=VLOOKUP(Sheet1!$K$5,Sheet2!$A$400:$B$403,2,FALSE)
=INDEX(Sheet2!$B$400:$B$403,MATCH(Sheet1!$K$5,Sheet2!$A$400:$A$403,0))

These formulas should work. Otherwise you can adjust the sheetnames according to the actual names of your sheets.

My bad! Thanks alot first 2 formulas worked but only with one ticker: ENPH , when i look up any other ticker it doesn't work

@radwansam_ 

Does it work in the attached sample file? It works as intendend on my computer.

interesting... it does work on this file
quick update, when i add a new ticker and price to your sample file say on row 396 on sheet 2, it doesn't reflect on sheet 1 (this value doesn't match data validations restrictions...

@OliverScheurich 

 

Hey i know where the problem is, but cant fix it! so it allows stocks only from rows 400 to 403, stocks like ENPH, ENVX but not other rows. i can even see it in the code however when i change it to 1 - 2000 for example it doesn't work?

best response confirmed by radwansam_ (Copper Contributor)
Solution

@radwansam_ 

You can adjust the source list of the data validation to:

=Sheet2!$A$396:$A$403

 

Row 396 isn't included in the provided formulas. The original example only included rows 400 to 403. However it's easy to adjust the formulas to a range that includes row 396.

 

=VLOOKUP(Sheet1!$K$5,Sheet2!$A$396:$B$403,2,FALSE)

=INDEX(Sheet2!$B$396:$B$403,MATCH(Sheet1!$K$5,Sheet2!$A$396:$A$403,0))

 

 

1 best response

Accepted Solutions
best response confirmed by radwansam_ (Copper Contributor)
Solution

@radwansam_ 

You can adjust the source list of the data validation to:

=Sheet2!$A$396:$A$403

 

Row 396 isn't included in the provided formulas. The original example only included rows 400 to 403. However it's easy to adjust the formulas to a range that includes row 396.

 

=VLOOKUP(Sheet1!$K$5,Sheet2!$A$396:$B$403,2,FALSE)

=INDEX(Sheet2!$B$396:$B$403,MATCH(Sheet1!$K$5,Sheet2!$A$396:$A$403,0))

 

 

View solution in original post