Complicated problem, at my wits end, excel boffins help!!!!!

HI

I have a rather complex problem that might take a little explaining.

Basicaly, im designing a workbook that logs instances on accidents. Its devided into four seperate spreadsheets;

Function (where you choose which financial period you wish to view)
Periodical Report (where the data is made avalable as a report)
Weekly Input ( the current years log, which the report takes information from)
previous year (the last years log)

The problem im having is with my formula when on the cusp of the year. Our financial periods are 1-13. In the periodical report, it displays the CURRENT PERIOD, along with the 3 periods PRIOR to the current. However, I cant get my spreadsheet to recognise when it needs to take information from the PAST or PRESENT year. For example, if im viewing period 1 in the current year, it also needs to show periods 13, 12, 11 from the past year. though if i was using period 13 in the current year, it would need to display periods 12, 11, 10 also from the current year. My formula currently is as follows


=CONCATENATE(IF(D$4=1,'Weekly Input (Current Year)'!BC8,""),IF(D$4=2,'Weekly Input (Current Year)'!BD8,""),IF(D$4=3,'Weekly Input (Current Year)'!BE8,""),IF(D$4=4,'Weekly Input (Current Year)'!BF8,""),IF(D$4=5,'Weekly Input (Current Year)'!BG8,""),IF(D$4=6,'Weekly Input (Current Year)'!BH8,""),IF(D$4=7,'Weekly Input (Current Year)'!BI8,""),IF(D$4=8,'Weekly Input (Current Year)'!BJ8,""),IF(D$4=9,'Weekly Input (Current Year)'!BK8,""),IF(D$4=10,'Weekly Input (Current Year)'!BL8,""),IF(D$4=11,'Previous Year'!BM8,""),IF(D$4=12,'Previous Year'!BN8,""),IF(D$4=13,'Previous Year'!BO8,""))

however this does not surfice. As it cannot discriminate between periods in the last year and the present year.

as you can see its a bit of a mind-bender, hope someone can help me out because im going slowly insane

Ben

Your example code looks like only the column headings. There is no indication of how the data is arranged. If you segregate your data (maybe with named ranges), you might be able to use vlookup to select the data from the correct location based on the period number.

Hey thanks for replying, sorry if i was vague.

the data is arranged in columns each representing a week, which are grouped into 4 to represent the 'period'. this then feeds into a 'period total' chart, which heads each column 1-13 with the details decending from top down. This is the same for both current and last years sheets.

=IF(d4<11,OFFSET('Weekly Input (Current Year)'!BB8,0,B20),OFFSET('Previous Year'!BM8,0,B20-11))

for the other cells replace d4 with =IF(d4-1<1,14-d4,d4-1)

for the previous period
etc

I am not sure this is totally right from you post and formula, but hopefully it will give you some ideas. Your value in d4 always casues the rebuilt to move one colum to the left, so the offset formula can be used. It is not clear when you need to switch to the previous year, but hopefully you will have a few ideas

If not provide more information

Regards

Dav