I need to automate a workbook to autofill some pages.
The layout is:

Page 1 is master sheet. basically the idea is:

a b c d e
f g h
1 name1 phone1 address1
2 name 2 phone 2 etc etc
3 name 3 phone 3 you get the idea
4 name 4 phone 4
5
6
7
7
8
9

pages 2-100 are individual reference sheets for each name.
sheet 2 would be:
a1 = a1 from master sheet
a2 = b1 from master
a3= c1 from master

What I would like to be able to do is find a way to automate the
process so that I can set up one sheet and every sheet i make
afetrwards would autopopulate with the information without having to
individually go to each sheet and copy in the formulas.

Looking around on other message boards I did learn the ability to use
JWalk's SHEETOFFSET function
(http://j-walk.com/ss/excel/tips/tip63.htm)
to reference the previous page and so instead of page 3 having to
manually reference back to the master sheet, it looks to the previous
page.
For example:

Sheet 3, A1= "look back at previous page and see where it got its data
from and take the data in the next cell down".

This can be done with:
=INDEX(Master!A:A,MATCH(SHEETOFFSET(-1,$A$1),Master!A:A,FALSE)+1)

However if you come up with duplicates like A10, A11, and A12 all being
"James Smith" the above formula will not work, because it doesnt really
understand going to the next row.

Any ideas, or better suggestions on how to accomplish this task?


--
shaneh
------------------------------------------------------------------------
shaneh's Profile: http://www.excelforum.com/member.php...o&userid=25033
View this thread: http://www.excelforum.com/showthread...hreadid=385612