I really can't see the client going for that - they're 'difficult'. If they come back tomorrow & say they have to be able to sort, I'll start giving them the options. Thanks for that. The frustrating thing is originally it was just one row per client, so sorting was easy. Then they decided to change the spec... That should be the easy bit, if I read it into a different sheet. There are other columns - I only included Equities to keep it simple. -- Krusty '03 Tiger 955i '02 MV Senna (for sale) '96 Tiger (for sale) '79 Fantic Hiro 250 (for sale) '81 Corvette (for sale)
That sounds very funky, but I wouldn't have the first idea where to begin. However... .... if they come back insisting they must have it, you'll probably have mail, & some beer money if I can wangle it! -- Krusty '03 Tiger 955i '02 MV Senna (for sale) '96 Tiger (for sale) '79 Fantic Hiro 250 (for sale) '81 Corvette (for sale)
Well, if they don't want to sort by all available components, you already have a solution by he look of it. Working .xls available if you want. Logical. DNC seems to have an elegant solution anyway. -- +----------------------------------------------------------------+ | Pete Fisher at Home: | | Voxan Roadster Gilera Nordwest * 2 Yamaha WR250Z | | Gilera GFR * 2 Moto Morini 2C/375 Morini 350 "Forgotten Error" | +----------------------------------------------------------------+
OK, I'm going to explain this badly.. in the form of a question.. but.. Could you just read each type of record onto it's own sheet within a workbook, sort those sheets by value, then have one more page with all the formula and combining nonsense on it that does an extract across the 3 using whichever pre sorted list you select?
I'm scared now. -- Krusty '03 Tiger 955i '02 MV Senna (for sale) '96 Tiger (for sale) '79 Fantic Hiro 250 (for sale) '81 Corvette (for sale)
You wanker. So, because you're not being paid you'd sooner offer shit advise than have a proper look and help? You'll obviously not be expecting more than that sort of effort next time you post a question.
< snip > I know everyone keeps banging on about pivot tables, but I've got a feeling that you can do it using groups: http://office.microsoft.com/en-us/excel/HA011097981033.aspx However, I can't be bothered to excavate my laptop just to fire up Excel, but I'll have a go tomorrow morning if you haven't already worked it out by then.
You may be right. OTOH, I can remember deciding long ago that sub-totals and sorting are not happy bedfellows - at least not in Excel.
they can be, but, any user that isn't savvy to all of grouping, sorting and totals will **** the sheet up in short order. I also seem to recall (but could be wrong) that grouping isn't necessarily happy about adding additional rows, or at least, not in an automated fashion.
Automation is the key with this. The spreadsheet's generated on the fly when the user hits a button in our application, & the data can vary wildly - could be no columns after the cash one, or could be 50. Might be anywhere from 1 to several hundred clients. It will only be viable if all the user has to do is click a 'sort' button at the top of a column, & everything happens automagically. -- Krusty '03 Tiger 955i '02 MV Senna (for sale) '96 Tiger (for sale) '79 Fantic Hiro 250 (for sale) '81 Corvette (for sale)
I figured that out from your other responses. Hmm, that might scupper my macro to refresh theory, unless your application can drop the macro in automagically. This isn't a problem, as we can use the macro to insert a new column into a set place, like before or after the client name. Single rows really is the best way to do this, but I'm convinced it can be done in the fashion discussed, although now I think a little harder, I'm not sure how we're going to automate the ranking, but that should be easy to blag^h^h^figure out.
Old Peculier. Alc 5.6% vol. A couple of bottles after my earlier curry and pints of Stella in the bar. I know it's not a reason but it is an excuse.
In communiqué <>, Heh, I may have got something right. <goes back to manipulating tuples (relational model) using an application designed for the job> -- +----------------------------------------------------------------+ | Pete Fisher at Home: | | Voxan Roadster Gilera Nordwest * 2 Yamaha WR250Z | | Gilera GFR * 2 Moto Morini 2C/375 Morini 350 "Forgotten Error" | +----------------------------------------------------------------+
The voice of reason ! -- +----------------------------------------------------------------+ | Pete Fisher at Home: | | Voxan Roadster Gilera Nordwest * 2 Yamaha WR250Z | | Gilera GFR * 2 Moto Morini 2C/375 Morini 350 "Forgotten Error" | +----------------------------------------------------------------+
That's basically what I'm doing. The data's XML, generated from Oracle, with Excel purely used to display it as everyone will have Excel installed. There was no requirement originally to change the outputted sort order, so this is basically trying to fudge something that's had a lot of work put into it. I don't want to change the main code as it's many thousands of lines, & they're going live in five weeks. If that's what's required though (i.e. specify the sort order before hitting the big button), then that's what I'll do - & send a big invoice for the privilege! -- Krusty '03 Tiger 955i '02 MV Senna (for sale) '96 Tiger (for sale) '79 Fantic Hiro 250 (for sale) '81 Corvette (for sale)
