Forum Discussion

radwansam_'s avatar
radwansam_
Copper Contributor
Feb 18, 2023
Solved

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: ...
excel
  • OliverScheurich's avatar
    OliverScheurich
    Feb 18, 2023

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

     

     

radwansam_'s avatar
radwansam_
Copper Contributor
Feb 18, 2023
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's avatar
    OliverScheurich
    Gold Contributor
    Feb 18, 2023

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

     

     

Resources