Excel 2003 - Referencing data in a table to use in formulas

[Rocketman]

How do you reference data in a table for use in formulas where the data table
will be updated and resorted from time to time, thus changing the row
location of the original data. The VLOOKUP function will work if you use a
text string in the 'Look_up_value' field, but inputting text strings in every
formula is very time consuming. Is there a way to easily specify the 'text
string' in a cell as the 'Look_up_value', rather than the cell address, whose
contents will change when you resort the table after adding new data.

 

[Billy Liddel]

Try creating a dynamic named range. An excellent source is Debra Dalgliesh's
site.
http://www.contextures.com/xlNames01.html

You could also look at data validation on the same site.

Regards
Peter

 

[Rocketman]

Thanks for your help! My understanding is if you name a cell or range, the
name is referenced to the cell(s) address and not the information stored in
the cell(s). When your list is revised by adding additional rows of
information at the bottom of the list and resorting, the named cell(s) will
still refer to the previous named cell or range, even though that information
has moved to another cell (row) due to the resorting. The list I am using
has only 2 columns, one the lookup 'text string' with the adjacent 'Value' in
the next row.

 

[Billy Liddel]

Yes, the lookup value will remain the same vlookup(e23,dynamicrange,2,0) so
e23 remains the same but the dynamic range expands, or contracts as you add
or delete rows. the COUNTA function does this.

Sorry for the delay but I went to bed shortly after posting.

regards
Peter

 

[Rocketman]

Yes, the lookup value will remain the same vlookup(e23,dynamicrange,2,0) so
e23 remains the same but the dynamic range expands, or contracts as you add
or delete rows. the COUNTA function does this.

Sorry for the delay but I went to bed shortly after posting.

regards
Peter

 

[Rocketman]

Thanks for the update. Adding or deleting a row will work OK. The problem
is when you resort the data after adding or deleting a row. The resort may
change cell 'e23' content, and thus an incorrect data point referenced by the
workbook formulas.

 

[Billy Liddel]

If you can not have a specified fererence cell in the worksheet list could
you use one in another sheet?

Peter

 

[Rocketman]

Please explain how to do this. I am not following your idea. Thanks

 

[Billy Liddel]

say you had a blank sheet where you put your references, say sheet1. Your
lookup formula on the original sheet would would be
+vlookup(sheet1!c23,Myrange,2,0) where Myrange is the dynamic range you
created earlier.

The reference calls up the sheet as well as the range. And MyRange allready
includes a Sheet reference.

Good Luck
Peter