Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
cell returning totals of 2 cols+ sending this value to another col
Hi everyone, hope you can help me with this one as manually, it's a lengthy
process! Using Excell 2007 and though a fairly new user, know my way around: Worksheet contains 6 columns T, U, V, W, X and Y plus another column AG in a small fixed a few columns to the left of the worksheet proper. Col W contains the formula (copied and pasted right down the col): =IF(T8="YES",(U8)*V8," ") returns a + figure Col X contains the formula (copied and pasted right down the col):- =IF(T8="NO",-V8," ") returns a - figure Col Y, in one cell appearing only once at the end of a series of rows, contains the formula: =SUM(W8:W18)+(SUM(X8:X18) returns total of col W+X for that block of rows (8 to 18 for ex). Cols T,U and V are blanks as data is entered and will prompt cols W,X and Y to return a value. Example: Col T Col U Col V Col W Col X Col Y Col AG row 8 blank blank blank blank blank blank 2550 row 9 Yes 30 100 3000 blank blank 500 row 10 No blank 450 blank -450 blank etc. row 11 blank blank blank blank blank blank etc row 18 blank blank blank blank blank 2550 row 19 etc etc 500 What I am doing: copy and paste my formulae in cols W and X; then, manually counting the rows in the block, enter the SUM formula in the box in col Y, at the end of the sequence (which can vary from 4 rows to 25 or more rows); I then make a note of the cell row number and, in the static small recap table a few columns away, enter in, say AG6, = Y18, AG7 = Y25, etc. etc. What I would like: To automate the process to make it simpler an easier to handle; Is there a way that the sum value in both cells, in the Y column and in the AG column, could be returned automatically without having to resort to the manual counting of rows? Thank you all in advance for your help and input |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
cell returning totals of 2 cols+ sending this value to another col
Besides a visual inspection, what determines the size of the block to
calculate? Your example went down to Row 18, even though Row 11 to 18 was empty. Need more info. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "CAT" wrote in message ... Hi everyone, hope you can help me with this one as manually, it's a lengthy process! Using Excell 2007 and though a fairly new user, know my way around: Worksheet contains 6 columns T, U, V, W, X and Y plus another column AG in a small fixed a few columns to the left of the worksheet proper. Col W contains the formula (copied and pasted right down the col): =IF(T8="YES",(U8)*V8," ") returns a + figure Col X contains the formula (copied and pasted right down the col):- =IF(T8="NO",-V8," ") returns a - figure Col Y, in one cell appearing only once at the end of a series of rows, contains the formula: =SUM(W8:W18)+(SUM(X8:X18) returns total of col W+X for that block of rows (8 to 18 for ex). Cols T,U and V are blanks as data is entered and will prompt cols W,X and Y to return a value. Example: Col T Col U Col V Col W Col X Col Y Col AG row 8 blank blank blank blank blank blank 2550 row 9 Yes 30 100 3000 blank blank 500 row 10 No blank 450 blank -450 blank etc. row 11 blank blank blank blank blank blank etc row 18 blank blank blank blank blank 2550 row 19 etc etc 500 What I am doing: copy and paste my formulae in cols W and X; then, manually counting the rows in the block, enter the SUM formula in the box in col Y, at the end of the sequence (which can vary from 4 rows to 25 or more rows); I then make a note of the cell row number and, in the static small recap table a few columns away, enter in, say AG6, = Y18, AG7 = Y25, etc. etc. What I would like: To automate the process to make it simpler an easier to handle; Is there a way that the sum value in both cells, in the Y column and in the AG column, could be returned automatically without having to resort to the manual counting of rows? Thank you all in advance for your help and input |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
cell returning totals of 2 cols+ sending this value to another
Hi Ragdyer,
The "blocks" of data are completely random: previously downloaded data sorted chronologically before copying into the workbook, which may contain, as stated, from 4 to 25 rows, and that's where lies the problem: I don't know in advance how many rows there will be in each block, hence the manual counting to set my "total" cells in column Y. Thanks for the interest Regards CAT "Ragdyer" wrote: Besides a visual inspection, what determines the size of the block to calculate? Your example went down to Row 18, even though Row 11 to 18 was empty. Need more info. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "CAT" wrote in message ... Hi everyone, hope you can help me with this one as manually, it's a lengthy process! Using Excell 2007 and though a fairly new user, know my way around: Worksheet contains 6 columns T, U, V, W, X and Y plus another column AG in a small fixed a few columns to the left of the worksheet proper. Col W contains the formula (copied and pasted right down the col): =IF(T8="YES",(U8)*V8," ") returns a + figure Col X contains the formula (copied and pasted right down the col):- =IF(T8="NO",-V8," ") returns a - figure Col Y, in one cell appearing only once at the end of a series of rows, contains the formula: =SUM(W8:W18)+(SUM(X8:X18) returns total of col W+X for that block of rows (8 to 18 for ex). Cols T,U and V are blanks as data is entered and will prompt cols W,X and Y to return a value. Example: Col T Col U Col V Col W Col X Col Y Col AG row 8 blank blank blank blank blank blank 2550 row 9 Yes 30 100 3000 blank blank 500 row 10 No blank 450 blank -450 blank etc. row 11 blank blank blank blank blank blank etc row 18 blank blank blank blank blank 2550 row 19 etc etc 500 What I am doing: copy and paste my formulae in cols W and X; then, manually counting the rows in the block, enter the SUM formula in the box in col Y, at the end of the sequence (which can vary from 4 rows to 25 or more rows); I then make a note of the cell row number and, in the static small recap table a few columns away, enter in, say AG6, = Y18, AG7 = Y25, etc. etc. What I would like: To automate the process to make it simpler an easier to handle; Is there a way that the sum value in both cells, in the Y column and in the AG column, could be returned automatically without having to resort to the manual counting of rows? Thank you all in advance for your help and input |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
cell returning totals of 2 cols+ sending this value to another
Is this your scenario:
You import data into Columns T, U, and V. The amount of data is variable, anywhere from 4 to 25 rows. You have pre-existing formulas in Columns W and X to perform calculations on this imported data. Questions - Do the various import sessions remain in the datalist, or is the data from one session deleted before another import is completed? If multiple imports are retained in the datalist, what separates each session? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "CAT" wrote in message ... Hi Ragdyer, The "blocks" of data are completely random: previously downloaded data sorted chronologically before copying into the workbook, which may contain, as stated, from 4 to 25 rows, and that's where lies the problem: I don't know in advance how many rows there will be in each block, hence the manual counting to set my "total" cells in column Y. Thanks for the interest Regards CAT "Ragdyer" wrote: Besides a visual inspection, what determines the size of the block to calculate? Your example went down to Row 18, even though Row 11 to 18 was empty. Need more info. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "CAT" wrote in message ... Hi everyone, hope you can help me with this one as manually, it's a lengthy process! Using Excell 2007 and though a fairly new user, know my way around: Worksheet contains 6 columns T, U, V, W, X and Y plus another column AG in a small fixed a few columns to the left of the worksheet proper. Col W contains the formula (copied and pasted right down the col): =IF(T8="YES",(U8)*V8," ") returns a + figure Col X contains the formula (copied and pasted right down the col):- =IF(T8="NO",-V8," ") returns a - figure Col Y, in one cell appearing only once at the end of a series of rows, contains the formula: =SUM(W8:W18)+(SUM(X8:X18) returns total of col W+X for that block of rows (8 to 18 for ex). Cols T,U and V are blanks as data is entered and will prompt cols W,X and Y to return a value. Example: Col T Col U Col V Col W Col X Col Y Col AG row 8 blank blank blank blank blank blank 2550 row 9 Yes 30 100 3000 blank blank 500 row 10 No blank 450 blank -450 blank etc. row 11 blank blank blank blank blank blank etc row 18 blank blank blank blank blank 2550 row 19 etc etc 500 What I am doing: copy and paste my formulae in cols W and X; then, manually counting the rows in the block, enter the SUM formula in the box in col Y, at the end of the sequence (which can vary from 4 rows to 25 or more rows); I then make a note of the cell row number and, in the static small recap table a few columns away, enter in, say AG6, = Y18, AG7 = Y25, etc. etc. What I would like: To automate the process to make it simpler an easier to handle; Is there a way that the sum value in both cells, in the Y column and in the AG column, could be returned automatically without having to resort to the manual counting of rows? Thank you all in advance for your help and input |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
cell returning totals of 2 cols+ sending this value to another
Hi Ragdyer,
Thank you very much for your interest; I realise I wasn't very clear or precise with my explanations - you'll have to forgive me as I am pretty much a newbie - but I have been given a solution to both of my queries and they do work, so I'm happy. The imported data is sorted on a separate worksheet and then imported back into my main worksheet to work with; Starting with column B containing only 1 data entry per block, which is the beginning of the sequence, the rest of the data is spread over cols C, D, E and F and remain static; Col G to S have some data entered into them at a later date but I still have some work to do on them, re conditional formatting etc. Cols T, U and V are where I enter the conditions and data which will be reflected in returned values in cols W and X, and in col Y are the totals of each sequence! I know it's as clear as mud but here goes by way of example: Col B Col C Col D Col E Col F .....Col W Col X Col Y Row 7 1.30A 1 text text text +80 Row 8 2 text text text -20 Row 9 3 text text text Row 10 4 text text text +60 Row 11 2.15C 1 text text text Row 12 2 text text text ETC. The "total" cell in col Y is always one row above the new entry in col B (which starts a new sequence). I have been given a couple of functions to enter in col Y which return the totals of cols W and X in exactly the right spot and another set of functions which copy those same values neatly bunched up in a small static check-up table. I apologise for that but not having had any reply I reworded my queries in the Function Section. I now have to tackle my conditional formatting which I fear will be a VBA job and I'm a total ignoramus on the subject but willing to learn! So, it's not over yet! but I am grateful to everyone for all the help I have been given. And thanking you too. Kind Regards "RagDyer" wrote: Is this your scenario: You import data into Columns T, U, and V. The amount of data is variable, anywhere from 4 to 25 rows. You have pre-existing formulas in Columns W and X to perform calculations on this imported data. Questions - Do the various import sessions remain in the datalist, or is the data from one session deleted before another import is completed? If multiple imports are retained in the datalist, what separates each session? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "CAT" wrote in message ... Hi Ragdyer, The "blocks" of data are completely random: previously downloaded data sorted chronologically before copying into the workbook, which may contain, as stated, from 4 to 25 rows, and that's where lies the problem: I don't know in advance how many rows there will be in each block, hence the manual counting to set my "total" cells in column Y. Thanks for the interest Regards CAT "Ragdyer" wrote: Besides a visual inspection, what determines the size of the block to calculate? Your example went down to Row 18, even though Row 11 to 18 was empty. Need more info. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "CAT" wrote in message ... Hi everyone, hope you can help me with this one as manually, it's a lengthy process! Using Excell 2007 and though a fairly new user, know my way around: Worksheet contains 6 columns T, U, V, W, X and Y plus another column AG in a small fixed a few columns to the left of the worksheet proper. Col W contains the formula (copied and pasted right down the col): =IF(T8="YES",(U8)*V8," ") returns a + figure Col X contains the formula (copied and pasted right down the col):- =IF(T8="NO",-V8," ") returns a - figure Col Y, in one cell appearing only once at the end of a series of rows, contains the formula: =SUM(W8:W18)+(SUM(X8:X18) returns total of col W+X for that block of rows (8 to 18 for ex). Cols T,U and V are blanks as data is entered and will prompt cols W,X and Y to return a value. Example: Col T Col U Col V Col W Col X Col Y Col AG row 8 blank blank blank blank blank blank 2550 row 9 Yes 30 100 3000 blank blank 500 row 10 No blank 450 blank -450 blank etc. row 11 blank blank blank blank blank blank etc row 18 blank blank blank blank blank 2550 row 19 etc etc 500 What I am doing: copy and paste my formulae in cols W and X; then, manually counting the rows in the block, enter the SUM formula in the box in col Y, at the end of the sequence (which can vary from 4 rows to 25 or more rows); I then make a note of the cell row number and, in the static small recap table a few columns away, enter in, say AG6, = Y18, AG7 = Y25, etc. etc. What I would like: To automate the process to make it simpler an easier to handle; Is there a way that the sum value in both cells, in the Y column and in the AG column, could be returned automatically without having to resort to the manual counting of rows? Thank you all in advance for your help and input |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
cell returning totals of 2 cols+ sending this value to another
Glad you got it all sorted out.
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "CAT" wrote in message ... Hi Ragdyer, Thank you very much for your interest; I realise I wasn't very clear or precise with my explanations - you'll have to forgive me as I am pretty much a newbie - but I have been given a solution to both of my queries and they do work, so I'm happy. The imported data is sorted on a separate worksheet and then imported back into my main worksheet to work with; Starting with column B containing only 1 data entry per block, which is the beginning of the sequence, the rest of the data is spread over cols C, D, E and F and remain static; Col G to S have some data entered into them at a later date but I still have some work to do on them, re conditional formatting etc. Cols T, U and V are where I enter the conditions and data which will be reflected in returned values in cols W and X, and in col Y are the totals of each sequence! I know it's as clear as mud but here goes by way of example: Col B Col C Col D Col E Col F .....Col W Col X Col Y Row 7 1.30A 1 text text text +80 Row 8 2 text text -20 Row 9 3 text text text Row 10 4 text text text +60 Row 11 2.15C 1 text text text Row 12 2 text text text ETC. The "total" cell in col Y is always one row above the new entry in col B (which starts a new sequence). I have been given a couple of functions to enter in col Y which return the totals of cols W and X in exactly the right spot and another set of functions which copy those same values neatly bunched up in a small static check-up table. I apologise for that but not having had any reply I reworded my queries in the Function Section. I now have to tackle my conditional formatting which I fear will be a VBA job and I'm a total ignoramus on the subject but willing to learn! So, it's not over yet! but I am grateful to everyone for all the help I have been given. And thanking you too. Kind Regards "RagDyer" wrote: Is this your scenario: You import data into Columns T, U, and V. The amount of data is variable, anywhere from 4 to 25 rows. You have pre-existing formulas in Columns W and X to perform calculations on this imported data. Questions - Do the various import sessions remain in the datalist, or is the data from one session deleted before another import is completed? If multiple imports are retained in the datalist, what separates each session? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "CAT" wrote in message ... Hi Ragdyer, The "blocks" of data are completely random: previously downloaded data sorted chronologically before copying into the workbook, which may contain, as stated, from 4 to 25 rows, and that's where lies the problem: I don't know in advance how many rows there will be in each block, hence the manual counting to set my "total" cells in column Y. Thanks for the interest Regards CAT "Ragdyer" wrote: Besides a visual inspection, what determines the size of the block to calculate? Your example went down to Row 18, even though Row 11 to 18 was empty. Need more info. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "CAT" wrote in message ... Hi everyone, hope you can help me with this one as manually, it's a lengthy process! Using Excell 2007 and though a fairly new user, know my way around: Worksheet contains 6 columns T, U, V, W, X and Y plus another column AG in a small fixed a few columns to the left of the worksheet proper. Col W contains the formula (copied and pasted right down the col): =IF(T8="YES",(U8)*V8," ") returns a + figure Col X contains the formula (copied and pasted right down the col):- =IF(T8="NO",-V8," ") returns a - figure Col Y, in one cell appearing only once at the end of a series of rows, contains the formula: =SUM(W8:W18)+(SUM(X8:X18) returns total of col W+X for that block of rows (8 to 18 for ex). Cols T,U and V are blanks as data is entered and will prompt cols W,X and Y to return a value. Example: Col T Col U Col V Col W Col X Col Y Col AG row 8 blank blank blank blank blank blank 2550 row 9 Yes 30 100 3000 blank blank 500 row 10 No blank 450 blank -450 blank etc. row 11 blank blank blank blank blank blank etc row 18 blank blank blank blank blank 2550 row 19 etc etc 500 What I am doing: copy and paste my formulae in cols W and X; then, manually counting the rows in the block, enter the SUM formula in the box in col Y, at the end of the sequence (which can vary from 4 rows to 25 or more rows); I then make a note of the cell row number and, in the static small recap table a few columns away, enter in, say AG6, = Y18, AG7 = Y25, etc. etc. What I would like: To automate the process to make it simpler an easier to handle; Is there a way that the sum value in both cells, in the Y column and in the AG column, could be returned automatically without having to resort to the manual counting of rows? Thank you all in advance for your help and input |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2 Cols To 2 Cols VLookup Comparison | Excel Discussion (Misc queries) | |||
Pivot needs BOTH cur/cum totals w/out extra cum cols/repeat lines | Excel Worksheet Functions | |||
conditional formating on rows & cols depending on one cell | Excel Discussion (Misc queries) | |||
Sending data to cell | Excel Discussion (Misc queries) | |||
Cond Format:re color 2 cols, skip 2 cols | Excel Worksheet Functions |