Excel guru help please.

Colbaker

Colbaker

Colbaker

Associate
Joined
8 Dec 2004
Posts
1,970
Location
Paignton, Devon
Hi all,

Having a bit of a headache with excel at the moment and you guys always give good advise.

What I am trying to do is put a formula in CELL B6 on worksheet SEARCH that matches the letter in B4 with the letter in the Area worksheet tab (in the below instance it would be AREA T worksheet) and then looks up today's date and returns the cell next to that date which will have a name in it.

Is this going to have to go down the VBA coding route?

Sorry for the pics they were screen captured from my phone.

Edit to hopefully make it clearer..

Sorry should have explained a bit better

What I want to do is enter an Area letter in B4 on the SEARCH sheet and then I want CELL B6 to return the persons name that is working that day so on the second picture I would also have the date in the A column so effectively it will match B6 (Area T) with the sheet (AREA T) then lookup today's date and return the name in the cell next to it.

I think that makes more sense :)



 
Last edited:
Colbaker said:
Hi all,

Having a bit of a headache with excel at the moment and you guys always give good advise.

What I am trying to do is put a formula in CELL B6 on worksheet SEARCH that matches the letter in B4 with the letter in the Area worksheet tab (in the below instance it would be AREA T worksheet) and then looks up today's date and returns the cell next to that date which will have a name in it.

Is this going to have to go down the VBA coding route?

Sorry for the pics they were screen captured from my phone.



Click to expand...

I only know how to do the VBA route.

And that would be to do a loop to search vertically till it finds a match, then when it finds that match, move horizontally from that location +1 to add the date to that current active cell.

Probably not the best option but I never really use excel aside from the vba aspect of it
 
not entirely clear on what you are doing but you can use the INDIRECT function to reference other sheets "dynamically".

so you could use a vlookup and refer to the sheet indirectly.

So something like =vlookup(B4,indirect(A6),2,0)

so for instance if you had 'Area T' in the cell A6 it would do a lookup against sheet 'Area T' if you had 'Area C' in that cell it would refer to sheet Area C and so on and so forth.

It's one of those function that are incredibly useful but not used that much. If you start wrapping them around each other you can do some very clever things.

Also if you want to do a lookup on today just replace B4 with Today() be careful though that the date is correctly formatted in the reference sheet though, i.e. it needs to be a number or date, not text. If it is text then wrap today with something like Text(Today()),"dd-mm-yyyy")
 
Last edited:
Sorry should have explained a bit better

What I want to do is enter an Area letter in B4 on the SEARCH sheet and then I want CELL B6 to return the persons name that is working that day so on the second picture I would also have the date in the A column so effectively it will match B6 (Area T) with the sheet (AREA T) then lookup today's date and return the name in the cell next to it.

I think that makes more sense :)
 
You must log in or register to reply here.
Back
Top Bottom