Return selected data from large list - Criteria dependant

[ExcelRoy]

Hi all,

I am trying to return data on particular jobs from a large list on another sheet in the workbook

If the user enters a job no. into G5 then I would like data returned relevant to that job only

I have the following

C7;C10000 is the Date Entered
D7:D10000 is the unique employee no.
E7:E10000 is the employee name
F7:F10000 is the job no. worked on
G7:G10000 has job information
H7:H10000 has the hours worked

I was hoping to use something similar to that from below but not sure if this can be done?


=IF(H7="","",SUMIFS('TimesEntry'!H$7:H$15898,'TimesEntry'!D$7:D$15898,H7,'TimesEntry'!G$7:G$15898,G5,'TimesEntry'!$C$7:$C$15898,">="&$AD$7,'TimesEntry'!$C$7:$C$15898,"<="&IF($AD$8="",EOMONTH($AD$7,0),$AD$8)))

Many thanks for any help

 

[Aladin Akyurek]

Hi Aladin,

Many thanks, but this returns "" to all from column G

Thanks

Did you apply control+shift+enter?

 

[ExcelRoy]

Hi Aladin,

Yes I did apply crl-shft-enter

Thanks

 

[Aladin Akyurek]

Hi Aladin,

Yes I did apply crl-shft-enter

Thanks

See:
https://dl.dropboxusercontent.com/u/65698317/ExcelRoy%20Return%20selected%20data%20from%20large%20list%20-%20Criteria%20dependant.xlsx

 

[ExcelRoy]

Thanks Aladin,

yes everthing appears to be working in your example

I can use your other solution but it is just bugging me why I cannot get this working in another column

I have moved into a different cell, but the formula is trying to return from column G in TimesEntry which is a code of alpha-numeric characters

=IFERROR(1/(1/INDEX('TimesEntry'!G$7:G$15898,SMALL(IF('TimesEntry'!$F$7:$F$15898=$E$5,ROW('TimesEntry'!$F$7:$F$15898)-ROW('TimesEntry'!$F$7)+1),ROWS(F$7:F7)))),"")

I just don't understand how it can return the date with no problems but the code is just returning ""

Very strange

Thanks

 

[ExcelRoy]

Hi Aladin,

I have been playing and have found out that this formula returns numbers only. Could it be modified for alpha-numeric instead

Many Thanks

 

[Aladin Akyurek]

Hi Aladin,

I have been playing and have found out that this formula returns numbers only. Could it be modified for alpha-numeric instead

Many Thanks

What is the range from which the results must come?

 

[ExcelRoy]

Hi Aladin,

'TimesEntry'!G$7:G$15898 is the range that I need to return from

Obviously the E5 is the "Job No"

Many Thanks

 

[Aladin Akyurek]

Hi Aladin,

'TimesEntry'!G$7:G$15898 is the range that I need to return from

Obviously the E5 is the "Job No"

Many Thanks

It's hard to pin down your lay-out. It seems to have changed.

To make sure:

1) The range in column F of TimesEntry houses Job No's

2) When the F range equals E5, the value of the corresponding cell in the G range must be picked out.

3) The G range of TimesEntry is text.

The task: Return blank when the corresponding cell in the G range is empty. Correct? If correct, why not apply the custom format suggested earlier:

[=0]"";General

 

[ExcelRoy]

Hi Aladin,

1) correct

2) correct

3) set as General, but the codes will be R99, I22,V1 ec etc

Thanks

 

[Aladin Akyurek]

Hi Aladin,

1) correct

2) correct

3) set as General, but the codes will be R99, I22,V1 ec etc

Thanks

Regarding the last point:

Select the formula cells of column G.
Activate Format Cells.
Choose Custom.
Enter the following in the box for Type:

[=0]"";General