Hello all.
I am posting with a question I can hope you friendly bunch can help me with. I have a spreadsheet which I use in my shop. It has two sheets, "ProductData" and "Lookup."
In "ProductData" it contains a database of 25,000 lines in a table "Products" which is data from a CSV file. This table has 36 headers which each contain various product attributes.
In "Lookup" there is only Column A which is unlocked, which is a barcode input (from a USB scanner, or may be manually entered.) - When the barcode is entered it will populate the remaining columns with the data associated with that product. The purpose of this is to scan items on the shop floor in order to generate labels or match against a purchase order, etc etc. In "Lookup," I do not need every attribute so there are only 17 columns pulling data plus two that calculate based on the pulled data. There are 1000 rows with these formula.
While this certainly works, I am running it on a powerful machine and I would like to reduce the size to enable it to run efficiently on other machines on my system. I feel as though my formulas are quite lengthy and I was hoping to condense them where possible to improve efficiency. I don't know if using VBA would speed up the process, rather than having 1000 rows calculating things, would it only calculate on the specific row?
In "Lookup"
Column B(lookup):
* C5 is the name of the header in "Products," in this case it is "SalePrice"
* This is repeated across the other columns with the appropriate reference.
Column O (formula):
* P5 = Y or N, this is an option to hide cost pricing.
* This is for Margin (%), a similar formula is in Column Q for Margin.
Other than this, there are no other formulas or VBA in the workbook but I do plan on adding things in the future to include products that don't scan etc.
There is very little cell formatting - just "Number", "Text" on things like price and barcode. I haven't altered the font, colour etc other than the header titles on "Lookup" (19 cells.) No conditional formatting except for two cells in Row 1 which are just using a simple COUNTIF function.
I have cleared and hidden rows 1007 onwards and Columns V onwards.
I am using Office 365 on:
Windows 11 Desktop (Main)
Windows 11 Tablet (Target Use)
Windows 10 Desktop (Alternate)
OSX Big Sur (Infrequently)
I would appreciate any assistance from everyone. I love building spreadsheets, I'm just not that great at it yet. TIA for the support.
Adam
I am posting with a question I can hope you friendly bunch can help me with. I have a spreadsheet which I use in my shop. It has two sheets, "ProductData" and "Lookup."
In "ProductData" it contains a database of 25,000 lines in a table "Products" which is data from a CSV file. This table has 36 headers which each contain various product attributes.
In "Lookup" there is only Column A which is unlocked, which is a barcode input (from a USB scanner, or may be manually entered.) - When the barcode is entered it will populate the remaining columns with the data associated with that product. The purpose of this is to scan items on the shop floor in order to generate labels or match against a purchase order, etc etc. In "Lookup," I do not need every attribute so there are only 17 columns pulling data plus two that calculate based on the pulled data. There are 1000 rows with these formula.
While this certainly works, I am running it on a powerful machine and I would like to reduce the size to enable it to run efficiently on other machines on my system. I feel as though my formulas are quite lengthy and I was hoping to condense them where possible to improve efficiency. I don't know if using VBA would speed up the process, rather than having 1000 rows calculating things, would it only calculate on the specific row?
In "Lookup"
Column B(lookup):
Excel Formula:
=IFERROR(IF(ISBLANK(B$5),"",IF(ISBLANK($A7),"",XLOOKUP($A7,Products[Barcode],INDIRECT("Products["&B$5&"]"),""))),"")* This is repeated across the other columns with the appropriate reference.
Column O (formula):
Excel Formula:
=IFERROR(IF(OR($P$5="N",ISBLANK($O7),ISBLANK($B7)),"",($B7-$O7)/$B7),"")* This is for Margin (%), a similar formula is in Column Q for Margin.
Other than this, there are no other formulas or VBA in the workbook but I do plan on adding things in the future to include products that don't scan etc.
There is very little cell formatting - just "Number", "Text" on things like price and barcode. I haven't altered the font, colour etc other than the header titles on "Lookup" (19 cells.) No conditional formatting except for two cells in Row 1 which are just using a simple COUNTIF function.
I have cleared and hidden rows 1007 onwards and Columns V onwards.
I am using Office 365 on:
Windows 11 Desktop (Main)
Windows 11 Tablet (Target Use)
Windows 10 Desktop (Alternate)
OSX Big Sur (Infrequently)
I would appreciate any assistance from everyone. I love building spreadsheets, I'm just not that great at it yet. TIA for the support.
Adam
