J
Jason.Alden.Benoit
I have modified the index/match formula from a tutorial I got off the
Microsoft site to almost suit my needs. I have ran into the issue of
pulling data from an unopen workbook, however. I THINK I have decided
to use one huge workbook (NR Data) that uses one tab of data for every
week, and leave that workbook and the workbook this formula goes into
in the same place.
=INDEX('[NR Data.xls]Sheet1'!$A:$Y,MATCH("Totals for "&$A$2&":",'[NR
Data.xls]Sheet1'!$A:$A,0),MATCH(B$3,'[NR Data.xls]Sheet1'!$2:$2,0))
This formula will be copied into many cells in a person's summary
sheet for the year. $A$2 will be the lookup reference for a person's
name on their sheets with $A:$A being where on "NR Data" it look for
that persons name. The idea is to have the worksheet this formula is
used for a template. A2 has a formula that updates unpon the renaming
of the sheetname it is in.
I will have B (B$3) column be the area where it will reference
category headers, dynamic to the column I paste the formula. $A:$Y
will be where the formula looks for the match to the category from
column "B"
Can I easily make "sheet1" a reference to the dates (i.e.
2/2/2008,2/9/2008) that will be in column A? If so, is index the way
to go? Is there a better approach to all of this? I'd rather not have
to open this big workbook every time, if possible, for up to 200
people's sheets from a network drive.
Thanks,
Alden
Microsoft site to almost suit my needs. I have ran into the issue of
pulling data from an unopen workbook, however. I THINK I have decided
to use one huge workbook (NR Data) that uses one tab of data for every
week, and leave that workbook and the workbook this formula goes into
in the same place.
=INDEX('[NR Data.xls]Sheet1'!$A:$Y,MATCH("Totals for "&$A$2&":",'[NR
Data.xls]Sheet1'!$A:$A,0),MATCH(B$3,'[NR Data.xls]Sheet1'!$2:$2,0))
This formula will be copied into many cells in a person's summary
sheet for the year. $A$2 will be the lookup reference for a person's
name on their sheets with $A:$A being where on "NR Data" it look for
that persons name. The idea is to have the worksheet this formula is
used for a template. A2 has a formula that updates unpon the renaming
of the sheetname it is in.
I will have B (B$3) column be the area where it will reference
category headers, dynamic to the column I paste the formula. $A:$Y
will be where the formula looks for the match to the category from
column "B"
Can I easily make "sheet1" a reference to the dates (i.e.
2/2/2008,2/9/2008) that will be in column A? If so, is index the way
to go? Is there a better approach to all of this? I'd rather not have
to open this big workbook every time, if possible, for up to 200
people's sheets from a network drive.
Thanks,
Alden