Forum Discussion
Formula Help
spalmer The attached file now contains a lookup table for all the products, colours and sizes. This makes it easier to match the individual order rows to the correct prices. You will note that I have used several named ranges to make it all easier to write and maintain. Furthermore, I added a few COUNTIFS in the order summary for Hats, as I expect that would have been your next question. Perhaps you find my changes useful in completing the schedule for the other products as well.
And I can think of many more improvements, and would probably come-up with a totally different approach. But it's beyond the scope of this forum to go into details.
spalmer The attached file now contains a lookup table for all the products, colours and sizes. This makes it easier to match the individual order rows to the correct prices. You will note that I have used several named ranges to make it all easier to write and maintain. Furthermore, I added a few COUNTIFS in the order summary for Hats, as I expect that would have been your next question. Perhaps you find my changes useful in completing the schedule for the other products as well.
And I can think of many more improvements, and would probably come-up with a totally different approach. But it's beyond the scope of this forum to go into details.
Sir, thank you so much! I really appreciate your time and help in this and would really like to hear your thoughts and recommendationsabout this. I love excel and always love to hear opinions on how to make better spreadsheets. Thank you again Riny_van_Eekelen
spalmer Glad I could help. With regard to my thoughts about the design, I believe you complicate the matter by having each order row containing all products that then also calculates the tax and total amount for each row. Was wondering, what if a customer wants to buy 5 hats and 3 pants of different colour and size? You'll end up entering multiple partially filled rows for one customer and you still have to summarise the total for that customer.
So, I would be inclined to make it all a bit more structured.
Maintain lists for Customers and Suppliers, each with and ID, Name, Address etc.
Maintain a Product list (Product ID, Description, Size, Color, Price, Supplier).
Maintain an Order log (OrderNr, Date, ItemNr, Customer ID, Product ID, Qty)
Then, from all of that you can fairly easily extract information for invoices to your customers and orders to your suppliers.
But perhaps, I'm overthinking the process and your business might not require such an approach.
- No not at all those are great ideas. Totally forgot about Qty lol. I was also thinking about turning that into a table (just the ordering portion, not the sizes and cost port) . Do you think those formulas will still work if I turned it into a table?
