I am working on an order form, and i have two sheets in the workbook. the
first sheet, materials, is the input sheet. it lists a bunch of materials in
our inventory and in the same row as any given material is the input for
quantity.

here is a picture of what i am describing:
http://img146.imageshack.us/img146/3...estion1bu0.jpg

Now, what i want to do is have the string of text and quantity copy over to
another worksheet (named PROPOSAL). Seems simple, but as you can see in the
first picture, not every item will be purchased. and the list is over 400
rows long. Of course they can be individually copy/pasted, but since this
will be done a lot i am looking for a quick solution to make them switch over
without a copy/paste.

this is the desired result:
http://img354.imageshack.us/img354/3...estion2nw0.jpg

I have thought of this problem in terms of lookups, arrays and even macros,
but they seem so complex (at least based on my knowledge of the three).
Anybody know a simpler solution? Thanks for any advice.

In your output sheet, B21 (if I can tell from the pic), enter the
following *array* formula:

=INDEX('HC-Material'!B9:B34,MATCH(TRUE,'HC-Material'!C9:C34<"",0))

(array formulas are committed with Shift+Ctrl+Enter).

Below, in B22 enter the following *array* formula:

=INDEX('HC-Material'!$B$9:$B$34,MATCH(1,ISNUMBER('HC-Material'!$C$9:$C
$34)*(COUNTIF($C$2:C2,'HC-Material'!$B$9:$B$34)=0),0))

Copy the second formula down until you get error values. Then, in C21
use VLOOKUP.

HTH
Kostis Vezerides

On Jun 27, 6:21 pm, DFrank wrote:
I am working on an order form, and i have two sheets in the workbook. the
first sheet, materials, is the input sheet. it lists a bunch of materials in
our inventory and in the same row as any given material is the input for
quantity.

here is a picture of what i am describing:http://img146.imageshack.us/img146/3...estion1bu0.jpg

Now, what i want to do is have the string of text and quantity copy over to
another worksheet (named PROPOSAL). Seems simple, but as you can see in the
first picture, not every item will be purchased. and the list is over 400
rows long. Of course they can be individually copy/pasted, but since this
will be done a lot i am looking for a quick solution to make them switch over
without a copy/paste.

this is the desired result:http://img354.imageshack.us/img354/3...estion2nw0.jpg

I have thought of this problem in terms of lookups, arrays and even macros,
but they seem so complex (at least based on my knowledge of the three).
Anybody know a simpler solution? Thanks for any advice.

Vererid,

Appreciate the response, but something is wrong with both formulas given.
For the first one,
=INDEX('HC-Material'!B9:B34,MATCH(TRUE,'HC-Material'!C9:C34<"",0)), it
returns a number. This is supposed to return the item name.

and on the second one, i get an error, and it highlights $C$34 in the
formula bar.

here are pictures of what im working with:

http://img170.imageshack.us/img170/3797/bkw3ku1.jpg
http://img185.imageshack.us/img185/3458/bkw4px1.jpg



I apologize if youre all good, and the error is on my end, which is highly
likely. Thanks.




"vezerid" wrote:

In your output sheet, B21 (if I can tell from the pic), enter the
following *array* formula:

=INDEX('HC-Material'!B9:B34,MATCH(TRUE,'HC-Material'!C9:C34<"",0))

(array formulas are committed with Shift+Ctrl+Enter).

Below, in B22 enter the following *array* formula:

=INDEX('HC-Material'!$B$9:$B$34,MATCH(1,ISNUMBER('HC-Material'!$C$9:$C
$34)*(COUNTIF($C$2:C2,'HC-Material'!$B$9:$B$34)=0),0))

Copy the second formula down until you get error values. Then, in C21
use VLOOKUP.

HTH
Kostis Vezerides

On Jun 27, 6:21 pm, DFrank wrote:
I am working on an order form, and i have two sheets in the workbook. the
first sheet, materials, is the input sheet. it lists a bunch of materials in
our inventory and in the same row as any given material is the input for
quantity.

here is a picture of what i am describing:http://img146.imageshack.us/img146/3...estion1bu0.jpg

Now, what i want to do is have the string of text and quantity copy over to
another worksheet (named PROPOSAL). Seems simple, but as you can see in the
first picture, not every item will be purchased. and the list is over 400
rows long. Of course they can be individually copy/pasted, but since this
will be done a lot i am looking for a quick solution to make them switch over
without a copy/paste.

this is the desired result:http://img354.imageshack.us/img354/3...estion2nw0.jpg

I have thought of this problem in terms of lookups, arrays and even macros,
but they seem so complex (at least based on my knowledge of the three).
Anybody know a simpler solution? Thanks for any advice.

Ok, i figured out the problem with the first equation, the cells werent
matching. no big issue, i didnt include the cell numbers/letters in the
original, so it would have been impossible not to make this mistake.
The first cell works no matter where the first qty mark is.

but the second one still gives an error:
http://img397.imageshack.us/img397/338/bkw5et8.png

Thanks for responding.


"vezerid" wrote:

In your output sheet, B21 (if I can tell from the pic), enter the
following *array* formula:

=INDEX('HC-Material'!B9:B34,MATCH(TRUE,'HC-Material'!C9:C34<"",0))

(array formulas are committed with Shift+Ctrl+Enter).

Below, in B22 enter the following *array* formula:

=INDEX('HC-Material'!$B$9:$B$34,MATCH(1,ISNUMBER('HC-Material'!$C$9:$C
$34)*(COUNTIF($C$2:C2,'HC-Material'!$B$9:$B$34)=0),0))

Copy the second formula down until you get error values. Then, in C21
use VLOOKUP.

HTH
Kostis Vezerides

On Jun 27, 6:21 pm, DFrank wrote:
I am working on an order form, and i have two sheets in the workbook. the
first sheet, materials, is the input sheet. it lists a bunch of materials in
our inventory and in the same row as any given material is the input for
quantity.

here is a picture of what i am describing:http://img146.imageshack.us/img146/3...estion1bu0.jpg

Now, what i want to do is have the string of text and quantity copy over to
another worksheet (named PROPOSAL). Seems simple, but as you can see in the
first picture, not every item will be purchased. and the list is over 400
rows long. Of course they can be individually copy/pasted, but since this
will be done a lot i am looking for a quick solution to make them switch over
without a copy/paste.

this is the desired result:http://img354.imageshack.us/img354/3...estion2nw0.jpg

I have thought of this problem in terms of lookups, arrays and even macros,
but they seem so complex (at least based on my knowledge of the three).
Anybody know a simpler solution? Thanks for any advice.

From what I see from your pics you have not array-entered the formula.
Double-click to enter Edit mode and then commit with Shift+Ctrl+Enter.

HTH
Kostis

On Jun 27, 9:33 pm, DFrank wrote:
Ok, i figured out the problem with the first equation, the cells werent
matching. no big issue, i didnt include the cell numbers/letters in the
original, so it would have been impossible not to make this mistake.
The first cell works no matter where the first qty mark is.

but the second one still gives an error:http://img397.imageshack.us/img397/338/bkw5et8.png

Thanks for responding.

"vezerid" wrote:
In your output sheet, B21 (if I can tell from the pic), enter the
following *array* formula:

=INDEX('HC-Material'!B9:B34,MATCH(TRUE,'HC-Material'!C9:C34<"",0))

(array formulas are committed with Shift+Ctrl+Enter).

Below, in B22 enter the following *array* formula:

=INDEX('HC-Material'!$B$9:$B$34,MATCH(1,ISNUMBER('HC-Material'!$C$9:$C
$34)*(COUNTIF($C$2:C2,'HC-Material'!$B$9:$B$34)=0),0))

Copy the second formula down until you get error values. Then, in C21
use VLOOKUP.

HTH
Kostis Vezerides

On Jun 27, 6:21 pm, DFrank wrote:
I am working on an order form, and i have two sheets in the workbook. the
first sheet, materials, is the input sheet. it lists a bunch of materials in
our inventory and in the same row as any given material is the input for
quantity.

here is a picture of what i am describing:http://img146.imageshack.us/img146/3...estion1bu0.jpg

Now, what i want to do is have the string of text and quantity copy over to
another worksheet (named PROPOSAL). Seems simple, but as you can see in the
first picture, not every item will be purchased. and the list is over 400
rows long. Of course they can be individually copy/pasted, but since this
will be done a lot i am looking for a quick solution to make them switch over
without a copy/paste.

this is the desired result:http://img354.imageshack.us/img354/3...estion2nw0.jpg

I have thought of this problem in terms of lookups, arrays and even macros,
but they seem so complex (at least based on my knowledge of the three).
Anybody know a simpler solution? Thanks for any advice.