Using excel to write wiring connection plan

[caplja]

He there, hope this is not going to be a too long post but will try to minimize it.

I'm trying to write wiring plan of connections in excel.
Have been able to manage data sort in the way I need to once I have all data in one huge table. But, now I'm stuck with automatically filling data table from sheet with wiring connections.

Here is an example(just one bit of it) what it looks like(picture)


http://www.deviantpics.com/hbZ


Ok, this was drawn in autocad, but I will make a table and do that in excel. It will contain only data in numbers and text.
As you see, this tell you destination A and B of one cable, type of cable, and its name.

And the most important thing, on the first sheet there is various number of these tables, all king of terminal blocks. X100,X110..x200. And for every single one, I want excel to create on its on sheet name of terminal block the table described below.

I want to export that data to another sheet in excel that will have name of terminal block: "X220". And on that sheet will be create table like this one(picture):

http://www.deviantpics.com/hbo


Can it be done?

[Glenn Kennedy]

No-one is going to want to re-type all that stuff.

Will you please attach a sample Excel workbook? Please don't attach a picture of one.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[caplja]

I understand, hoing to write a sample workbook asap.
thank you

[caplja]

OK, so here it is

On sheet terminal block there is three A3 paper with specific data. Only data that is process to sheet "table of connections" is in gray area.

Hope i have make everything clear. There can be done some modification to this table if it is going to make it easier.

To clarify, I need the sheet like "table of connections" to to create itself automatically when I enter data on first sheet.

[Glenn Kennedy]

Brr. That took a while. Try this. there are a lot of complex array formulae here. BUT, so far as I can see it reproduces your example. here's hoping that it was representative of the real thing.

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...

I also had to create a helper column on the first sheet to get it to work. it can be hidden, of course.

[Glenn Kennedy]

I should have added that I corrected a couple of spelling errors (tarminal instead of terminal...). I hope you don't mind!!

[caplja]

Brr. That took a while. Try this. there are a lot of complex array formulae here. BUT, so far as I can see it reproduces your example. here's hoping that it was representative of the real thing.

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...

I also had to create a helper column on the first sheet to get it to work. it can be hidden, of course.

O my, I'm grateful to you so much! have been struggling for days with array formulas.

I'm new to "advanced" excel and have been able to pull out data from this kind of table that you have now created.
This is only part of project but it means a lot to me, thank you.

Have tried to input some extra data and works just fine.

Only one thing I need to correct, In column D and H on sheet3. Terminals a shown only if they are number, it can happend that for some kind of apparatus terminal is combination of letter and number, example: "A1" or "Y+".
I guess all I need to do is to make same rule under column D and H as it is on C and G to do that.

Thank you again, will analyze those formulas as much as I can and try to learn from it.
You helped me a lot. Wish I could sent you a beer

All best,
Caplja

[Glenn Kennedy]

No. It's a bit more than that. See the revised sheet.

[caplja]

I apologize, I have complicated it for you.
Must have been said it wrong. I was telling about other two columns, not the main one.
There is always a number in that one (N-column) .

Now, I painted the example in red. You see, those terminals are not shown in sheet 3.

[Glenn Kennedy]

OK. Back to the original formulae (much less ugly looking). In the "terminal" column, I had applied a "trick" to suppress zeros in a column returning numeric data (another,different, "trick" has been applied to do the same in columns G & K - columns returning text). Since you have a formula returning mixed text and numbers, I used a custom format to hide the zeros.

It should be OK now??? !!!!

[caplja]

Wow, super fast.
Yes, this is it.

Going to work on it now.
Thank you a lot.

[Glenn Kennedy]

Woo Hoo!

I've earned a beer.

[caplja]

To be precise I owe you as much beer as you can drink if we are going to measure time you have saved me.

Hope I'm going to analyze and learn what have you done here so I can repeat it.

I'm testing it right now and it works just fine.
Have managed to input twice as more "tables" on terminal block sheet. Have edited the range of formula and it works like a charm.

[Glenn Kennedy]

I warn you...I'm Irish, so that's a lot of beer...

[caplja]

Have notice that on first post, so knew to offer a beer!
challenge accepted

thank you again