Hi,
I get sent a very detailed multi sheet spreadsheet monthly and need to pull the information. At present I have been using Vlookup and HlookUp to automatically extract the data I need. However the sheet I am being sent is changing each time and thus think there is a smarter way of doing this than how it has been set up.
I need to find the:
ROW: country with the specific variable then
COLUMN: draw from either volume or value and then draw either abc or def under the Volume/variable.
This all from a specific sheet.. yes i know!!!
The problem is coming in with the variables as VlookUp will draw the first occurrence of the country. Allowing me to easily draw variable x but not variable y (Note the country row is actually merged with the two variables in the next column).
Column is not a problem as I can do a simple count from VLookUp as that data stays constant.
I would like to find a way to tell the cell to look for the country then to check the cell next to it for a variable (note country column is rows merged not separate cells with two entries) and if not true to move down one row (still in the column next to to country (variable column)) and check if that is right (remembering that as a variable is sometimes broken down that it may need to move more that one row) while still ensuring that the country reference is kept.
See attachment for a short summary of what I get.
Thanks for the help!
Bookmarks