Concat & Index mixed in a formula help

I am trying to bring together some cell items using concat, but am having a problem because I think I need to incorporate Index and Match in it, but I don't know how to do it.

If someone could help please it would be much appreciated.

In the enclosed file I want it to be on line 11, and copyable down.

I am also trying to format a text item, within the concat formula too, but can only get it to show 0 rather than S, I have cells format as this and it works, but I have copied the formating into the formula and it doesn't?

So on line 11 with the formula the sample of how i want it to look is on line 16.

So for the 1.50 - 1.75 part, i want it to look at the manhole refernve on tab surface manhole and then look how deep it is on surface manhole depth range tab an bring forward its depth from lines 9 & 10

Finally on the surface manhole tab the manhole has 1 hydrobrkae, if this is the case I want this bringing into the description, if it is not then nothing should show. e.g if there is a 1 in the box it is needed if it is empty then nothing.

Hi there,

It's a bit of a mouthful, but see if the following formula in cell B11 does what you need:

Please Login or Register  to view this content.

If I needed to use a formula like this I'd probably include a few helper columns and split the various formula components over those columns.


Hope this helps - please let me know how you get on.

Regards,

Greg M

Hi Greg,

Thank you that works great, apart from S14 & S16 arent coming through if I copy them down.

I can see why S14 might not as it doesn't have a reference on the depth range sheet, but can't see why S16 wouldn't work though?

Hi again,

Thanks for that feedback.

Cover type S16 doesn't have a non-blank reference in cells F19:L19 of the "Depth Range" worksheet either - hence the #N/A error in cell B19 of the "Sheet1" worksheet.

A reference for Cover type S16 will appear if you make the highlighted change to the formula in cell K19 on the "Depth Range" worksheet, but I have no idea if making such a change is consistent with your tolerance requirements:

Please Login or Register  to view this content.

Cover type S14 is just too large to be covered by the ranges currently displayed on the "Depth Range" worksheet, so I imagine you'll need to add two(?) more data columns to accommodate that size.


Hope this helps - as before, please let me know how you get on.

Regards,

Greg M

Also how would I add the following too please?

Hydrobrake work comes through dependant on J column, how would i add the words in row 8 columns H to O to bring through those words if there is a one in the cell.

I have tried adding "&IF('Surface Manhole'!I12=1," e.o. Ladder","")) to the end of the formula, but I think i am getting it wrong somewhere as i keep getting an error

Thanks for the above Gregg, that was a help with the schedule, must have posted at the same time

Hi again,

Just so I understand your requirements, are you talking about a "summary" that might look (where appropriate) something like the following:

S11 - 1500 diameter 1.50 - 1.75m deep. Includes Hydrobrake & Penstock & Chain

Regards,

Greg M

Yes that is correct if they have a 1 in the box similar to how the hydrobrake works now. So only if there is a 1 in the box, if not then nothing is shown.

Thanks

Hi again,

Thanks for your very prompt response.

I think that the following formula entered in Cell B11 of the "Sheet1" worksheet will do what you need:

Please Login or Register  to view this content.

As a formula it's definitely not a thing of beauty, and I would strongly recommend that you revise the layout of the "Sheet1" worksheet to include several helper columns over which the various components of the above formula can be spread, before these components are concatenated into an overall "summary" cell. The helper columns can be hidden to improve the layout of the worksheet.


Hope this helps - as before, please let me know how you get on.

Regards,

Greg M

Thanks for all the help Gregg, much appreciated. ( is there a way to have the cell auto size, as if all the items arent selected then the cell looks too big, when it is set to wrap text?

This is just what i wanted.

Regards

Graham

Hi again,

First of all, many thanks for all of your feedback and also for the Reputation increase - much appreciated!

You're welcome - glad I was able to help.


As far as AutoFit and Wrap Text are concerned, I think you might have to AutoFit Column B on the "Sheet1" worksheet each time a change is made in columns H:O of the "Surface Manhole" worksheet. Such changes would cause the text in the associated cells on the "Sheet1" worksheet to expand/contract as appropriate.

It IS possible to run a routine which applies AutoFit to column B whenever changes are made in columns H:O. Is that what you need?


Best regards,

Greg M

Hi Gregg,

Yes how would Ido that?

Hi again,

Sorry for the delay in replying to your last request.

The following code should be entered into the VBA CodeModule of the "Surface Manhole" worksheet:

Please Login or Register  to view this content.

This will cause AutoFit to be applied to cells B11:B110 of the "Sheet1" worksheet each time a value in a cell in the range H11:O110 of the "Surface Manhole" worksheet is changed.

The highlighted values may be altered to suit your requirements.


Hope this helps - as always, please let me know how you get on.

Regards,

Greg M