Hi -
I have attached the workbook containing the problem because this description may be a little confusing. There are 2 sheets, the first is called 'Shopping Lists' and the second is called 'Countries'.
The 'Shopping Lists' sheet has two sets of data: the bottom set shows each shopping list, each item, the item category, the item country of origin, and the weighting (in %) of that item on the list. The top set is a condensed version of the bottom: it is a simple table with each list, and the composition by category of items in the list.
The 'Countries' sheet just has a list of countries and whether they are classified as 'Emerging' or 'Developed' economies.
I have 2 questions and would appreciate any advice or creative solutions (or not so creative...I have a feeling I'm missing some basics):
1) In Data Set 1, I want to show the composition by category (protein, carb, etc.) for each list. It's easy enough to use SUMIF() for this, but I want to exclude summing items that are internationally sourced. For internationally sourced items, I want to sort them by whether they are from developed or emerging nations.
I am trying to use a function for these last 2 rows of Data Set 1 that will determine if items are internationally sourced, and then use vlookup within that function to go look on the next sheet ('Countries') to see how that country is classified. I can't figure out this formula...and I don't even know if it's possible to use VLOOKUP() within SUMIFS().
I don't just want to add a column to Data Set 2 with the classification of the country because then I will have to re-do all the formulas in Data Set 1.
2) In Data Set 1, I want to use the fill handle to drag the formula across to fill List 2, List 3, List 4, etc. However, when I do this, the fill handle just moves the formula over by 1 cell (because in Data Set 1, each list only takes up a column) - but in Data Set 2 there are multiple columns for each list. How do I copy&paste the formula / use the fill handle so that my formula will use the correct array (i.e. for List 2, the sum_range should be J13:J19, instead of G13:G19)? Can I use OFFSET() for this, and if so, how?
Thank you VERY much for any help anyone can provide!!! Please let me know if any part of the question is unclear.
Bookmarks