Help with formula with Named Ranges

[Corey ....]

I have 3 Named Ranges in a Sheet Named("SavedData") as follows:
ModuleNames =SavedData!$A$2:$A$200 ; NamesDone =SavedData!$D$2:$D$200 and ;
CompetentDate=SavedData!$AD$2:$AD$200.

In another sheet named Completed_Modules i have set up a spreadsheet that
lists any Module Names that have entered into the SavedData sheet(Column
A).
I also have set up the spreadsheet to display any Names that have been also
entered into the SaveData sheet into the same row as the Module names but in
(Column D).
The CompetentDate when completed is also placed into the SavedData sheet on
the same row of data, in (Column AD).

What i am hoping to acheive is to display in the Completed_Modules
worksheet, the DATE if there is one entered in the SavedData, but in a cell
that has the NamesDone and ModuleNames matching.

I tried this formula but get no data in the cell:
=IF(AND(ModuleNames=A13,NamesDone=B1,CompetentDate<>""),CompetentDate,"")

A13 is one of a list of Module Names in Column A in the spread sheet
B1 is one of a list of Names in Row 1

Name 1 Name 2 Name 3 Name 4 Name
5 etc across sheet
Module Name 5

Module Name3

Module Name 1 1/9/07

Module Name 8

Others etc....


As can be seen above if there is a match of the Module name, Name and there
is a Date in the CompetentDate sheet(CompetentData<>"") then the date is
placed into the spreadsheet (1/9/07).



How can i get the named ranges to work in the formula as explained?

regards

Corey....

 

[T. Valko]

Try this array formula** :

=INDEXCompetentDate,MATCH(1,(ModuleNames=A13)*(NamesDone=B1)*(CompetentDate<>""),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[CurlyDave]

I have 3 Named Ranges in a Sheet Named("SavedData") as follows:
ModuleNames =SavedData!$A$2:$A$200 ; NamesDone =SavedData!$D$2:$D$200and ;
CompetentDate=SavedData!$AD$2:$AD$200.

In another sheet named Completed_Modules i have set up a spreadsheet  that
lists any Module Names that have entered into  the SavedData sheet(Column
A).
I also have set up the spreadsheet to display any Names that have been also
entered into the SaveData sheet into the same row as the Module names butin
(Column D).
The CompetentDate when completed is also placed into the SavedData sheet on
the same row of data, in (Column AD).

What i am hoping to acheive is to display in the Completed_Modules
worksheet, the DATE if there is one entered in the SavedData, but in a cell
that has the NamesDone and ModuleNames matching.

I tried this formula but get no data in the cell:
=IF(AND(ModuleNames=A13,NamesDone=B1,CompetentDate<>""),CompetentDate,"")

A13 is one of a list of Module Names in Column A in the spread sheet
B1 is one of a list of Names in Row 1

                                Name 1    Name 2    Name 3    Name 4    Name
5 etc across sheet
Module Name 5

Module Name3

Module Name 1                        1/9/07

Module Name 8

Others etc....

As can be seen above if there is a match of the Module name, Name and there
is a Date in the CompetentDate sheet(CompetentData<>"") then the date is
placed into the spreadsheet (1/9/07).

How can i get the named ranges to work in the formula as explained?

regards

Corey....

Check out Vllokup
http://www.contextures.com/xlFunctions02.html
and index Match
http://www.contextures.com/xlFunctions03.html