Creating an Index

[dhbyrne]

HI
I am trying to create an index in Excel
On Sheet 1 I have the index and on sheet 2 I have a product list.The products are divided into sections by the type of product e.g. Dog Food is one group and Cat Food is another etc and goes on for about 40 pages. What I want to be able to do is create an index on sheet 1 that will automatically update the page numbers on Sheet 1 from sheet 2 as they cange.
Can someone please help me, even if it means writing a macr

thanks in advance.

 

[David McRitchie]

Take a look at the VLOOKUP Worksheet Function
http://www.mvps.org/dmcritchie/excel/vlookup.htm

sheet1 cell G2:
=VLOOKUP(A2,Sheet2!$A:$K,10,0)

The 0 indicates that the table in sheet 2 is not sorted
by product number, but you must use this if you want
an exact match so it doesn't matter if it is sorted or not.

sheet2: column K has the page number
column A has the product number

To avoid viewing #N/A! errros you can code
=IF(ISNA(VLOOKUP(...,...,...,False)),"Item not found",VLOOKUP(...,...,...,False))

=IF(ISNA(VLOOKUP(A2,Sheet2!$A:$K,10,0)),"Item not found",VLOOKUP(A2,Sheet2!$A:$K,10,0))

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

HI,
I am trying to create an index in Excel.
On Sheet 1 I have the index and on sheet 2 I have a product list.The products are divided into sections by the type of product

e.g. Dog Food is one group and Cat Food is another etc and goes on for about 40 pages. What I want to be able to do is create an
index on sheet 1 that will automatically update the page numbers on Sheet 1 from sheet 2 as they cange.

 

[dhbyrne]

thanks for the info, reading that site now...
I was hoping to use the [page] of [pages] in the footer to find out what page the item was on. Is that possible?

 

[David McRitchie]

I was hoping you meant an entered catalog page number, but I
misread here are some links to help find page numbers, read the
entire thread because Myrna had a function, but trouble with it in
Excel XP.. See
http://google.com/groups?selm=u#[email protected]

Laurent's subroutine would probably be best, you run it as needed
so wouldn't be bogging things down as much.