index & match with links for updating to other workbooks

[wrpalmer]

Goodmorning- in my life as an excelite, my level of expertise is about
25% advanced and there in lies my problem. While understanding index &
match on "a" sheet in workbook #1, i am unable to write the formula
that will allow me to update prices on the one sheet, open separate
workbooks, and import that update to a specific line of cells that
match the updated material
=INDEX($B2:$B20,,MATCH(B23,$A2:$A20,0)) -this works well on a single
sheet but when in the sheets of the workbks to be updated the link
naming convention seems ok but i get hung up because "B23" represents
the cell in the first sheet but not the 2d & 3d work sheets and i do
not understand the formula for renaming or naming the B23 to the proper
cell in the other wkbk sheets and identify that the new "B23" is in a
different wkbk and diff cell & is merely a link

update wksheet diff wkbk
sheet
A B
abt 1 abt glw
gm
glw 2
gm 5 ? ?
?

simply stated " if in 'diff wkbk sheet' "abt" exists, then update with
amt from col B in "update wksheet" to the occurrence of "abt" in 'diff
wkbk sheet' if " abt" exists in 'diff wkbk sheet'
-symbols are dynamic in the sense they change periodically and so it
cannot be a hard link but to be activated only if an occurance of the
ticker symbol exists in 'diff wkbk sheet"
- the 'update wksheet' will the constant
Thanks to whomever has the wisdom to guide me
VTY wrpalmer

 

[RagDyeR]

Let XL do the work and insert the path for you.

Open both WBs.
Start your formula in the appropriate sheet:

=INDEX($B2:$B20,,MATCH(

NOW, navigate to the other WB (or sheet), and click in B23.

You'll see that XL references the formula with the path to the proper cell.

Complete the formula in the formula bar, and hit <Enter>.

If the B23 was in another WB, when you close *that* WB, you'll see the
formula enlarge and change, to display the *full path* to the now closed WB.

You can now use this formula as an example for your future formulations, or
.... you can just continue to let XL do it for you.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

message
Goodmorning- in my life as an excelite, my level of expertise is about
25% advanced and there in lies my problem. While understanding index &
match on "a" sheet in workbook #1, i am unable to write the formula
that will allow me to update prices on the one sheet, open separate
workbooks, and import that update to a specific line of cells that
match the updated material
=INDEX($B2:$B20,,MATCH(B23,$A2:$A20,0)) -this works well on a single
sheet but when in the sheets of the workbks to be updated the link
naming convention seems ok but i get hung up because "B23" represents
the cell in the first sheet but not the 2d & 3d work sheets and i do
not understand the formula for renaming or naming the B23 to the proper
cell in the other wkbk sheets and identify that the new "B23" is in a
different wkbk and diff cell & is merely a link

update wksheet diff wkbk
sheet
A B
abt 1 abt glw
gm
glw 2
gm 5 ? ?
?

simply stated " if in 'diff wkbk sheet' "abt" exists, then update with
amt from col B in "update wksheet" to the occurrence of "abt" in 'diff
wkbk sheet' if " abt" exists in 'diff wkbk sheet'
-symbols are dynamic in the sense they change periodically and so it
cannot be a hard link but to be activated only if an occurance of the
ticker symbol exists in 'diff wkbk sheet"
- the 'update wksheet' will the constant
Thanks to whomever has the wisdom to guide me
VTY wrpalmer

 

[wrpalmer]

RagDyeR- Thank you for helping solve my problem of linkin
worksheets/book- wrpalme

 

[RagDyeR]

Thanks for the feed-back.