How can I make a reorder form?

ycul222

New Member
Joined
Dec 1, 2005
Messages
3
Ok, sheet 1 lists 2000 products to reorder. I'd llike to put a check box next to each one so when it's checked sheet 2 will have a reorder list made. If I check item #'s 200, 300, and 800 I want only those items to show on sheet 2 cells A1 thru A3.
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
ycul222

Welcome to the Mr Excel board!

This can be done with check boxes, but not able to show the results on this board. Try this for starters though.

On the 'Products' sheet, apply Data Validation to column B. (Allow: 'List', Source: Yes,No). This will allow you to use a drop-down list to choose 'Yes' for the items to reorder.

Formula in C1: =COUNTIF(B:B,"Yes")
Formula in C2 (copied down): =IF(B2="Yes",COUNTIF(B$2:B2,B2),"")

On the 'Reorder List' sheet, A2 (copied down):
=IF(ROW()-1>Products!$C$1,"",INDEX(Products!A:A,1+MATCH(ROW()-1,Products!C$2:C$2000,0)))

Is this heading in the right direction?
Mr Excel.xls
ABCD
1Part #Reorder?3
2100 
3101Yes1
4102Yes2
5103 
6104Yes3
7105 
8
Products
Mr Excel.xls
ABCD
1Reorder Parts
2101
3102
4104
5 
6 
Reorder List
 

ycul222

New Member
Joined
Dec 1, 2005
Messages
3
reorder list

Yes, I think it's a start. I follow the first part but the second doesn't work. I get #REF!

=IF(ROW()-1>Products!$C$1,"",INDEX(Products!A:A,1+MATCH(ROW()-1,Products!C$2:C$11,0)))

suggestions?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Re: reorder list

ycul222 said:
Yes, I think it's a start. I follow the first part but the second doesn't work. I get #REF!

=IF(ROW()-1>Products!$C$1,"",INDEX(Products!A:A,1+MATCH(ROW()-1,Products!C$2:C$11,0)))

suggestions?
Which cell on the 'Reorder List' sheet is this formula in? Did you COPY the above formula from your sheet to your post on the board? What are the values in C1:C11 of the 'Products' sheet?

Is the layout of the 'Products' sheet the same as my sample?

Can you post a small sample of each sheet? To do so, see the link at the bottom of this page to Colo's HTML Maker. Also read the ‘Sticky’ titled: 'HTML Maker FAQ (How to show your sheet on the board)' which is at the top of this ‘Excel Questions’ forum. Test it out in the ‘Test Here’ forum which is found in the ‘MrExcel Message Board Forum Index’ before trying to use it in this forum.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,745
Messages
5,597,893
Members
414,185
Latest member
tarun19954

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top