Concatinate based on if conditions

[m2_ilango]

Friends, I have a great belief that this forum is helping me real quick on my challenges.

Here is a quick solution i am looking for using macros or simple formulas whatever it is.

I have attached a spreadsheet, wherein i have to display multiple ECN numbers on one cell, if the conditions match for "Status" and "Resources". This is useful for ma as a Dashboard.

I have given an example for what kind of result i am looking for in BLUE color.

Regards,
ILANGO M
+91-9731855576

[Glenn Kennedy]

Using this UDF.

Paste code into a module

Please Login or Register  to view this content.

and use this array-entered formula.

=concatall(IF($D$11:$D$21=$B3,IF($C$11:$C$21=C$2,$B$11:$B$21,""),""),CHAR(10))

Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

[Glenn Kennedy]

Incidentally, if I were you, i'd remove your phone number from the post. there are a lot of strange people out there!!

[Crooza]

Try this UDF in a new module

Please Login or Register  to view this content.

then in C3
=join(C$2,$B3)
and drag across and down

[m2_ilango]

Thank you for your solution.
Crooza, Can you help me introduce a form control and then execute the command, I am finding trouble in that case.

Thanks in advance for your help.

[m2_ilango]

Glenn, Thanks for your advice. I am unable to execute it with a form button (May be a box) and a macro assigned to it. Please help me on that one

[Crooza]

Not sure what you're after. Is the UDF not what you wanted??

[Glenn Kennedy]

Likewise... Does it do/not do what you want?

[m2_ilango]

Sorry, Earlier i posted what i wanted as an example.

Within limited time, I am unable to tweak the help to the real time environment.

What i am looking for is a form function ("Execute"), by clicking which the dashboard on Sheet 1 should be up to date,
Data is on the sheet 2, for which it should refer column B, D and H for data.

It would be a great help if you could help me on this one.

Regards,
ILANGO M

[Glenn Kennedy]

The USF will always be up to date. In the input data changes, so the output will update, too.

[m2_ilango]

That i have noticed, That feature is great. I am unable to add new rows and that is not reflecting and one more requirement is the data will be in another sheet and i need just the dashboard on the another sheet. And one more execute form tab i am planning to place.

[m2_ilango]

Got it, thanks for your help