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,

sorry but the formula doesn't seem to work

I have done exactly how you have said but can not get it to work
I have tried it s an array formula and still nothing

I have noticed that when I drag from G6 to H6 the G5 turns to H5 which should'nt be the case, this should always stay as G5 that said I have tried with G5 alo and still nothing

Thanks

Misplaced paren...

G5 is a job number.

G6, just enter, copy to H6, and down:

=IFERROR(INDEX(TimesEntry!C$7:C$20, AGGREGATE(15,6,(ROW(TimesEntry!$F$7:$F$20)-ROW(TimesEntry!$F$7)+1)/(TimesEntry!$F$7:$F$20=$G$5),ROWS(G$6:G6))),"")

Adjust to suit.

 

[ExcelRoy]

hi Aladin,

Does this formula work on Excel 2007?

Thanks

 

[Aladin Akyurek]

hi Aladin,

Does this formula work on Excel 2007?

Thanks

If you get a #NAME? error, it won't.

Try instead...

G6, control+shift+enter, not just enter, copy across to H6, and copy down:

=IFERROR(INDEX(TimesEntry!C$7:C$20,SMALL(IF(TimesEntry!$F$7:$F$20=$G$5,
  ROW(TimesEntry!$F$7:$F$20)-ROW(TimesEntry!$F$7)+1),ROWS(G$6:G6))),"")

 

[ExcelRoy]

Simply put, Perfect results

Many thanks for your time and patience

Have a great Christmas & new year!

 

[Aladin Akyurek]

Simply put, Perfect results

Many thanks for your time and patience

Have a great Christmas & new year!

You are welcome. Happy festive days...

 

[ExcelRoy]

Hi Again Aladin,

Sorry, but could I ask one more question

If the formula returns a "0" could this return a "" instead? Is this possible

Thanks

 

[Aladin Akyurek]

Hi Again Aladin,

Sorry, but could I ask one more question

If the formula returns a "0" could this return a "" instead? Is this possible

Thanks

Format the formula cells as:

[=0]"";General

If this not satisfy, where would you get 0 - in G or in H?

 

[ExcelRoy]

Hi Aladin,

it would be in column G

Thanks

 

[Aladin Akyurek]

Hi Aladin,

it would be in column G

Thanks

G6, control+shift+enter and copy down:

=IFERROR(1/(1/INDEX(TimesEntry!C$7:C$20,SMALL(IF(TimesEntry!$F$7:$F$20=$G$5,
  ROW(TimesEntry!$F$7:$F$20)-ROW(TimesEntry!$F$7)+1),ROWS(G$6:G6)))),"")

 

[ExcelRoy]

Hi Aladin,

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

Thanks