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

 

[svendiamond]

Your formula seems to be adding the sums of columns with text and other things. It sounds like you want to be able a job number into G5 and return the Date/Employee#/EmployeeName/JobInfo/Hours for that particular job number, correct?

If that's the case just have spots for the return values... such as in J1:J5... and return them all separately. So in J1 for Date Entered you would have:

=INDEX('TimesEntry'!$C$7:$C$15898,MATCH($G$5,$F$7:$F$15898,0))

Then for the others you would just put the other columns instead of C

 

[ExcelRoy]

Hi svendiamond,

That's just brilliant, many thanks exactly what I needed

Would there be a way to modify this to return "" if there is a "" being returned as at the moment it would return a "0"

Thanks again

edit: When I drag doen this formula it only returns one entry only and not multiple entries?

 

[svendiamond]

Hi svendiamond,


Would there be a way to modify this to return "" if there is a "" being returned as at the moment it would return a "0"

There are probably better ways but this is what I always do:

=IF(INDEX('TimesEntry'!$C$7:$C$15898,MATCH($G$5,$F$7:$F$15898,0))=0,"",INDEX('TimesEntry'!$C$7:$C$15898,MATCH($G$5,$F$7:$F$15898,0)))


aka "if the answer to my INDEX/MATCH is zero, blank, otherwise, same INDEX/MATCH

edit: When I drag doen this formula it only returns one entry only and not multiple entries?

Change the $G$5 to wherever your lookup values are and remove the $$ so G5

 

[ExcelRoy]

Hi svendiamond,

Many thanks, but I think I may have confused slightly

I am hoping that I enter a job number into G5 and the formulas will pick all assoiated information from the TimesEntry sheet

There will be many lines of different data inputed so I need the formula to return everything from the TimesEntry associated with G5

The layout would be exactly as you have suggested in different columns, but I would like the dates shown in say C8:C208, D8:D208 for the employee etc etc

Hope this is possible

Many thanks for your help

 

[Aladin Akyurek]

Hi svendiamond,

Many thanks, but I think I may have confused slightly

I am hoping that I enter a job number into G5 and the formulas will pick all assoiated information from the TimesEntry sheet

There will be many lines of different data inputed so I need the formula to return everything from the TimesEntry associated with G5

The layout would be exactly as you have suggested in different columns, but I would like the dates shown in say C8:C208, D8:D208 for the employee etc etc

Hope this is possible

Many thanks for your help

Destination sheet other than TimesEntry..

G5 houses a job number of interest.

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

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

 

[ExcelRoy]

Hi Aladin,

Yes indeed the destination sheet is different from TimesEntry

I have entered your suggested formula into C8 and copied across and down, but this just shows nothing at all?

Many thanks for trying to help

 

[Aladin Akyurek]

Hi Aladin,

Yes indeed the destination sheet is different from TimesEntry

I have entered your suggested formula into C8 and copied across and down, but this just shows nothing at all?

Many thanks for trying to help

G5 must have a job number of interest.

Formula must be entered in G6, copied across to H6, then down.

This should give you records from C and D columns of TimesEntry as you requested.

 

[ExcelRoy]

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

 

[ExcelRoy]

Hi Aladin,

I think I may have the answer why it doesn't work. I have 2007 excel and I believe that AGGREGATE only works on 2010

Would there be another option to suit 2007

Many Thanks