MS Excel / VBA help thread

Have you tried using formulas in conditional formatting? It's not easy but it might help.

Maybe a bit o/t. The cell being looked up for a Mailmerge contains the value 3.30 i have typed this myself. The Mailmerge preview shows it as 3.29999999999998

This does not happen for all records, only some.

Any ideas how to fix or is it just a bug that means i'll have to find another way of doing this mundane repetitive task?

=round()

Tried it but there's nowt to round though, I typed 3.3 into a cell but merge lookup treats this as 3.29999999998.

I could understand if I'd calculated the value, but this is a value I typed in.

Welcome to binary floating point. There are rational decimal numbers which are unrepresentable using binary floating point.

Blimey. It's like another language but I get the gist of it.

Yeah... One third isn't representable in base 10 or 2, but in base 3 it's 0.1

I have a worksheet with 45 different organisations. and 16 pivot based sheets that look at the original page.

I'd like to split the original page to get the data by each organisation.
And then select each new worksheet plus the 16 other pivot based sheets and create a workbook with new worksheet plus the 16 sheets.

The splitting is fine.
I'm guessing the select sheets and copy to a new book would require a for loop?

OH HAI GUYZ.

Not sure if you want to know this or if you already do. But, it's something I stumbled on.

I needed to build a dashboard using pivots controlled by a slicer. SIMPLES so far.

One of the displays had to use median age. Which isn't a real option in pivots. So.
I created a pivot with ONLY the field I needed a median from. I put this field as the row label.
I then defined the column it was in as a named range.
I then wrote in a cell, far far away,:
=IF(ISERR(AGGREGATE(12,6,Row_Labels_F)),"Nothing to see here", AGGREGATE(12,6,Row_Labels_F))

Now when I use the slicer to show my data I get a median age as well as all the max, mins, whatever else I'm displaying and are linked by the slicer.

Inconveniently an Excel update has deleted my personal macro book so I can't find my previous example of when I did this.

I'm looking for a macro that opens each file in a folder, copies the contents of each worksheet and pastes them into a new workbook, one after the other. I basically just want to aggregate loads of workbooks/sheets into one.

I could rewrite it but I'm a bit rusty so wondering if anyone has one lying around.

I normally use the very useful RDB merge for this http://www.rondebruin.nl/win/addins/rdbmerge.htm but for some reason it doesn't like the sheets I'm working on. Can't work out why

Cheers

There's probably a Powershell script for this, if you install the Microsoft Open XML SDK.

We're using it in an ASP.Net web app as part of an import routine.

Cheers. I found this which, in conjunction with a macro I already had, worked https://msdn.microsoft.com/en-us/library/cc793964(v=office.12).aspx#ConsolidateDataFromMultipleSheets_CopyAllDatafromMultipleWorksheets (although I imagine it's part of the underlying code for RDBMerge so don't know why that didn't work).

Hi.
I'm struggling today. (hahahahahah)

I have 3 pivots. Controlled by one slicer.
One of the pivots tells me how many different languages are spoken at one organisation.

What I'd like to do is actually return the number of different languages in that org in a different cell. the data is held in a table on a separate sheet in the same book.

So:
Click org code slicer and select new org code.
this changes three pivots and their charts on the page.

I then want in cell b6 (for example) it to return the number of different languages spoken in that org code. make sense?
I thought it would be simple.

Alternatively, if b6 could say:

countif: orgcode,b1, languagespoke,NOTBLANK

or

if b6 could look at the number of lines returned in the pivot table controlled by the slicer and tell me how many there are that are not "unknown".

thanks.

You can reference blocks in a pivot table when creating single cell formulas, but it gets a bit nasty and unpredictable at times. I've had both spectacular success and frustrating failures.

Try it, it might work for you. Alternatively, can you add it as a calculated field in the pivot table itself?

I tried the single formula thing last night, with no real success.
:
=COUNTIF(rng,"<>txt")

You can make Pivots easier to deal with by turning off 'Get Pivot Data' means it just treats the pivot as normal data not ranges, not sure if it helps, but can make formulas easier

i usually ignore getpivot, but i think that might work in this case.

Hello.
We all know I'm a basic bitch.
Good. Glad that's out the way.

I'm trying to put a procedure in place so that I can delegate the production of a report to others, so if/when I leave I know things will still work nicely. this follows on from the guy who left after programming everything (>60k lines of VBA code, no comments, no SOP to help people, nice person).

I've got a union query that joins three tables together to get all cases from 2015 - 2016.

I've got a make table from that union query that also has an ugly IF statement in to produce two new columns.

I've got a query that looks at the table generated above and only takes out columns I want (leaving behind the IF statment columns).

Can I join all of those queries together into one SQL query? Typically, this is my train of thought of having modules to do things, rather than one overall routine. Because I'm a basic bitch.

I don't want to use a macro because that generates the whole "click button do work" rather than "think about what we're doing here". So if I'm gone, nobody will be able to use the routine.

I've then got a spreadsheet that plots graphs and does stuff like counts cases per month in the different areas. This looks at the final table generated in access. The graphs are nice, the tables i've generated are laid out nicely, and have lovely sparklines.

The contents of this spreadsheet are required for a report are linked to a powerpoint document. So headers update with dates, text can be changed in the excel sheet and is then updated in the powerpoint thing.

It feels a bit overly complicated, but it works.
Extract data, run 3 things in access, close access, open excel, refresh/calculate formulas, open powerpoint, print to pdf, circulate.

Is there a way, there must be, to increase the efficency of the data table generation in access?

would expect you can combine the union and select, yes. Post the queries?

I'm so fucking basic I can't even post the code.

We all know I'm a visual basic for applications bitch.

ftfy

I get a blocked message...

Can I join all of those queries together into one SQL query?

Probably - using CASE ... WHEN statements and JOINs would be more elegant than clunky & inefficient UNIONS

If there is no relationship between the three tables, then a UNION might be the easier option, it's hard to say without the query or any knowledge about what the tables contain

Yeah. I'd really like to post something up, but it's just getting blocked.