Group a set of values based on dimension and depth ranges

[gjjh25]

Can anyone help with this please?

I am trying to group a few values in to one price depending on selected cells.

So it currently works as follows.

In Surface Manhole Tab

I enter the ref as a number, the cover level, the invert level, this will then work out a depth.

I then go across and select any of the following heading that apply to this manhole, backdrop, ladder hydrobrake etc all the way to the end.

The surface manhole depth range tab sorts the various manholes in to depth groups with the manhole reference so I can see what they are, so in this example s2 & s3 are both at the same depth range but a different diameter manhole.

The following tab is where I want all the values to come together.

So at the moment it does the following:-

It takes the references from the Surface manhole depth range tab and brings them through as a count.

At the bottom of this sheet is a table of rate, which then simply times by the number from the top table to calculate the price.

This is the bit I would like to expand on.

I would like the manhole to have their own individual price, e.g

S1 would be a 1200 dia manhole price from the price table schedule, but then would add in the value of a backdrop. The rate being in the box above the heading

S2 would be a 1200 dia manhole price form the price table schedule, but then this would include a ladder in the price. The rate being in the box above the heading

I think I may need a new tab that would do this but am not sure how to pull all this together.

Just to be clear the manholes need to be sorted first in diameter and then in depth range as at the top of the depth range table. There are normally 50+ rows in this workbook just cut it down for ease.

If someone could help or advice please it would be much appreciated.

[José Augusto]

Hi

Try this on 'Surface Manhole'!Q11 and drag down

Please Login or Register  to view this content.

Notes: Prices in 'Surface Manhole'!$G$6:$O$6 and quantyties in 'Surface Manhole'!$G$11:$O$99 (Replace Yes by 1. use CF if wanted)
See the file

[gjjh25]

This is excellent.

Thank you very much for your help.

[José Augusto]

You are welcome.

I'm happy to have helped.