[Solved] Comparing values and getting a text string return
Posted: Sun Jul 28, 2013 7:18 pm
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
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