Excel help 2

[phil009]

Hi, sorry I don't know what forum to put this question in so it might not belong here but here goes...I have 2 excel workbooks (one with all my data and the other which I would like to link the data to). My excel workbook with all my data (export.xls) has data dating from 1996 to 2004 and is updated on a weekly basis. This means that there are 52 columns (i.e. Week 1, Week 2, etc...). My problem is that the other excel workbook needs to have info from the current week for every year. So since this is week 26 I would want week 26 data from 1996 through 2004 and next week I will want week 27. I guess what I was thinking is just having a box at the top of my worksheet that allows you to choose the week in it and will then pull the relevant data from export.xls. Is this possible?

Any help would be much appreciated,
Phil

 

[Zathras]

Almost anything is possible.

You didn't give us a whole lot to go on, but it sounds like you could use the INDEX function in your "other" workbook and designate a cell (e.g. "A1") to contain the number of the week that you want to access.

 

[SkipVought]

Phil,

If it were me, the first thing I'd do is NORMALIZE the data. Check out -- 'Fundamentals Of Relational Database Design'

This will make working with your data MUCH MUCH easier. Insetad of 52 columns across, the table might be
[tt]
Year Week Value
[/tt]



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[phil009]

Skip\Zathras-Thanks for the quick replies.

Skip- The problem with normalizing the table is that There are ten companies and 23 categories that must be recorded for each week for each company. And, the table was already set up like this when I got it and it seems like a huge job to normalize it now.

Zathras-I have never used the INDEX function before but I just read about it on Microsoft Excel Help, I just don't understand from that how I would be able to choose the week that I would be referencing (I would only the current week). Do you understand what I mean?

Thanks again,
Phil

 

[Zathras]

Here is a quick tutorial.

First, prep your EXPORT.XLS sheet by selecting all of your data and giving it the range name DATABASE.

Then in a separate worksheet, enter a week number in cell A1, and give that cell the range name CURRENTWEEK.

Now put this formula in any other cell of the same worksheet:

=INDEX(EXPORT.xls!DATABASE,4,CURRENTWEEK)

You should see the data from the fourth row of the selected column. Change the number 4 to whatever row you want to access. You could even change it to the expression ROW() to reference the corresponding row from one sheet to the other.

 

[phil009]

Zathras-That is exactly what I needed. Thank you so much. You get a star for that one!!

Thanks to both of you, Phil

 

[phil009]

Ok-now I have finished one part, but I have one more question. Using INDEX(export.xls!Database,4,CurrentWeek) I have been able to get the current week's data but now I need the year-to-date total. So since this is week 26 I would somehow need to get weeks 1 through 26 added together (and then next week I would want weeks 1 through 27).

Any help or suggestions would be greatly appreciated,
Phil

 

[SkipVought]

Hi,

If you have the week number, for instance in A1 and if your sum range began in A3 on Sheet1, then
[tt]
=SUM(OFFSET(A3,0,0,1,A1))
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[phil009]

Skip-Thanks for your reply that really helped.

Not to further rub this question into the ground, but I have one final part to do (I promise this is the last question on this thread!). So I can currently bring up both the current week and the year to date total but now I need to get a quarter to date total. The quarters go from weeks 1-13, 14-26, 27-39, and 40-52. So if this is week 23 I would want a total from weeks 14 through 23, but if it is week 27 then I would only need the total for week 27, and so on. Is there a way that this can be accomplished??

Any help would much appreciated,
Phil

 

[phil009]

I guess I probably should mention that I tried to use your formula Skip, just with an IF statement like :
=IF(CurrentWeek2>40,SUM(OFFSET([export.xls]union!$AT$1902,0,0,1,CurrentWeek2)),IF(CurrentWeek2>27,SUM(OFFSET([export.xls]union!$AG$1902,0,0,1)),IF(CurrentWeek2>14,SUM(OFFSET([export.xls]union!$T$1902,0,0,1,CurrentWeek2)),IF(CurrentWeek2>1,SUM(OFFSET([export.xls]union!$G$1902,0,0,1,CurrentWeek2)),""))))

But it returns the whole quarter and not the part that I would like it to return,

Phil

 

[SkipVought]

Well, if you use the OFFSET formula to get Quarter to date, you'll need to

1) calculate where the quarter starts and

2) the number of weeks into the quarter

So using the formula, you need a start offset value in let's say B1 (the STARTING week number of the current quarter)...
[tt]
=SUM(OFFSET(A3,0,B1-1,1,A1-B1))
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[phil009]

Skip-Once again you come through in the clutch. You are a life saver.

Thank you so much,
Phil