Can you insert sub-total cells which vary controlled from dropbox

[BIGTREV]

I need to insert various sub-totals in a column which sums the cells immediately above it but want to do this from a dropbox (or other simple method) which is in a remote column/cell.

i.e., the attached shows various sub-totals in the same column which sum the cells immediately above but only within the sub-section it belongs to. You will note that each section has a different number of rows that it has to sum.

To overwrite each cell to carry out the function is obviously very time consuming which is why I need an automatic solution.

I would like to be able to use a dropbox that would sit in another column which would automatically tell the relevant cell to change state and sum the cells between it and the next sub-total directly above and in the same column.

If I the revert the dropbox back again, I want the cell containing the sub-total to reset back to its original state.

Can this be done?

Regards.

[protonLeah]

We only help on certain problems not to create a worksheet for you..
You should start first then if you encounter a problem on the worksheet it's the the time to ask a question to fellow members.

Why did you post a pdf file instead of a workbook??

[BIGTREV]

Hi,

As you will see from the attached workbook it is advanced in its construction and I can assure you I am not trying to get anyone to build it for me. I am in simple need of this function and put it on the forum that way because I wanted to simplify my request so that it was not too onerous.

The reason I uploaded a PDF was simply to show the result I want and as PDF's are listed in the forms of file to be uploaded I did not see any problem with that. However, I apologise if that seems to have caused any confusion. You will now also see that I had simplified the PDF to what columns/rows/cells were actually being used.

As stated in my original thread, I am wanting the sub-total to come through without having to edit each cell to force the sum to appear which can be quite time consuming if you have numerous sub-sections to contend with, I have only shown 3 sub-sections which I named as 'rooms' for simple comparrison, of course in reality there will be a great many more diverse sub-sections which are not just simple rooms, this also means I have to change the total of the worksheet at cell AC13. It will I am sure be realised that where I have listed some elements in the worksheet these are greatly simplified from the detail which is actually input and each section list when they are used can have many more rows than just 4 or 5 as per this sample so the sub-total has to have the ability to automatically expand or contract by various different amounts to the number of cells it is being asked to calculate without taking into account any other sub-total cell which could be above it and that is where my problems lay.

Each cell in Col AC has the calc =IF(AND(E37<>"",D37="ITEM"),AA37*AB37,"") which then calculates the total for that row, I want to have a control cell which changes the cell to SUM its own sub-section but then if the control cell is reverted back to its original state to allow the calc as shown above so the specific question is how do you tell the cell to do that? Of course perhaps Excel cannot achieve that in which case I will have to do each sub-section manually when called for and renter the calc if I need to change it back

As you will see I have all of the other functions working well and this requirement will see the sheet complete. Once this is done it is my intention to then copy the worksheet to about 30 or so independant sheets, they will all be named TAB 1, TAB 2, TAB 3 etc etc and so on to TAB 30. The individual results of each worksheet will then automatically populate into the tender summary where all the information will be collated and give all relevant details of combined cost, nett values of material, profit, labour time etc. The tender summary at the moment is only filled in for TAB 1 although rows are awaiting the information to be put in with regard to other yet to be created worksheets.

As the projects I often quote on can be quite complex and run into the many thousands this is a tool that must be reliable and work well for me with great accuracy which I know it will do, this last function is therefore the icing on the cake so to speak although that is not to say I may have further expansion ideas for its future, but not for now.

So to my question, what I want to know, is there any way a cell can used in this way. The remote cell I would want to use to control this function would be somewhere back near column D which dictates the sub-header starting the sub-section. As you will see from that col D has a dropbox and the state of that then triggers the conditional formating in column range Z:AC

I hope you can accept the above and can help.

Regards,

BIGTREV.