cell returning totals of 2 cols+ sending this value to another col

[CAT]

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

 

[Ragdyer]

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.

 

[CAT]

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]

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?

 

[CAT]

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]

Glad you got it all sorted out.