Returning the latest value to another cell

[Richmate]

Hi

I have managed to get myspreadsheet to tell me when my spreadsheet was last updated (thanks to another member of the forum) but I also need my spreadsheet to tell me who last updated the spreadsheet.

Let me try and explain.
My spread sheet is split into 10 sections, each of which contains a name dropdown list (col Q)and a date list (col R). The idea is the user will update this sheet each time a task is done. They will then put in the date complete and whom the task has been handed too.

This information is then updated to another part of the worksheet Col K11 & N11. Now as I have explained above I have managed to return the latest date to Col N11 but I also need to return col R to col N11

I have attached the spreadsheet to make it clearer.

thanks in advance

Rich

[deemo]

Perhaps not the most elegant but how about you enter this in N11

=INDEX(11:11,,13+(COUNTA(S11,X11,AC11,AH11,AM11,AR11,AW11,BB11,BG11,BL11))*5)

which will look at the last updated amends run and return the "passed to" column

Make sure you switch the data validation off for that column though.

[Richmate]

Thanks for the prompt reply.

I have inserted the formula but its only returning the date into column n11 (hope ive done it correctly). I also need to show the result of Q11 in K11 when R11 is updated. Im not really concerned with column S as I need to show just Q and R (or V and W depending on the latest date). Sorry if my previous post didnt explan this properly.

Any ideas

Rich

[NBVC]

Does this work?

=IF($N11="","",INDEX($Q11:$BJ11,MATCH(1,INDEX(($Q$7:$BJ$7="Passed to")*($R11:$BK11=N11),0),0)))

copied down.

[Richmate]

Ive pasted this into N11 but im getting a circular reference warning!

Rich:-)

[Richmate]

No apologies this is working. brilliant!!!.

NBVC would it be possible for you to break down the formual and tell me how it work. Not to worry if it would take you and age to do that.

Thanks for all the replies. Great work all, most appreciated.

Rich

[NBVC]

Formula:

=IF($N11="","",INDEX($Q11:$BJ11,MATCH(1,INDEX(($Q$7:$BJ$7="Passed to")*($R11:$BK11=N11),0),0)))

First it checks if N11 is empty... if so it leaves the cell blank so that you don't get errors or misleading results.

Next it index a range you want retrieve your data from, that is Q11:BJ11

Then it finds the position to extract from with the use of a MATCH() function.

The MATCH function syntax is: MATCH(Lookup_Value,Lookup_array,match_type)

So we are looking for a 1 in the array that this INDEX(($Q$7:$BJ$7="Passed to")*($R11:$BK11=N11),0) produces and getting an exact match since we have a 0 match_type.

The INDEX(($Q$7:$BJ$7="Passed to")*($R11:$BK11=N11),0) formula builds an array of 1's and 0's based on 2 conditions being met. Condition 1 is that $Q$7:$BJ$7 equals "Passed to" so that we make sure we focus only on those columns that contain that string in row 7. This produces an array of TRUE/FALSE results. The second condition $R11:$BK11=N11 looks for dates matching your max date in N11 within range R11:BK11... (notice the offset of ranges from Q:BJ to R:BK so that matches line up for the next step). So this produces another array of TRUE/FALSE results.

The 2 resultant arrays are multiplied together... and when TRUEs and FALSE's are multiplied together they give results of 1's and 0's, where TRUE*TRUE yields a 1 and all other products between the two, yields 0.

So in the end, you should have only one 1 in the array, that is where the row 7 is "Passed to" and row 11 matches date in N11.

Now Match finds and reports the position of that 1 and the INDEX function pulls the value at that matching position to give you the result.

Note: If there is more than 1 match found (i.e. the Max date appears multiple times, then the first is extracted only)

Hope that helps.

[Richmate]

WOW, may take me a while to get my head round all that, but its really appreciated.

Ive learnt alot that will be useful..

Many Thanks

Rich