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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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
Back
Top