stuck up in using lookup and reference

[ajkiran]

i need to generate an output based on this requirement.

a list is available which has 5 rows and 4 columns.
column1 has A/B/C/D/E in rows
column2 has day1/day2/day3/day4/day5 in rows
column3 has eve1/eve2/eve3/eve4/eve5 in rows
column4 has ngt1/ngt2/ngt3/ngt4/ngt5 in rows

A/B/C/D/E should appear on consecutive friday and from 6th friday again the cycle starts in form of A/B/C/D/E. I NEED TO DEVELOP A PROGRAM SUCH THAT ONLY ONE ENTRY i.e. for fri1 is required rest will appear every friday.

I TRIED VLOOKUP but it is becoming very lengthy. but it requires a big database to generate result. i tried index, sumproduct but it it still requires a big database to work on.

[ajkiran]

any one who can help. i have to develop this sheet to expedite as the old method is tedious and bulky

[teylyn]

I'm not quite sure I understand what you want to achieve. What data do you enter where and what result do you want to see where?

You can just enter A B C D E into five cells and then copy these cells down.

Or you could use a formula. If starting in row 9, for example

=CHOOSE(MOD((ROW()-9)/5,1)*5+1,"A","B","C","D","E")

copy down as far as required.

cheers

[ajkiran]

there is a task which is represented by 'A' and it appears on friday. similarly another tasks 'B', 'C', 'D' and 'E' will appear on consecutive fridays. the first task 'A' will reappear on 6th friday 11th friday and so on. similar will be the case for other tasks. they reapear after 6 weeks. the tasks are cyclic, means Athen B then Cthen D then E then A. i want to develop a excel sheet that follows the rules mentioned above.

[teylyn]

The Choose() formula above should work, then.

[ajkiran]

do i need to have datasheet for the whole year and thereafter or only for the first 5 fridays and automatically it will check the current day and generate result

[teylyn]

do i need to have datasheet for the whole year

No, you don't. You only need the formula I suggested above, adjusted to the row number you start it in. If the formula starts in row 9 use

=CHOOSE(MOD((ROW()-9)/5,1)*5+1,"A","B","C","D","E")

and copy down. If the formula starts in row 4, use

=CHOOSE(MOD((ROW()-4)/5,1)*5+1,"A","B","C","D","E")

Can you adapt that to your spreadsheet? If not, please post your workbook.