Excel Magic Trick 983: Get Last Data For Employee From Across Many Sheets

Create a single formulas that will lookup an employee's name and retrieve the last value for that employee across many sheets and display the values in a column:
1) See how to use the INDIRECT function to create a sheet reference
2) See how to use the LOOKUP function to lookup the last value for an employee using an array calculation.
3) LOOKUP function can perform array calculations without using Ctrl + Shift + Enter
4) Get Last data from a column of employee names and numbers.

[excelisfun]

Oh, no... I forgot to post it!!! I will post it in a few hours. Sorry about that.

[excelisfun]

Not so much too good: just a guy having fun with Excel!

[excelisfun]

You are welcome! Sorry about the complicated...

[excelisfun]

I just posted it. Sorry about the delay - too much to do and I just forgot...

[excelisfun]

I am glad that the video helped, but as for Excel 2013 and no dancing ants, that is very sad, since dancing is so good!!

[lfranco]

I owe your channel so much that I wouldn't mind watching the whole adds if that gives you some sort of well deserved revenue.

[excelisfun]

You can skip the ads. But, yes, if the full ad is watched then money is received.

[excelisfun]

I use the F9 key to evaluate the formula element. Maybe your keyboard does not have F keys. You can also evaluate by going to Formulas Ribbon Tab, Calculation group, Calculate Now button, or use the Alt keyboard: Alt, M, B.

[excelisfun]

I just posted it. I am sorry for the forgetful delay...

[millsgirl]

I'm afraid we now expect perfection from you. It's your own fault, you're too good! Thank you.

[excelisfun]

I am not sure. Try contacting Bob Umlas, the author of that book.

[AhmadMustafaMohmedMetwalley]

it is more complicated, but you did for your effort.

[excelisfun]

I think you can just use Number Formatting to show the serial number dates in a different way. A custom Number format like this might work:

m/d/yyyy

If it is text, maybe a formula like:

=DATE(RIGHT(A2, 4), MID(A2, SEARCH("/", A2)+1, SEARCH("/", REPLACE(A2, 1, SEARCH("/", A2), ""))-1), LEFT(A2, SEARCH("/", A2)-1))

[Excel]

Thank you very much - very good explanation

[ExcelStrategy]

What i like the most here, it's the brilliancy of dividing for one every element of the "bolean vector" converting all the "FALSE" to errors and then drop it inside the "lookup". It's a usefull technique that can have many applications. PS. In my excel 2013 the "dancing ants" does not dance anymore becouse my graphic card does not support direct X 10 :(

[ronwb]

Its Mark Dodge and Craig Stinson for the authors.

[boazeps]

I'm not Mike, or even close, but "F9" means the button F9 on the keyboard's upper side- above the numbers (F1 to F12)

[MrSarky]

Hello Online Excel Team,

Thank you Mike for the tipp, I tweaked a bit with your formula just to get one where I don't need sheets' name in different cells, as well as assuming that there could be more than 20 records on a worksheet :

=LOOKUP(2, 1/($B$2=INDIRECT("'data("&ROWS(E$2:E2)&")'!"&"B2:B"&MATCH("zzz", INDIRECT("'data("&ROWS(E$2:E2)&")'!"&"B:B"), 1))), INDIRECT("'data("&ROWS(E$2:E2)&")'!"&"C2:C"&MATCH("zzz", INDIRECT("'data("&ROWS(E$2:E2)&")'!"&"B:B"), 1)))

My array formula I first come up with looks like this :



In the end it might be a lot easier to do and to see through if we indeed just put the worksheet names in a helper column.

Take care !

[lfranco]

Hi. Does it make any difference to you if I skip the adds? I mean does your site receive money if the full add is watched?

[ronwb]

In Excel Inside Out they refer to the INDIRECT function in that it returns the contents of a cell using its reference. The ref-text argument is said to take the A1 style or R1C1 style reference or a cell name. They go through this example where B3 is 2.888 and C6 contains the B3 cell reference and the formula is =INDIRECT(C6). It returns the value 2.888 but I'm not sure of the process Excel is following on INDIRECT. Can you help please?