how to lookup value based on a match and max function

[itsjustmeee]

Hi,

I've been making a time sheet for our office, and I am drawing a blank (maybe bcoz I've been staring at excel for days now)..

I have a table for date, start and end of task, task, then status of task.... so let's say on the last column, I have "date completed" for tasks was pending (so if they finish it the next day or whenever, then beside the "pending" status, whenever it gets finished at another date, the cell beside it is supposed to look up the actual date it was "done"....

I'm thinking it should be a combination of looking up the same task name, and value of status should be done, to fetch the date. (i've tried match and index, then matchif, then even maxif... but I am not getting the right result)

at the back of my head, i feel that I am on the verge of figuring it out, but I am getting close to my deadline, so I need some help fast.

I'm sure I am not explaining this well.... pls check out my sample sheet ("daily" sheet) and have a look...

Any suggestion will be a great help to me!!!!

Thanks in advance!

[NickyC]

Hi
I'm a bit confused - how can there be an end time if the action is still pending?

[itsjustmeee]

Hi!! Thanks for checking it...

Actually, its to be used by the employee continuously for the whole day (so we can get the total delivered hours). so if in case he has to stop what he is currently doing and do something else (eventhough he didn't finish the current one yet), then he has to log the "end time" and then log the next task.... so the status for that previous one will be 'pending'.... now if he will come back to the previous task, and finish that, then the status will be 'done (previous task)'...

so I need to somehow, based on previous task (column H), find the task in column G (when he started or last worked on it).... so that the previously "pending task" will have a "completed on (for pending tasks)" date in column K....

I probably need to rework it completely but somehow I still want to see if this can be done or not.... :D

Hope i explained it well enough.... and thanks again for taking the time to read.

If you've any suggestion to make it better, I'm all ears!!

[NickyC]

Hi
Is this too simple - in cell K11

Please Login or Register  to view this content.

[itsjustmeee]

Hi!!

Thanks so much for taking the time... actually, I may not be explaining it well enough.

the previous task that was pending, once he continues with it at another time or /and date, then I need to get the "date" it was finished to show up beside the status "pending" on column K.

I've attach a sample input like this:

So let's say, for example, if I started the task "fix light" (on G11) on Nov 4 (B11) then left it pending(J11).... on Nov 5(B15) I started with the same task "fix light" which I will then place on column H15 "Previous task"--which should mean that it was a previous task that I didn't finish.... so when I finish it on Nov 5, I need to go back to the previous list on Nov 4 which is stating "pending"(J11) to say the date it was finished (which is Nov 6) on K11...

so basically, I need column K to:
1. be alert whenever there is a task in column H (which means it was a previously started task), then look for the same task name from "column G"

2. and, whenever the task in column J is "pending", then column K must look for the date when it finds the task is stating "done" on that later date....

so in the end, K11 have to show the date Nov.6 (from b15)

phew!! i hope it's clearer this time?? either I've made a complicated mess or complicating a simple thing to start with bcoz I still can't figure it out but it kinda feels like it's at the tip of my toungue (or brain in this case??! lol

thanks again!

[NickyC]

Hi
Someone cleverer than me can probably do this with lookup formulas, but I'd do it with a macro.
Does this do what you're after?

Please Login or Register  to view this content.

[itsjustmeee]

hi!!

I've tried your suggestion but it doesn't seem to work. nothing is showing

I only have a small grasp of macro in excel (and only have a very rusty background in Visual Basic-unpracticed theory mostly, and I actually cannot apply any of what I know in VB to excel vba.. lol!)... but your code looks like it should get me what I'm looking for....

Maybe I am doing it wrong?

I pasted it in the admin (daily) under 'general' (should I choose worksheet instead?)

still I am so thankful you're giving your time to help out (shy mode)

*(iv attached what I did using your code....)

[NickyC]

Press alt+F11 to enter the visual basic area, select insert >module and paste the macro there. Select "run" to run it.

[itsjustmeee]

omg! its working!!!!

thank u!!

u don't know how much it means to me! I'll never forget what you did, Nicky (esp. your extra patience in understanding my prob)!!!

Hope I can give the same favor back to someone like you've done for me someday (when I'm much better at it :D)

again, thank you so much!!!!! you rock!!

[itsjustmeee]

can i ask one last question??

i've been trying it and it works fine whenever I run it.

but when I tried entering pending and done tasks it doesn't automatically show the done date unless I go back to the module sheet and run it...

(even when I re-open the page, i have to click "run" before it does the function)

final help pls...!

[NickyC]

Yes - that's the thing with a macro, you normally run it each time you update the sheet.

It can be done easier than entering the VBA area each time though.

Do you have the ‘developer’ tab visible on your computer? If not:

• select the office button (probably an orange circle on the top left of the screen with the windows logo in it)
• select Excel Options (from the bottom of the screen)
• select the ‘popular’ tab
• check ‘show Developer tab in the ribbon’
• press “OK”

When you return to Excel, the developer tab should be visible in the ribbon of menus at the top of the screen (probably to the right of ‘view’).

To run the macro, select the developer tab and click on macros in the ‘code’ group, then press ‘run’ to run it.

If you like I can tell you how to make the macro run whenever you change the worksheet, but this could slow you down if it’s a big worksheet and can become unstable when you do complex changes to the worksheet. So unless you need the macro to run automatically, I suggest you run it manually.

[itsjustmeee]

Hi Nicky,

thanks again! now I understand it. i am quite satisfied doing it manually, it won't take much effort anyways. :D

but just for the sake of learning, can u also tell me how to make it run when i change the worksheet? if it won't take you too long to show me, that is.

in any case, uv already made my day, actually you made my week! hahaha

[NickyC]

Ok

• Right click the sheet name tab at the bottom of the sheet.
• Select ‘view code’
• From the left hand drop-down menu at the top of the sheet, select ‘worksheet’
• From the right hand drop-down menu at the top of the sheet, select ‘change'

This will set up a macro for you to run whenever the sheet changes

Copy the code I gave you earlier, excluding the title and ‘end sub’, into the new macro, so it looks like the code below.

Whenever you change that sheet, the macro should run.

You may have to change the file type to a macro-enabled worksheet (with the extension .xlsm not .xlsx) to allow this to run

Please Login or Register  to view this content.

[itsjustmeee]

You are so great!

Thank you again and again!!! i've learned a lot from you in just a couple of days or so.. I really appreciate it <3

Hope you won't get tired of helping out people like me here!!!

thanks a bunch!!!!

:D