Product catalog that populates an order sheet

iHarley

New Member
Joined
Feb 12, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
1613171078715.png

I've got the above product catalog (it goes on to ~124 rows) with 9 sheets for different finishes, with a 10th sheet to be an order form. What I would like to setup is a way when I enter a number in the Quantity to Order column that complete corresponding row (and cells?) are copied to the order sheet... sheet. This will only need to work on 1 sheet at a time so each product sheet would need what I assume is VBA code and a button to run. I would like entries to being on line 4 of the order sheet which is the sheet 10. The only other thing I have on this spreadsheet is a conditional formatting rule for when there is an entry in the quantity box to highlight that row so that we can more easily see and verify we have the right product selected. Any help would be great!
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,663
Office Version
  1. 365
Platform
  1. MacOS
there is a few ways to do that
I would probably use a lookup and an indirect
Possible as you have 365 version , new functions like filter perhaps
Anyway

Does the Code also specify the finish , last 2 digits UF = Unfinished , then that could be used to decide which of the 9 sheets to lookup the matching data
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,663
Office Version
  1. 365
Platform
  1. MacOS
i have mocked up an idea - will take a bit of setting up
But I have used the last 2 digits of the code to determine which sheet to lookup
UF = Unfinished & BH = Beech


ORDER SHEET
Kitchen-Order-ETAF.xlsx
ABCD
1Codeseriessizeprice
2KB123-UFUnFinished19x14x19123.58
3KB123-BHBeech100x14x1923.58
Order
Cell Formulas
RangeFormula
B2:B3B2=IF(RIGHT(A2,2)="UF","UnFinished",IF(RIGHT(A2,2)="BH","Beech",""))
C2:C3C2=INDEX(INDIRECT("'"&RIGHT(A2,2)&"'!B:B"),MATCH(A2,INDIRECT("'"&RIGHT(A2,2)&"'!A:A"),0))
D2:D3D2=INDEX(INDIRECT("'"&RIGHT(A2,2)&"'!C:C"),MATCH(A2,INDIRECT("'"&RIGHT(A2,2)&"'!A:A"),0))


Cat for Unfinished
Kitchen-Order-ETAF.xlsx
ABC
1CodeSizePrice
2KB124-UF190x14x10£ 1,213.58
3KB123-UF19x14x19£ 123.58
UF


Cat for Beech
Kitchen-Order-ETAF.xlsx
ABC
1CodeSizePrice
2KB124-BH100x14x10£ 10.58
3KB123-BH100x14x19£ 23.58
BH
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,663
Office Version
  1. 365
Platform
  1. MacOS
Using Vlookup and a column() reference would enable the formula to be copied across the range , so long as the output was in same order as the catalogue, added to the dropbox version
=VLOOKUP($A7,INDIRECT("'"&RIGHT($A7,2)&"'!A:H"),COLUMN()-1,FALSE)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,549
Messages
5,636,933
Members
416,953
Latest member
prakashkumar

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