Forum Discussion
Formula Help
Hello Everyone,
I'm in need of some help with a formula I just cant seem to figure out, if its even possible. In the spreadsheet attached I have an order form. On the far right in columns T - Z I have a list of jersey options along with their color, size and cost for each. Some of the jersey options cost different depending on the Size and Color.
what I'm trying to figure out is, lets take hats for instance, in columns C, D & E I have size, color and cost. what I'm trying to figure out is, in column E "Hat $" I need a formula that totals the hat cost depending on what hat Size and Color they choose from the list of options to the right in columns T-Z. I've tried IF, AND, OR but cant figure it out. if this is even possible that would be great. Thank you so much for your time and help I really appreciate it. I hope this isnt to confusing.
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.
11 Replies
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.
