SOLVED

# Formula autofilling values present on another worksheet

Occasional Contributor

# Formula autofilling values present on another worksheet

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:

Sheet 2:

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

# Re: Formula autofilling values present on another worksheet

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))``

# Re: Formula autofilling values present on another worksheet

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

# Re: Formula autofilling values present on another worksheet

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.

# Re: Formula autofilling values present on another worksheet

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

# Re: Formula autofilling values present on another worksheet

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

# Re: Formula autofilling values present on another worksheet

interesting... it does work on this file

# Re: Formula autofilling values present on another worksheet

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...

# Re: Formula autofilling values present on another worksheet

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_ (Occasional Contributor)
Solution

# Re: Formula autofilling values present on another worksheet

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))