Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
select cell value based on value in adjacent cell
I'm creating a timesheet and running into some trouble calculating total time
usage. Here's the setup: I have a column P in which conditional data is entered (e.g., if a person used Holiday accrual time, they enter code "60"; this field may or may not be populated, and may use one of 3-4 different codes). The cells in this column are not contiguous, but broken up by merged cells. The next coloumn, Q, holds the actual hours of accruals used (general formatting). The cells in this column are not contiguous, but broken up by merged cells, matching column P. Way down at the bottom of the timesheet, I need to calculate the number of hours of accruals used based on what code is entered in column P. So here's what I need to figure out how to calculate: If any cell in column P (P4-43, skipping the merged cells) holds the code "60", then find the corresponding value in the adjacent cell in column Q, then sum the values in the cells that meet the criteria and display it in cell N49. I don't know if the actual cell references help you or not, but I'm hoping they will. I'm frustrated no end. I know this can be done, in theory, because it was done in another application - Word Perfect (more likely dropped in from Quattro Pro). I just don't know if it can be done in Excel, or how. The table in Word Perfect is protected and I can't open to see the formulas and references that were used, and the person who created it is long gone from our office. I hope someone can help! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
select cell value based on value in adjacent cell
Put the following formula in cell N49.
=SUMIF(P4:P43,60,Q4:Q43) Hope this helps. Bill Horton "Paige" wrote: I'm creating a timesheet and running into some trouble calculating total time usage. Here's the setup: I have a column P in which conditional data is entered (e.g., if a person used Holiday accrual time, they enter code "60"; this field may or may not be populated, and may use one of 3-4 different codes). The cells in this column are not contiguous, but broken up by merged cells. The next coloumn, Q, holds the actual hours of accruals used (general formatting). The cells in this column are not contiguous, but broken up by merged cells, matching column P. Way down at the bottom of the timesheet, I need to calculate the number of hours of accruals used based on what code is entered in column P. So here's what I need to figure out how to calculate: If any cell in column P (P4-43, skipping the merged cells) holds the code "60", then find the corresponding value in the adjacent cell in column Q, then sum the values in the cells that meet the criteria and display it in cell N49. I don't know if the actual cell references help you or not, but I'm hoping they will. I'm frustrated no end. I know this can be done, in theory, because it was done in another application - Word Perfect (more likely dropped in from Quattro Pro). I just don't know if it can be done in Excel, or how. The table in Word Perfect is protected and I can't open to see the formulas and references that were used, and the person who created it is long gone from our office. I hope someone can help! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
select cell value based on value in adjacent cell
Good grief - Thanks! I was experimenting with SUMIF, but I'd apparently
constructed it wrong. Whew! "William Horton" wrote: Put the following formula in cell N49. =SUMIF(P4:P43,60,Q4:Q43) Hope this helps. Bill Horton "Paige" wrote: I'm creating a timesheet and running into some trouble calculating total time usage. Here's the setup: I have a column P in which conditional data is entered (e.g., if a person used Holiday accrual time, they enter code "60"; this field may or may not be populated, and may use one of 3-4 different codes). The cells in this column are not contiguous, but broken up by merged cells. The next coloumn, Q, holds the actual hours of accruals used (general formatting). The cells in this column are not contiguous, but broken up by merged cells, matching column P. Way down at the bottom of the timesheet, I need to calculate the number of hours of accruals used based on what code is entered in column P. So here's what I need to figure out how to calculate: If any cell in column P (P4-43, skipping the merged cells) holds the code "60", then find the corresponding value in the adjacent cell in column Q, then sum the values in the cells that meet the criteria and display it in cell N49. I don't know if the actual cell references help you or not, but I'm hoping they will. I'm frustrated no end. I know this can be done, in theory, because it was done in another application - Word Perfect (more likely dropped in from Quattro Pro). I just don't know if it can be done in Excel, or how. The table in Word Perfect is protected and I can't open to see the formulas and references that were used, and the person who created it is long gone from our office. I hope someone can help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I insert a cell based on its match with an adjacent cell? | Excel Worksheet Functions | |||
Linking a cell to another workbook cell based on a variable name | Excel Discussion (Misc queries) | |||
Can I select a worksheet based upon a cell criteria?(for printing) | Excel Worksheet Functions | |||
I want to format a cell based on an adjacent cells value | Excel Discussion (Misc queries) | |||
GET.CELL | Excel Worksheet Functions |