J
jose123
In my template I have the following information. I'd like to get the data
out of the template and into a workbook of its own. What would be the best
approach to set-up this data? For example, Data1 and Data2 can be their own
worksheet in the same workbook and the data is set-up as shown or should the
data be in it's own workbook and create a new tab when the effective date
changes. I don't want to change the formulas in the template everytime a new
tab is created. Also, I need to know how to do a lookup of this data.
Data1:
Eff. Date EE# EE Name Rate Scale WDays
12/01/08 1 Doe, John R1 100 M-F
01/01/09 1 Doe, John R1 100 W-Sun
12/01/08 2 Rae, Martha R2 80 M-F
03/01/09 2 Rae, Martha R2 90 M-F
Data2:
----------Rate-----------------
Scale R1 R2 R3
12/01/08 100 23.62 23.92 23.62
12/01/08 90 21.26 21.56 21.26
12/01/08 80 18.90 19.20 18.90
12/01/08 70 16.53 16.83 16.53
03/01/09 100 23.72 24.92 23.82
03/01/09 90 21.46 21.76 21.36
03/01/09 80 19.90 19.30 18.95
03/01/09 70 16.63 16.93 16.73
On 12/05/08 I look up information for EE#1 from data1 and data2 I would want
information from the row eff. 12/01/08 to be returned. R1 and 100 would be
used for the lookup from data2 as wellas the eff. date.
On 04/01/09 I look up information for EE#2 from data1 and data2 I would want
information from the row eff. 03/01/09 to be returned. R2 and 90 would be
used for the lookup from data2 as well as the eff. date.
out of the template and into a workbook of its own. What would be the best
approach to set-up this data? For example, Data1 and Data2 can be their own
worksheet in the same workbook and the data is set-up as shown or should the
data be in it's own workbook and create a new tab when the effective date
changes. I don't want to change the formulas in the template everytime a new
tab is created. Also, I need to know how to do a lookup of this data.
Data1:
Eff. Date EE# EE Name Rate Scale WDays
12/01/08 1 Doe, John R1 100 M-F
01/01/09 1 Doe, John R1 100 W-Sun
12/01/08 2 Rae, Martha R2 80 M-F
03/01/09 2 Rae, Martha R2 90 M-F
Data2:
----------Rate-----------------
Scale R1 R2 R3
12/01/08 100 23.62 23.92 23.62
12/01/08 90 21.26 21.56 21.26
12/01/08 80 18.90 19.20 18.90
12/01/08 70 16.53 16.83 16.53
03/01/09 100 23.72 24.92 23.82
03/01/09 90 21.46 21.76 21.36
03/01/09 80 19.90 19.30 18.95
03/01/09 70 16.63 16.93 16.73
On 12/05/08 I look up information for EE#1 from data1 and data2 I would want
information from the row eff. 12/01/08 to be returned. R1 and 100 would be
used for the lookup from data2 as wellas the eff. date.
On 04/01/09 I look up information for EE#2 from data1 and data2 I would want
information from the row eff. 03/01/09 to be returned. R2 and 90 would be
used for the lookup from data2 as well as the eff. date.