Community,
(This is a lot but any help is appreciated)
I have built a "Quote sheet" within a Excel Workbook.
I have a Worksheet that contains all parts to be used (Table 1). This sheet will be automatically populated with values form different vendor sheets I will discuss next.
I have other sheets (vendor sheets) with all possible parts we would use. They are listed and grouped together with all other parts that are secured from that vendor. (in our example Table 2, Table 3 and Table 4 are vendor sheets for their specific parts)
Table 2, Table 3 and Table 4 list parts that we would use from different vendors, and columns of info about the each part.
There is a quantity row and in the vendor worksheet and the value is 0 till we change the value to an amount to be used on the given job.
When the part quantity is changed from 0 to 1 or more we know that the part will be used on the job and info in the corresponding columns will need to be populated to Table 1.
This is how my helper row works now. Lets sat that in Table 2 we have 4 rows of parts listed and the only part we are using is in row 4 all other parts still show 0 as the quantity. In my helper column everything would be blank except row 4 which would be 1 indicting it is the only part being used and the 1st part to show up in table 1.
In Table 3 only the 3 row has a quantity indicated so the helper row would show 2 indicating the second row to be populated in Table 1.
In Table 4 the 2nd & 4th row has a quantity indicated so the helper row would show 3 for row 2 and 4 for row 4.
Table 1 (All parts used in table 2-4 automatically populated to this table)
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Helper Col[/TD]
[TD]Description[/TD]
[TD]Part #[/TD]
[TD]Quty[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1 <- Typed in Value[/TD]
[TD]Use Row A this table as value; look up from value Table 2-4 and return description[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]A Variation of the formula in B2 will be used in Table 1 cells Column B to Column E[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Table 2 (Parts from Vendor John Doe 1 - Parts used on the job will have 1 of more shown in the quty column.)
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Helper Col[/TD]
[TD]Description[/TD]
[TD]Part #[/TD]
[TD]Quty[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]Hose[/TD]
[TD]002[/TD]
[TD]0[/TD]
[TD]$15.00[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]Clamp[/TD]
[TD]005[/TD]
[TD]0[/TD]
[TD]$1.00[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]Tie String[/TD]
[TD]120[/TD]
[TD]0[/TD]
[TD]$2.56[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1[/TD]
[TD]Container[/TD]
[TD]195[/TD]
[TD]2[/TD]
[TD]$55.00[/TD]
[/TR]
</tbody>[/TABLE]
Table 3 (Parts from Vendor John Doe 2 - Parts used on the job will have 1 of more shown in the quty column.)
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Helper Col[/TD]
[TD]Description[/TD]
[TD]Part #[/TD]
[TD]Quty[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]Radio[/TD]
[TD]1102[/TD]
[TD]0[/TD]
[TD]$29.00[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]Speaker[/TD]
[TD]1169[/TD]
[TD]3[/TD]
[TD]$24.00[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]Chord[/TD]
[TD]1136[/TD]
[TD]0[/TD]
[TD]$2.12[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]Knob[/TD]
[TD]956[/TD]
[TD]0[/TD]
[TD]$3.26
[/TD]
[/TR]
</tbody>[/TABLE]
Table 4 (Parts from Vendor John Doe 3 - Parts used on the job will have 1 of more shown in the quty column.)
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Helper Col[/TD]
[TD]Description[/TD]
[TD]Part #[/TD]
[TD]Quty[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3[/TD]
[TD]Tile[/TD]
[TD]269[/TD]
[TD]3[/TD]
[TD]$15.00[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]Rug[/TD]
[TD]596[/TD]
[TD]0[/TD]
[TD]$1.00[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4[/TD]
[TD]Wood Plank[/TD]
[TD]0056[/TD]
[TD]5[/TD]
[TD]$2.56[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]Apple (lol)[/TD]
[TD]539[/TD]
[TD]0[/TD]
[TD]$55.00
[/TD]
[/TR]
</tbody>[/TABLE]
In Table 2-4 the value in the Helper Col "A" will always be unique or blank. I have already built the formulas to create thus unique values in the helper columns. I want to look up the values in the helper row in table 1 to find the valus in Table 2-4 and return the information in the row to Table 1
OK that's a lot! any info you have will be helpful. I have been trying to do this without using an array formula but am now reading the book Ctrl+Shift+Enter on arrays formulas but not there yet.
(This is a lot but any help is appreciated)
I have built a "Quote sheet" within a Excel Workbook.
I have a Worksheet that contains all parts to be used (Table 1). This sheet will be automatically populated with values form different vendor sheets I will discuss next.
I have other sheets (vendor sheets) with all possible parts we would use. They are listed and grouped together with all other parts that are secured from that vendor. (in our example Table 2, Table 3 and Table 4 are vendor sheets for their specific parts)
Table 2, Table 3 and Table 4 list parts that we would use from different vendors, and columns of info about the each part.
There is a quantity row and in the vendor worksheet and the value is 0 till we change the value to an amount to be used on the given job.
When the part quantity is changed from 0 to 1 or more we know that the part will be used on the job and info in the corresponding columns will need to be populated to Table 1.
This is how my helper row works now. Lets sat that in Table 2 we have 4 rows of parts listed and the only part we are using is in row 4 all other parts still show 0 as the quantity. In my helper column everything would be blank except row 4 which would be 1 indicting it is the only part being used and the 1st part to show up in table 1.
In Table 3 only the 3 row has a quantity indicated so the helper row would show 2 indicating the second row to be populated in Table 1.
In Table 4 the 2nd & 4th row has a quantity indicated so the helper row would show 3 for row 2 and 4 for row 4.
Table 1 (All parts used in table 2-4 automatically populated to this table)
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Helper Col[/TD]
[TD]Description[/TD]
[TD]Part #[/TD]
[TD]Quty[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1 <- Typed in Value[/TD]
[TD]Use Row A this table as value; look up from value Table 2-4 and return description[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]A Variation of the formula in B2 will be used in Table 1 cells Column B to Column E[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Table 2 (Parts from Vendor John Doe 1 - Parts used on the job will have 1 of more shown in the quty column.)
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Helper Col[/TD]
[TD]Description[/TD]
[TD]Part #[/TD]
[TD]Quty[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]Hose[/TD]
[TD]002[/TD]
[TD]0[/TD]
[TD]$15.00[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]Clamp[/TD]
[TD]005[/TD]
[TD]0[/TD]
[TD]$1.00[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]Tie String[/TD]
[TD]120[/TD]
[TD]0[/TD]
[TD]$2.56[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1[/TD]
[TD]Container[/TD]
[TD]195[/TD]
[TD]2[/TD]
[TD]$55.00[/TD]
[/TR]
</tbody>[/TABLE]
Table 3 (Parts from Vendor John Doe 2 - Parts used on the job will have 1 of more shown in the quty column.)
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Helper Col[/TD]
[TD]Description[/TD]
[TD]Part #[/TD]
[TD]Quty[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]Radio[/TD]
[TD]1102[/TD]
[TD]0[/TD]
[TD]$29.00[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]Speaker[/TD]
[TD]1169[/TD]
[TD]3[/TD]
[TD]$24.00[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]Chord[/TD]
[TD]1136[/TD]
[TD]0[/TD]
[TD]$2.12[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]Knob[/TD]
[TD]956[/TD]
[TD]0[/TD]
[TD]$3.26
[/TD]
[/TR]
</tbody>[/TABLE]
Table 4 (Parts from Vendor John Doe 3 - Parts used on the job will have 1 of more shown in the quty column.)
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Helper Col[/TD]
[TD]Description[/TD]
[TD]Part #[/TD]
[TD]Quty[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3[/TD]
[TD]Tile[/TD]
[TD]269[/TD]
[TD]3[/TD]
[TD]$15.00[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]Rug[/TD]
[TD]596[/TD]
[TD]0[/TD]
[TD]$1.00[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4[/TD]
[TD]Wood Plank[/TD]
[TD]0056[/TD]
[TD]5[/TD]
[TD]$2.56[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]Apple (lol)[/TD]
[TD]539[/TD]
[TD]0[/TD]
[TD]$55.00
[/TD]
[/TR]
</tbody>[/TABLE]
In Table 2-4 the value in the Helper Col "A" will always be unique or blank. I have already built the formulas to create thus unique values in the helper columns. I want to look up the values in the helper row in table 1 to find the valus in Table 2-4 and return the information in the row to Table 1
OK that's a lot! any info you have will be helpful. I have been trying to do this without using an array formula but am now reading the book Ctrl+Shift+Enter on arrays formulas but not there yet.
