.. I am getting #REF! when I put in the formula.
That usually means your sheetnames listed in B1 across
somehow doesn't match exactly with what's on the tabs

Easier to see things if you could upload your sample
using a free filehost, then post a link to it here

For example, you could use this free filehost to upload:
http://www.freefilehosting.net/

Copy the "direct link" which is generated after you upload,
then paste it here

(Desensitize your sample as appropriate)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,500 Files:361 Subscribers:58
xdemechanik
---
"Mike" wrote:
Thank you very much for your help. I am getting #REF! when I put in the
formula. A9 has my heading "Position", B9 is heading "Lenses", C9 is heading
Cost. I have separate departments listed on the same sheet. Each department
may have a couple "Position" names that are the same, but have different data
from each other. Example - Position AR-01 may be in 2 or 3 departments
depending on the month but each have different values in the Lenses and Cost
columns. A9:A30 contains department XYZ, A37:A32 contains department
ABC....for 7 departments. Each have the same headings for the columns.
--
Mike