Is it time for an excel question ?

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 !!!

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.

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

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.

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!).

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)

re

wrong tool anyway

how hard is SQL to learn ?

😳

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 🙂

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 ?

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

P.S.

Taking NHS data home. Nooooooooooooooo

You really had better be using encrypted USB drive.

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

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.

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 🙁

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?

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.

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

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 ?)

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>

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…

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.

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!