Sum below until blank cell

**TheNxSyS** · 05-26-2009, 01:34 PM

Hi all,

UPDATE: I've come to the conclusion that this must be done using VBA...

There are 2 main probles to solve.

The first program should fill in the coloured cell's per sort.
First it should look for non-blanc cell's in colum A.
Then it shoud sum up the totals between the different sorts of goods.
Then it should look for the next-blanc cell in colum A and do the same.

Basilcally I need a sum function that will add SUM(X:Y-1).
Where X is the row the Xth non-blanc cell is and Y is the X+1th row with a non-blanc cell.

A second program should run after the first one and should copy the coloured cell's to a result page at the end.
First it should look for non-blanc cell's in colum A.
Then copy the name of the product and the coloured cell's per artikel to an result page.

Can anybody help me with that ???

Thanks

**JBeaucaire** · 05-26-2009, 01:45 PM

Welcome to the forum...

Be sure to click on the HOW TO... in the menu bar above and read through the forum rules. The most important is probably posting your question in the right "forum". This is the programming forum, so the answers you get here will be VBA, generally.

Hopefully a moderator will move your post, or you can close this thread and post it in the correct forum, Excel General.

Also, your jpg isn't legible. Post a sample of your workbook so people seeking to help don't have to manually mockup books to test their answers for you. In the sample workbook, be sure to include "desired results" samples and point them out so we can see the goal line clearly.

**TheNxSyS** · 05-27-2009, 03:00 AM

-comment removed-

**TheNxSyS** · 05-29-2009, 11:42 AM

Hi all,

First of all, if possible, I'm looking for an Excel-solution, not VBA!

I am trying to create a SUM function that will add SUM(X:Y).
Where X is the cell !!! below !!! the cell that contains the SUM solution.
And Y is the first blank cell in the column (or the cell above the blanc).
The X address is easy, but the Y address always various depending.

I've included an extract of the file I'm trying to modify.
You'll see that the stocks depend per sort and I want to return with the sum in each coloured cell.

The main purpose of these formulas is to be able to do a v.lookup per page for each not-blanc cell in colum A and to retreive the coloured cell's on a specific 'result'-sheet at the end of the workbook.

Current problems are for example the fact that people don't update the sums regulary after the inserted some new data. + all the results on the result-sheet are fixed locations so something can go wrong very easy.

Many thanks

**DonkeyOte** · 05-29-2009, 11:51 AM

You can try using INDEX - for ex.:

F3: =SUM($F4:INDEX(F4:$F$1000,MATCH(TRUE,INDEX($F4:$F$1000="",0),0)))

the above can be applied to all total cells.

try to avoid OFFSET function given Volatile.

**TheNxSyS** · 05-29-2009, 11:58 AM

I've updated the opening-post.
Because it seems it can't be done without VBA programming.

**DonkeyOte** · 05-29-2009, 12:00 PM

I have merged your two threads on this... please don't post dupes.

I've provided a non VBA formula for you (post #5) ... if this isn't acceptable outline why and the thread can progress.

**mewingkitty** · 05-29-2009, 12:03 PM

lol
Apparently doing ten things at once leaves me a little behind on the posts :P

**DonkeyOte** · 05-29-2009, 12:13 PM

I should have added the VBA I guess too given I've no idea if you want VBA or not ??

Code:

Public Sub InsertSubTotals()
With Range(Cells(3, "A"), Cells(Rows.Count, "A").End(xlUp)).SpecialCells(xlCellTypeConstants, xlTextValues).Offset(, 5)
    .FormulaR1C1 = "=SUM(R[1]C6:INDEX(R[1]C6:R1000C6,MATCH(TRUE,INDEX(R[1]C6:R1000C6="""",0),0)))"
End With
End Sub

the above would ins*ert subtotal formulae into every cell in F for which there is a description in column A

**TheNxSyS** · 05-30-2009, 04:11 AM

Hey all,

Thanks for all the answers so far.
As it is such a good weather today in Belgium I'm gonna enjoy the day.
I'll go back to the office on Sunday or Monday and try the proposed solutions.

Many thanks!

**TheNxSyS** · 06-01-2009, 04:36 AM

Hey all,

Still sunny on whit Monday, but work has to be done as I want the whole stock of the firm updated by the end of June so need to get going...
First up is automating the stock-excel file and making it monkey-proof, you wouldn’t believe how all the excel-files are structured over here. (All with fixed cells to be copied to difference files no wonder that half of them are no longer accurate)

I'm beginning with the AIL-tab as you can see in the file; off course in the real file we have over 100 tabs (each for a specific family of primary material).
This is why I'm counting on your help as my knowledge of excel-formulas is rather limited, or so it seems being amongst such an excel-experts.

First of all: thanks DonkeyOte for the index-function.
It works like a charm like you can see in the attached file.

However I'm currently struggling with a v.lookup in 'samenvatting'-tab (Dutch for summery).
I can't figure out the formula that will take the values of columns I, J, M & N of each row were column A is not empty. So in reality taking the values of each subtotal line per sort of goods.

I think VBA will be necessary and that is why I was thinking of using VBA for the subtotals as well (instead of the index-formula). This way I can make an 'update-button' that will update all subtotals and make the summery-list that I intend to.

(I need the summery to be able to check if the excel and the ERP-system are well aligned.)

That is why all ideas and help are welcome.

Best regards.

**DonkeyOte** · 06-01-2009, 04:45 AM

Re: VBA for subtotals - this you have already I believe...?

Re: Summary question, I have questions for you...

a) presumably you want to return contents of A in addition to I, J, M & N ?

b) are you able to add any formulae to AIL sheet ?
(ie use a helper column in P ... this way you can avoid expensive array formulae)

You can do this (summary) with either formulae or VBA - which do you prefer ?
In the long run VBA is more efficient but at the same time far less audit-able, trickier to adapt and depending on how it is implemented could be deemed open to error (ie if source changes you need to ensure Summary changes too) , these points are exacerbated if the users are not VBA savvy...

**TheNxSyS** · 06-01-2009, 06:02 AM

Hello DonkeyOte

Regarding the subtotals, you are correct. I have this already.

Regarding the summary:
A) Yes I want the specific name of the goods with the m², kg, pricing (€/kg) and total value on the summery tab at the end of the workbook.
B) Because I'm the main user of this file together with our bookkeeper we can do whatever we want in the file.

I prefer the VBA-because it's "clean".
However, I've only just bought a VBA-manual on Saturday.
I've never seen VBA at university, but I've seen other "languages".
So I think, with some self-education at night I can become acquainted with VBA.

If you can achieve the same result with formulas it's okay for me.
But as this summary will include all tabs (each family having its own number of specific codes, AIL i.e. 10 types of goods, another family having 3 types and yet another family having 25 types of goods) I think it will be difficult to make a nice summery by formula.
Meaning

AIL-family
- AIL Mxxxxxx1 ---> Results in columns B, C, D and E
- AIL Mxxxxxx2 ---> Results in columns B, C, D and E
.....
- AIL Mxxxxx10 ---> Results in columns B, C, D and E

BTC-family
-BTC Mxxxxxx1 ---> Results in columns B, C, D and E
-BTC Mxxxxxx2 ---> Results in columns B, C, D and E
-BTC Mxxxxxx3 ---> Results in columns B, C, D and E
-BTC Mxxxxxx4 ---> Results in columns B, C, D and E
-BTC Mxxxxxx5 ---> Results in columns B, C, D and E

BTX-family
-BTX Mxxxxxx1 ---> Results in columns B, C, D and E

COT-family
-COT Mxxxxxx1 ---> Results in columns B, C, D and E
-COT Mxxxxxx2 ---> Results in columns B, C, D and E
...
-COT Mxxxxx35 ---> Results in columns B, C, D and E

etc.

**DonkeyOte** · 06-01-2009, 06:05 AM

I agree that VBA is preferable if you intend to collate multiple sheets in one contiguous range, using formulae would invariably necessitate INDIRECT thereby making the calcs Volatile, in a large model Volatile functions should be avoided wherever possible.

It would I think be better to provide a sample file with multiple source sheets such that we can validate all is working correctly with sheet iteration etc...

**TheNxSyS** · 06-01-2009, 06:13 AM

Thanks,

I'll post another template later today with the following two family's.

Thread: Sum below until blank cell

Thread Tools

Display

Sum below until blank cell

re: Sum below until blank cell

re: Sum below until blank cell

Sum below until blanc cell

re: Sum below until blank cell

re: Sum below until blank cell

re: Sum below until blank cell

re: Sum below until blank cell

re: Sum below until blank cell

Re: Sum below until blank cell

Re: Sum below until blank cell

Re: Sum below until blank cell

Re: Sum below until blank cell

Re: Sum below until blank cell

Re: Sum below until blank cell

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions