I’d like to do this without having to use pivot tables. I put together a bi weekly report and want to have separate tabs for the different countries.
Lets assume I have 5 entries for Denmark. I want all 5 of those entries to show up in their own tab. If it was always going to be those 5, that would be easy. The problem is, the next time I run the report, there might be two more accounts added such that Denmark will now have 7 entries.
I think I need some sort of lookup function where it keeps returning values for Denmark until I run out of values.
Do you just want the total or the individual entries? if total, you can use sumif function so long as there is a constant in the data source tab.
For example - on your data source tab, column A has account, column B has country, column C has the amount
on your Denmark tab, you could put in cell A3 “Denmark”, cell B3 type in =sumif(Data Source TabB:B,A3,Data Source TabC:C)
This will return the sum total for any listings with Denmark listings in column B.
If you need individual listings, pivot table is the way to go or writing a macro that will copy the data source table and then filter out and delete anything that is not Denmark.
I would say this is much easier (and neater) solved in Access (or any other simple database program).
If you absolutely want to use Excel, you can use the “COUNTIF()” command to determine how many entries you’ll need per category, and use the “MATCH()” and “OFFSET()” commands to extract the corresponding entries.
Not knowing access or VBA, I think this is the answer. The formula may end up being a bit complicated, but I think some combination of count if, match, index, offset, and VLookup may work.
I need a recurring report that pulls out the specific data. i.e. I need a list of all the accounts in Denmark. their instrument SNs, the instrument type, etc.
Why not have all of the data on one worksheet and use filter?
That’s what I currently have. In short, using all the data in a filter requires a level of explanation of how to get to what the country managers want to see.
I know it sounds stupid……anyone should be able to look at a sheet and figure out how to sort for what they want, but it often requires 60 seconds of explanation. The problem is, I don’t have the time nor the ability to track down every country manager and show them how the data is organized.
So, instead the idea is to have a one page summary for each country where they just go to that page and go “hmmm, okay, cool.”
give the country managers some credit, it’s not rocket science . . . and if they can’t figure out Excel filters, do you want them managing countries?
The question for me is - - - is it OK for country managers to see the numbers for other countries? If so, put together a little demo video and give them a link to it, we do this all the time.
I would agree about sharing the numbers and I would agree about training.
It would take you less time to do a small deck with some screen capture than coming up with a new formula (assuming numbers can be shared).
That being said, if those managers have no clue about Excel, what tells you they will have a clue about Excel Tab and which Tab to click?
You’ll probably have to do this in two steps if you want to avoid VBA. I’d first create a series of columns where you’ve got a bunch of IF statements: =IF({RxCx}=“Denmark”,{value to be displayed},“”) such that visually, you only see the rows of data where the country = “Denmark” (or set to some chosen value, of course). Second, create a single column named range “blanksrange” that contains unique values (e.g. don’t make it the column of blanks and “Denmark”… account numbers, names, etc. are good). Third, put this into a new column:
=IFERROR(INDEX(blanksrange,SMALL((IF(LEN(blanksrange),ROW(INDIRECT(“1:”&ROWS(blanksrange))))),ROW(A1)),1),“”)
and fill it down for as many rows are in blanksrange, then control-Shift-Enter to turn it into an array formula.
This should create a list of all of your unique values from step two, but without blanks. Finally, you can use vlookup or index/match (e.g. =INDEX({column of desired values from the original table},MATCH({the cell with the “unique value”},{range of unique values in the original table},FALSE) to get the rest of the values.
Huh. That sounds a lot more difficult than it actually is. Let me know if it’d be easier if I send you an example…
I can understand that you want the various country managers to open their sheets without having to run anything, but what about a macro you could run against the entire dataset that would pump out a separate sheet for each country; like Denmark.xls. That way you could run it in a controlled (and knowledgeable) environment and the managers would get their simplified data?
Systematically parsing through datasets without using the built in filter functions relying solely on in-cell formulas is a fools errand, imo…
I am in IT, and have snatched a tidy income over the years, and plead Guilty as Charged to empowering the people with technology
I’m not in IT. I’m in Business Intelligence. Telling managers that I don’t intend to make gathering information as user friendly as humanly possible will prevent me from snatching a tidy income.
Right now I’m imagining a Dilbert comic where the Lorenzo the IT guy retires and opens a restaurant. The customers are then told that they are expected to know how to cook the food themselves.
Al joking aside, my job is to provide a service. The better I make that service, the better I look. So far I’ve made a reputation for myself by providing the users reports that are extremely easy to read.