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

  • Thread starter Thread starter Rocketman
  • Start date Start date
R

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.
 
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.
 
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
 
Billy Liddel said:
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
Click to expand...
 
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.
 
If you can not have a specified fererence cell in the worksheet list could
you use one in another sheet?

Peter
 
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
 
Back
Top