Offset formula help wanted

N

Narnimar

I want better formula to get the value in a1 of a workbook than this.
=-(OFFSET([DATABASE.xlsx]CONSUMPTION!$A$2980,1,2,1,1))
The above DATABASE sheet is regularly updated and the cell value of
$A$2980 will move down. In this case this formula will not help me. The
$A$2980 has the fixed Itemname but I do not know how can I use formula. What
is the change I can do to this? Please help.
 
J

jamescox

If the fixed Itemname is unique in column A of that worksheet, they you
may be able to use MATCH to return the position of the sought Itemname.
With the position, you may be to construct the address
([DATABASE.xlsx]CONSUMPTION!$A$2980) by using the CONCATENATE function.

If Itemname is not unique, this would still work IF you needed the
location of the topmost occurance of Itemname.
 
N

Narnimar

Acually not working if Itemname moved from $A$2980 while I update the
DATABASE. Because my formula is not searching for Itemname's address.
 
Top