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.

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.


--
jamescox
------------------------------------------------------------------------
jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=111442

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


"jamescox" wrote:


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.


--
jamescox
------------------------------------------------------------------------
jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=111442