Discussion in 'Computing and ICT' started by magicstar29, Dec 31, 2011.

1. ### magicstar29

Hi,
I am hoping someone on here can help me with my spreadsheet. I am setting up a spreadsheet showing the reading levels of the children. As they move up the levels the teacher inputs the current date in the corresponding column.
I have set up a column next to the child's name where I would like it to automatically input their current level so the teacher can see the current levels at a quick glance. I am able to use the formula =max(c33) which finds the newest date and puts this in the cell. However I would like it to put the current level in the cell not the date. Is there a way of finding the newest date in the row and then automatically putting the column heading (level) in the cell?

So for child a I would like cell B2 to state ORT 2 not the date.

2. ### magicstar29

Sorry it did not post the picture of my spreadsheet.
A B C D E F
1 Name Current level ORT1 ORT1+ ORT2 ORT3
2 child a 1/09/11 4/10/11 5/11/11
3

3. ### HappyHippy

I'm sure there will be people along shortly with more advanced spreadsheet skills than me, but a I see it you have three options:

1. Make it a manual process.

2. Use VBA to write a macro that finds the right-most populated cell and moves up to get the level and paste into the correct cell.

3. Use multiple nested if statements. Something along the lines of "=IF(C2="","BelowORT1",IF(D2="","ORT1",IF(E2="","ORT1+",IF(F2="","ORT2","ORT3"))))". Obviously this would get longer if there were more columns and it would only pick the highest level (not necessarily the latest one).

4. ### jweb2k

I enjoyed this one!

=INDIRECT(CHAR(66+MATCH(MAX(C2:F2),C2:F2,0))&"1",TRUE)

It picks the latest level achieved rather than highest level, which would be a similar formula but I believe this is the one you'd want?

5. ### magicstar29

Thank you very much, it works!!!
I don't understand any of it but it does what I want. Thank you
Happy New Year

6. ### jweb2k

In case you were interested, I'll explain it (poorly!):

Working from the centre function outwards:
- max() of the dates to work out which cell needs to be targetted.
- match() to work out what column the maximum is in (returns a number)
- char() to change that matched number into a letter, add 64 to it to start at A (the ascii for A is 65 but the match returns "1" for the first column rather than "0") and another two (+66) as your table starts at column C.
- indirect() to sort out the exact cell, whether it's C1, D1, E1 etc based on all of the above mixed together!
Hopefully that'll help you if you want to extend the columns or modify it!

Thanks

8. ### JaquesJaquesLiverotEstablished commenter

I'm surprised that no-one has made the point that your spreadsheet is actually a database, and wouldn't have required such a complicated formula if you'd have done it in Access!