[Solved] Comparing values and getting a text string return

[Alan M]

SUBJECT: Comparing values and getting a text string return.
Using: open office 3.3.0, on laptop, Windows 7 Starter

Hi to all on the forum, I am very new to OOo and spreadsheets for that matter, and will appreciate any advice to my problem's.

I have two sheets of which I import the data from the job management system using CSV into the data entry sheet, this spreadsheet is largely based on time comparisons, total time worked and amount of jobs completed with reasons for none completion.
1. job card data entry sheet
2. job card report sheet

Problem 1 (see attached screen shots below)
On the job card data entry sheet I have 13 cells (in a row, per job card) that represent the total
possible delays types and delay hours for this job card, (I have 100 job card entries)

Theft - Material - Test room - Meetings - Leave - Transport - Weather - .......etc
N23, O23, P23, Q23, R23, S23, T23, ..........Z23

On the job card report sheet I have 100 ( job work order numbers) cells (in a column)
representing the reason for job card not completed.

J9, J10, J11, J12, J13, J14, J15, ..........J108

These cells must decide which delay has the highest amount of hours per job card and fill in the string text (delay name)

I have tried formulas like =IF(sheet1.N23>sheet1.O23;"THEFT";"MATERIAL")

But that only works to compare the two cells, I am pretty sure that I have to nest the (IF) or (OR) statements, but cannot fathom it out, I also have a cell in each job card entry, which is formatted to numbers (job completed 1 or 0) in Column and counts the total job cards completed over all 100 job cards, if this is 1 it means that the job is completed and no reason for non completion need to be filled into the ( J ) column cells, even though the delays still need to be recorded for statistics.

Problem 2
I have used copy - special paste - transpose - link, with some of the other formulas that helps copy the same formula over 100 job card entries, please also give a solution to copy formula for the above problem as the cells (in a row) for the delay types and hours are on varying distances apart and cannot drag the formula down and have I think what they called absolute reference.

Problem 3
In the attached screen shots below in the Data Entry Sheet referring to cells A9 and B9, for me to find the job card number and enter the data needed, I want to enter the number and have the cursor take me to the position of the work order number entered, I have tried to use the “VLOOKUP” and as can be seen on the screen shoot with the formula added above A9, the “MATCH” function, what happened here is that the formula “=MATCH (B9;A18:A1716;1)” returned only the number of lines down but did not take the cursor there, I even tried using the FIND & REPLACE in the edit menu, I think this did not work because when I export the work order numbers and estimated time from the job management system in CSV and then open that data in a linked .ODS file, the cells to be found refer to the location in of the linked file as can be seen in the DATA ENTRY SHEET screen shot in the formula bar, am I correct in surmising this, if so a solution please.

I must say though I am enjoying learning about spreadsheets and look forward to hearing from anyone who can help and thank you all in advance.
Alan M

[squenson]

OK, let's try to tackle the problems one by one!

Problem 1
My understanding is that you want, for each row starting from 9 to 108, the maximum number on each row and then to return the header of this column. I propose:
=INDEX($N$8:$Z$8,MATCH(MAX(N9:Z9),N9:Z9,0))

Problem 2
There is no simple way to simulate such "super-intelligent" copy. My suggestion would be to standardize the format of the job cards, eventually by inserting hidden columns to have the columns you need always at the same place.

Problem 3
Next to the cell where you enter the job card number, you can have a cell with a variable hyperlink that points to the right cell; you will just have to click on that cell and you will be "warped" to the right job card. Here is an example that goes to the cell Fnnn where nnn is the value entered in the cell F9: =HYPERLINK("#$Sheet1.$F$"&F9,"Go!")

I attach a spreadsheet with the formulas, so you can play with them.

[Alan M]

HI squenson, thanks a lot I am on the way to solving problem 1. works very well, I am not to sure about in the example that you proposed N8 to Z8 and N9 to Z9, but you did put me on the right track thanks very much I had to add a IF function to make the J cell blank if the job card was complete can you check it, it appears to work but if no time have been entered into the delay columns and the completed cell does not have a 1 (that is cell C18) it seems to default to the first INDEX heading, but thanks a lot bru I will start working on problem 3 as it appears that problem 2 has no solution, (note I did not relay that on the data entry sheet the job cards are entered on a scrolling sheet with the headings above the job card are frozen, the formula that gave me with the IF function to default J colum to 0 is =IF(D9>0;0;INDEX('Data Entry'.$M$8:$Z$8;MATCH(MAX('Data Entry'.M23:Z23);'Data Entry'.M23:Z23;0))), I am entering this formula into the J coloum on the REPORT SHEET, does it look correct to you.
Thanks
Alan M

[squenson]

It looks correct to me. The best way to validate a formula is to test it with different values in the cells used in the formula, so you can change the values in D9 or the range M23:Z23 to see the result.