• This topic has 22 replies, 9 voices, and was last updated 8 years ago by poly.
Viewing 23 posts - 1 through 23 (of 23 total)
  • Is it time for an excel question ?
  • scaredypants
    Full Member

    Oh, I think so !

    (please)

    I’ve got a spreadsheet that I’m using as a template, pasting in monthly results from more than one source (pivot tables). These are always a fixed layout but, obviously, variable numerical values.

    This template sheet then “corrects” a known error in the raw data by subtracting a fixed number from certain results in the pivot table output. To achieve this, the cells are all “=A25-A10” or whatever

    What I’d like is to have another series of cells that show the numerical value results of all those cells so I can copy & paste them to YET ANOTHER spreadsheet 🙁 .

    I bet there’s an elegant way of doing the whole thing much better but for now I’m after a command that says “=show the value of that cell over there with a formula in it”. This then needs to update every time I repaste a different set of original numbers into the template.

    I had a google but only found some ideas of vis basic scripts that would have to be refreshed anyway. Not only am I not really capable of including a script, I’m not sure it’s that much easier than just doing it manually every time using cut & paste special / values

    Heyyulp STW, HEYYULP !!!

    garage-dweller
    Full Member

    One way is to record a macro that just does your existing manual copy/ paste special. Then put it on a button and just click each time you refresh the base data. Reduces it to a one click job.

    Beyond that I think you may be in script territory.

    ironnigel
    Free Member

    Fresh Goods Friday 696: The Middling Edition

    Fresh Goods Friday 696: The Middlin...
    Latest Singletrack Videos
    scaredypants
    Full Member

    Thanks g-d
    amazingly, I do know how to record a macro …

    samunkim
    Free Member

    From your post its obvious that you know you are doing something pretty lame. But if you are stuck with having to do it this way – and we’ve all been there…

    Why not create two tables
    1: drop-in input table – Feeding the data to template itself
    2: an “output” table reading from the template & ready to paste back out from

    you only need to some
    =Sheet1CellA1
    =Sheet1CellA2
    =Sheet1CellA3
    &
    =TemplateCellA5
    =TemplateCellB7
    =TemplateCellC10
    type formulas this way.

    poly
    Free Member

    Or use a formula in the receiving sheet that links to the file with the corrected values in it, e.g. :

    ='[Monthly_Data.xlsx]CorrectedValues’!$A$1

    You can even build that name on the Fly, but lets keep it simple to start with (because Excel is the wrong tool anyway!).

    scaredypants
    Full Member

    Part of my problem is the size of some of the datasets, half a million rows and 25 columns in the raw table from which the (dozen or so) pivots are derived

    That thing runs pretty slowly already – the autosave tends to stop all activity for about 20 seconds each time – and I’m loath to link loads of extra stuff to it

    The data from the pivots are then shoved into a different “reporting” spreadsheet that summarises the monthly activity into about 10 cells on each of about 10 worksheets. These are used for generating reporting charts for people to ignore

    Whole process takes me ages, and most of it could be performed by a trained monkey; by the time I get to the thinking part at the end I’m late and knackered and almost CBA any more

    They pay me for it but frankly it’s a waste of my “abilities”, none of which lie in IT (and of course, a waste of their money ultimately but the big step of taking on somebody actually competent who could knock it into efficient, usable shape (for an up-front fee) is just too much to expect an NHS organisation to swallow)

    scaredypants
    Full Member

    re

    wrong tool anyway

    how hard is SQL to learn ?

    😳

    jimdubleyou
    Full Member

    I would spend a day getting it to do all of this automagically (even pay a freelancer yourself?) then you get to go home early once a month 🙂

    scaredypants
    Full Member

    then you get to go home early once a month

    until last week when I got a new work PC I had to take it home as the work machine couldn’t even open the thing.

    My colleagues all think I go riding for 2 days while doing this shit at home

    … so I may as well, right ?

    samunkim
    Free Member

    HaHaHa.. It just had to be NHS (Massive Reports which are sent to the uncaring, with no Trend Analysis and no expectation of Action)

    Still think some decent formula can dramatically reduce your workload.

    Basically you want your Template to self populate from the pivot every time which shouldn’t be to hard. Vlookup will work against a Pivot Table for example.

    The NHS also really like QlikView and your Trust (?) will probably own a license already. MacroExpress will also handle your data imports overnight from any number of different systems

    samunkim
    Free Member

    P.S.

    Taking NHS data home. Nooooooooooooooo

    You really had better be using encrypted USB drive.

    scaredypants
    Full Member

    You really had better be using encrypted USB drive.

    Yeh, I am – that caused it’s own headache. There are no names in these sheets either

    bails
    Full Member

    So you’ve basically got a big data table, which feeds a pivot table. And then you’re picking values out of the pivot table to show in a report?

    You could save the data table as a csv/text file, or leave as excel but put it in a seperate spreadsheet from the pivot tables and use that as your pivot’s data source. That way you can download and save the data, then open the pivot spreadsheet and click Refresh All on the data ribbon to update the pivots and the formulae that run off them.

    You should be able to use a getpivotdata/sumifs/vlookup or even just “=pivot_worksheet(T12)” to link formulaically to the pivot table and save you copying and pasting the same thing into the same place over and over.

    scaredypants
    Full Member

    I’ve never had much luck with getpivot commands – I tend to use one pivot table and vary the fields each time, so I think that might fail unless I made a dozen pivots (which I’ve been thinking about)

    I just can’t believe that there’s not a “paste the cell value here” command 🙁

    bails
    Full Member

    If you’re rejigging the pivot table in the same way each month so you can split/total things differently to get certain figures out then just make multiple pivots. You’ll set them up once, which should only take as long as moving them around each month anyway (just copy the tab). Then link to specific cells or ranges in those pivots with a simple =pivot1(a1) or sumifs/vlookups if necessary. Then all your have to do is download fresh data, click refresh all, hit f9 to make sure your formulae are recalculated, and bob’s your father’s brother.

    Edit: What do you mean by “paste cell values here command”? Just copy and paste as values?

    poly
    Free Member

    You can become reasonably competent in sql in a weekend, although codecademy have a three hour self learning course.

    I’m not sure sql is what you need either, but certainly cut n paste with 500000 values every month is not a sensible thing to do with Excel.

    mikesbikes71
    Free Member

    As mentioned earlier a recorded macro to paste special values should do what you want.
    Can you not do without the pivot tables layer? Replace them with array formulae. Also known as cse formulae as you have to press Ctrl shift enter to enter them. These are excellent for summarising results based on criteria

    scaredypants
    Full Member

    Just copy and paste as values?

    Yeah, I know. Just being a bit lazy – the figures I need aren’t in a nice block so there’s a bit of manipulation to do, whereas I was hoping to set up the 10 or so cell addresses just once and copy away

    I’m not sure sql is what you need either

    Oh, what do I need then ? Only other thing I can imagine being allowed to play on at work is Access

    (I don’t copy/paste 500000 lines, they just inform the pivot table(s) but does seem to make everything slow – presumably down to the way Excel “remembers” stuff ?)

    scaredypants
    Full Member

    Replace them with array formulae. Also known as cse formulae as you have to press Ctrl shift enter to enter them. These are excellent for summarising results based on criteria

    😯
    <googles>

    MrNice
    Free Member

    arrays are like black magic at first but great when you’ve got to grips with them. I learnt them from one of Stoner’s threads on here. It’s basically a permanent pivot table.

    Having said that, with that much data I’d want a database and SQL…

    mikesbikes71
    Free Member

    Sql express is a free download from Microsoft with no license implications. You are limited to a single processor core and 4 gb datafile. IIRC
    Be a great way to learn a bit of sql.

    poly
    Free Member

    SQL might be the right tool depending on how your data is structured. If you already have it installed then it might be an easy choice.

    Personally I’d use Python (and probably the Pandas library) – but depending on your IT folks paranoia / self importance level that might be a PITA. The nice thing is you can output into an excel spreadsheet for those people who have yet to see the light.

    I wouldn’t advocate Excel Array formulas because debugging them is a bit of a headache. They are great when they do what you want, but difficult to work with when something goes wrong. It may be if I persevered I’d get there – but you were looking to make your life easier!

Viewing 23 posts - 1 through 23 (of 23 total)

The topic ‘Is it time for an excel question ?’ is closed to new replies.