Creating a layout of values?

[mfleming]

Hello.

Overall Situation:
From my initial 2 columns of information 1 being Panel (number) and the 2 with Reinforcing (number) create data that shows all the panels for a specific reinforcing number.

The way I do it right now is have the two columns then beside them use a formula to get the cell value next to it and fill down. Then I manualy delete the extra reinf numbers and then cut/paste panels into proper cells going from left to right.

We have to re-create panels and reinforcing numbers for everyjob so I would like to automate this.


Here is an example of what it comes out as.

http://www.homenkobuilders.com/reinf.gif

I know there is an easy way to do this but I can't figure it at the moment.

Thanks

Matt

 

[SkipVought]

REINF in the formula is a Range Name that refers to the data in the REINF column of data.

Select all contiguous data to A1.

Insert/Name/Create - Create names in TOP row.

Clicking on REINF, in the Name Box should select ALL the data under the REINF heading.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[mfleming]

Somethings not working out.

http://www.homenkobuilders.com/Book2.xls

Its missing some of the panels ie. "81" which is reinf "1"

 

[SkipVought]

You did not name the columns A-D ranges properly.

I renamed them, using Inser/Name/Create - Create names in TOP row and immediately got this result...
[tt]
REINF PANEL
1 1 81 82 101 165 184

[/tt]


Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[mfleming]

Somethings not working out.
I get #N/A ruslts after Reinf 8. It doesn't fill to the bottom properly.

http://www.homenkobuilders.com/Book2.xls

 

[mfleming]

nevermind, got it.

Thanks for all your help, sorry it took soooo long.

I appreciate it.

Thank you,

Matt

 

[SkipVought]

Sorry.

It fills data thru REINF 80 (row 51) Panel R01.

Your Range references are not correct.

Selecting REINF in the Name Box should select all the REINF source data.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[SkipVought]

[tt]
REINF PANEL
1 1 81 82 101 165 184
2 27 28 54 55 #REF! #REF!
3 7 11 17 21 34 38
3 44 48 61 65 71 75
4 4 14 24 31 41 51
4 58 68 78 #REF! #REF! #REF!
5 85 88 94 97 169 172
5 178 181 #REF! #REF! #REF! #REF!
6 91 175 #REF! #REF! #REF! #REF!
7 100 166 #REF! #REF! #REF! #REF!
8 8 10 18 20 35 37
8 45 47 62 64 72 74
9 83 86 89 92 95 98
9 167 170 173 176 179 182
10 2 5 12 15 22 25
10 29 32 39 42 49 52
10 56 59 66 69 76 79
11 3 6 13 16 23 26
11 30 33 40 43 50 53
11 57 60 67 70 77 80
11 84 87 90 93 96 99
11 168 171 174 177 180 183
12 9 19 36 46 63 73
40 186 187 188 189 190 191
40 192 193 194 195 196 197
40 198 199 203 204 205 206
40 207 208 209 210 211 212
40 213 214 215 216 #REF! #REF!
41 185 #REF! #REF! #REF! #REF! #REF!
42 202 #REF! #REF! #REF! #REF! #REF!
43 200 201 217 218 #REF! #REF!
50 102 164 #REF! #REF! #REF! #REF!
51 104 107 114 122 135 146
51 159 162 #REF! #REF! #REF! #REF!
52 108 113 123 129 134 147
52 158 #REF! #REF! #REF! #REF! #REF!
53 110 111 128 131 132 152
53 153 #REF! #REF! #REF! #REF! #REF!
54 105 161 #REF! #REF! #REF! #REF!
55 116 117 140 141 #REF! #REF!
56 103 106 109 112 115 119
56 125 130 133 137 143 149
56 155 160 163 #REF! #REF! #REF!
57 120 126 138 144 150 156
58 118 121 124 127 136 139
58 142 145 148 151 154 157
70 219 #REF! #REF! #REF! #REF! #REF!
71 220 #REF! #REF! #REF! #REF! #REF!
72 221 #REF! #REF! #REF! #REF! #REF!
80 R01 #REF! #REF! #REF! #REF! #REF!
[/tt]



Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[mfleming]

Conditional Format:

Formula is: =ISERROR("#REF!")

Format Colour White

I entered this to format the results but it doesn't change anything.

What did I do wrong?

 

[SkipVought]

[tt]
=OR(ERROR.TYPE(F2)=4,ERROR.TYPE(F2)=7)
[/tt]

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[SkipVought]

Obviously the CF formula in F2.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}