References and Sort

[Mickey]

Hi all,
I have a workbook covering investments. On one sheet I list the investments
that I am tracking, some of these I own. The funds I own are listed again
on a separate worksheet. I update the monthly fund price in sheet 1 and use
a formula to automatically add that new price to any funds listed in my
portfolio.

The problem is, if I add a new fund to the first sheet and then do a 'sort',
the formulas on sheet 2 do not reflect the new cell references for their
respective fund prices. I have tried using 'names' and also relative
referencing.

Is there any way to force Excel to remember where a new cell has moved to
following a sort?

Many thanks for any help,
Mickey

 

[Frank Kabel]

Hi Mickey

you can use VLOOKUP on your second sheet with your own investments).
E.g.
=VLOOKUP(A1,'Sheet1'!$A1:$B99999,2,FALSE)
copy this down for all desired rows

Assumptions:
Column A on sheet 2 stroes your fund identifier (e.g., ISIN, Ticker,
etc.)
Column A on sheet 1 also holds the fund/security ID
Column B on sheet 1 stores your price info

HTH
Frank

 

[Mickey]

Hi Frank,
Thanks for the reply which sounds great. I haven't used the VLOOKUP
function before but this sounds like a good time to try it out!

I am very grateful for your help, thanks,
Mickey

 

[Frank Kabel]

Hi Mickey,

you're welcome. If you encounter further problems just come back to
this NG
Frank

 

[Dave Peterson]

It sounds like you're just using a simple reference to get the value back:

=sheet2!b99

If both worksheets have a common value that you can use as a key indicator (and
if that key indicator column is the leftmost in the 2nd worksheet, you can use a
formula to retrieve the values:

=vlookup($a3,sheet2!$a:$h,3,false)

(say A3 is on sheet1 and contains the name/abbreviation of the fund).

This will look for an exact match for A3 in sheet2's column A. When it finds
that match, it returns the third column to the right (Column C in this case).

If there is no match, you'll get an #n/a error.

You can hide this by looking for the error in your formula:

=if(iserror(vlookup($a3,sheet2!$a:$h,3,false)),"missing",
vlookup($a3,sheet2!$a:$h,3,false))
(all one cell.)

And if you need to retrieve more values, just copy the formula and adjust the 3
to the column to return.

Drag it down the range and then try your sort.

One more thing, if the value in sheet1 column A can be blank, you can hide the
error that would be returned with:

=if($a3="","",if(iserror(vlookup($a3,sheet2!$a:$h,3,false)),"missing",
vlookup($a3,sheet2!$a:$h,3,false)))
(still all one cell.)

 

[Mickey]

Thanks Dave,

Mickey