I dont think there is a formula to do this

[Caveman]

I have 2 worksheets,
Worksheet1 A3:A20 are names, B1:H1 are days of the week (SUN-SAT),
B2:H2 are days of the week to match the calender days
(2/1/2005-8/1/2005),when you change the sun date it changes the whole
weeks date.
Worksheet2 A2:A366 dates of the year (1/1/2005-31/12/2005), the next
column is based on a 4on/2off works rota system, B2:B366
on/on/on/on/off/off/on/on/on/on/off/off & so on. C1:Z1 have the same
names as sheet1 column A.

I need a formula that looks at a name & date on worksheet1, Looks at
worksheet2 for the same name and date, (J Bloggs (h1) on the 6/1/2005
(A7)) then return the data from the intersection of the grid ref, if
empty return the data from B7.

 

[Ken Wright]

Can't quite see how C1:Z1 on worksheet 2 which is a 24 cell range, can
contain the same values as A3:A20 on worksheet 1 which is an 18 cell range,
unless of course you have duplicates, however:-

On worksheet 1 in cell B3 put the following formula and copy across to
B3:H20

=IF(INDEX(Sheet2!$A$1:$T$366,MATCH(Sheet1!B$2,Sheet2!$A$1:$A$366,0),MATCH(Sheet1!$A3,Sheet2!$A$1:$T$1,0))="",INDEX(Sheet2!$A$1:$T$366,MATCH(Sheet1!B$2,Sheet2!$A$1:$A$366,0),2),INDEX(Sheet2!$A$1:$T$366,MATCH(Sheet1!B$2,Sheet2!$A$1:$A$366,0),MATCH(Sheet1!$A3,Sheet2!$A$1:$T$1,0)))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------

 

[Caveman]

Thanks Ken
works perfect, how do you become a MVP, ie how do you learn to
conbine the different formulas to make one large one.
are there courses or is it just experience

 

[Ken Wright]

LOL - Don't get too hung up on the MVP moniker ( Though don't get me wrong -
I do like having it :-> ) - There are smarter people than me in here that
don't have it, but that's because there are lots of different factors taken
into account - Enough boring stuff though

As for the formulas, just experience really, and there truly is very little
to it. Take the formula I gave you:-

=IF(INDEX(Sheet2!$A$1:$T$366,MATCH(Sheet1!B$2,Sheet2!$A$1:$A$366,0),MATCH(Sheet1!$A3,Sheet2!$A$1:$T$1,0))="",INDEX(Sheet2!$A$1:$T$366,MATCH(Sheet1!B$2,Sheet2!$A$1:$A$366,0),2),INDEX(Sheet2!$A$1:$T$366,MATCH(Sheet1!B$2,Sheet2!$A$1:$A$366,0),MATCH(Sheet1!$A3,Sheet2!$A$1:$T$1,0)))

That began as a single smaller INDEX formula, using the MATCH function to
get the arguments:-

INDEX(Sheet2!$A$1:$T$366,MATCH(Sheet1!B$2,Sheet2!$A$1:$A$366,0),MATCH(Sheet1!$A3,Sheet2!$A$1:$T$1,0))

As you can see, it is a lot smaller than the final, and 95% of the job has
been done by getting this far

Then all I need to do is cater for your 'If xx is blank, do yyy', and that
is handled by an IF formula:-

=IF(formula1="",formula2,formula1)

formula1 = the original formula

formula2 is a slight tweak on the original formula to return the value from
Col B, so i just switched the MATCH function that gave me the column for the
number 2 (ie second column):-

INDEX(Sheet2!$A$1:$T$366,MATCH(Sheet1!B$2,Sheet2!$A$1:$A$366,0),2)

Then just substitute the formula1 and formula2 bit in
=IF(formula1="",formula2,formula1) and you are done.

Big formulas are only ever a combination of smaller ones, and as long as you
work logically through what you are doing, testing each bit as you go, it
usually hangs together quite well. Often easy to lose sight of what you are
doing though and get focused on a particular formula, and then someone posts
a much shorter solution <g>

Regards
Ken..................