Tech Support Forum banner
Status
Not open for further replies.
1 - 10 of 10 Posts

·
Registered
Joined
·
20 Posts
Discussion Starter · #1 ·
Good Evening TSF

i have been racking my brains about this one for most of the day...

i have spreadsheet that contains a multitude of information, that records jobs that are scheduled to be carried out by an engineer on a dialy basis, i have now been asked to provide a weekly earnings sheet for each engineer, the main sheet already calculates their jobs as they go but what i am looking at doing is taking data from one sheet and inputting it onto subequent sheets for each engineer based on their ID and date. i have tried using INDEX MATCH but to no avail, it just chruns out the information for the first occurance of said ID

my columns are

Registration (ID)
Account
Job Number
DOC
Scheduled Date
Cost

and each row is complted based on the columns so if an engineer has 3 jobs a day his registraion would appear 3 times, so on his seperate sheet for said date it would need to display his 3 jobs for that day, along with their account, job number, doc and cost.

the Criteria used would be date and Registration but the solution to this evades me :upset: i know it will be similar to INDEX MATCH formulas but i just cant work it out

someone please help... :sigh:
 

·
Moderator , Microsoft Support, MS Office Pro
Joined
·
2,534 Posts
Hi teshiburu1988,

Take a look at the attached workbook. In it there's a data table, then a small block in which selected data from the larger table are extracted. The only input cell in this table is the one highlighted in yellow. The green cells summarise the formulae used - the additional rows & columns just use copies of the same formulae.

With this approach, you could set up your own worksheet to take whichever of your variables you want to use as the input and extract all the related data. The data table and summary table can be on separate worksheets if you prefer.
 

Attachments

·
Registered
Joined
·
20 Posts
Discussion Starter · #3 ·
Brilliant, Amazing... why can't i get it to work :upset: what have i done wrong?

i have attached my workbook with the layout and fields as i am looking for do you think you could possibly let me know what i have done wrong? i keep gettin #N/A error, the sheets that will be used in this are. GN56LYU and workrecord,

GN56 LYU Is where i need the information to be summarised

thanks for your help so far
 

Attachments

·
Moderator , Microsoft Support, MS Office Pro
Joined
·
2,534 Posts
Hi teshiburu1988,

why can't i get it to work .. what have i done wrong?
Um, 'cause you'd messed up the cell referencing - but I guess you knew that!

Here's your corrected workbook back, plus a bonus formula in A2, so you don't have to input the Scheduled Van ID - the formula picks it up from the worksheet name. Your 'hours' column still produces N/A errors, but that's only because the Y/N data are missing from the 'JobRecord' worksheet.
 

Attachments

·
Registered
Joined
·
2 Posts
hi macropod,
i liked your demo.zip

one issue here. you put the value i will be matching in $j$4, i don't want that. i will use value from another cell all results will start from k4. can you give some cleaner code(since no checking for first row is required here)?

in short i need a same formula from the first row(your first row formula is different than the rest)

thanks
 

·
Moderator , Microsoft Support, MS Office Pro
Joined
·
2,534 Posts
hi Animaz,

Other than simplifying the formula in K5 etc by eliminating the OR test (ie in K5, use '$J5=""' instead of 'OR(K4="",COUNTA(K$4:K4)=COUNTIF($A$1:$A$23,$J$4))'), there's really not much simplification that can be done. That's because the formulae on all rows after row 4 need to work out where to start looking for matches from. The formulae in row 4 are simpler only because they don't need to do that check.
 

·
Registered
Joined
·
1 Posts
macropod, I notice if you was to select different vehicles in between the one names on the work sheet I've a attached the example you will get a #n/a error .
 

Attachments

·
Moderator , Microsoft Support, MS Office Pro
Joined
·
2,534 Posts
Hi dcaba,

Well spotted!

Here's a revised version. A significant issue with this worksheet is that some columns have repeated data. To accommodate that, I've keyed the formula to the 'MTS Job Number' column, for which the data are apparently always unique.
 

Attachments

·
Moderator , Microsoft Support, MS Office Pro
Joined
·
2,534 Posts
Hi folks,

Attached is an update to the Demo workbook, with more robust formulae, plus some comments about the various formulae.
 

Attachments

1 - 10 of 10 Posts
Status
Not open for further replies.
Top