Offset References

G

gthull644

I have an Excel sheet that displays products but because the product
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 ar
referenced from another sheet. So, how do I reference the product name
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

et
 
B

bj

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
 
G

Graham Hull

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.
 
D

Domenic

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!
 
Top