I have an Excel sheet that displays products but because the products
can be various states I do not want the product names on every row.
eg:

Product 1____ Sold
_______ ____ Cancelled
_______ ____ Ordered

Product 2____ Sold
_______ ____ Cancelled
_______ ____ Ordered

because the product names constantly change the product names are
referenced from another sheet. So, how do I reference the product names
to a list in another sheet so that i can just drag down the references?
eg:
_
Product_List_____Report_sheet_

Product 1____(link to) Product 1
Product 2
Product 3
Product 4____(link to) Product 2

etc


--
gthull644
------------------------------------------------------------------------
gthull644's Profile: http://www.excelforum.com/member.php...o&userid=23794
View this thread: http://www.excelforum.com/showthread...hreadid=374479

I assume what you want to do is to pull the sold cancelled and ordered data
to the second sheet.

if on both sheet 1 and sheet 2 the product names are in column A
what you might do in for the sold on for product 1 on sheet 2 use
=offset(Sheet1!$A$1,match(A2,Sheet1!1$A$1:$A$1000, 0)-1,1)
end in ),1) for the Cancelled
and in
(+1,1) for the Ordered

"gthull644" wrote:


I have an Excel sheet that displays products but because the products
can be various states I do not want the product names on every row.
eg:

Product 1____ Sold
_______ ____ Cancelled
_______ ____ Ordered

Product 2____ Sold
_______ ____ Cancelled
_______ ____ Ordered

because the product names constantly change the product names are
referenced from another sheet. So, how do I reference the product names
to a list in another sheet so that i can just drag down the references?
eg:
_
Product_List_____Report_sheet_

Product 1____(link to) Product 1
Product 2
Product 3
Product 4____(link to) Product 2

etc


--
gthull644
------------------------------------------------------------------------
gthull644's Profile: http://www.excelforum.com/member.php...o&userid=23794
View this thread: http://www.excelforum.com/showthread...hreadid=374479

Thanks for the reply. It's not the data I want from the other sheet but
the product names. They have to be linked to the list of products in
such a way that I can just drag down the product names and not have to
worry about the gaps between the product names in the presentation
sheet.

If I understand you correctly, assuming that Sheet1 contains your source
data, and that the product names are listed in every fourth row, try the
following...

On a separate sheet:

A1, copied down:

=OFFSET(Sheet1!$A$1,ROWS($A$1:A1)*4-4,0)

Hope this helps!

In article .com,
"Graham Hull" wrote:

Thanks for the reply. It's not the data I want from the other sheet but
the product names. They have to be linked to the list of products in
such a way that I can just drag down the product names and not have to
worry about the gaps between the product names in the presentation
sheet.