Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Paige
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
William Horton
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Paige
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how do I insert a cell based on its match with an adjacent cell? Gabbon Excel Worksheet Functions 12 January 24th 06 01:28 PM
Linking a cell to another workbook cell based on a variable name Brian Excel Discussion (Misc queries) 6 June 1st 05 11:54 PM
Can I select a worksheet based upon a cell criteria?(for printing) Tim Richards Excel Worksheet Functions 0 March 30th 05 07:03 PM
I want to format a cell based on an adjacent cells value Cumbo Excel Discussion (Misc queries) 1 December 13th 04 12:52 PM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM


All times are GMT +1. The time now is 06:55 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"