MS Excel / VBA help thread

ideally like this
count of person, person, dob, first diag, second, third, last

Could try exporting table to excel, then pivot table should show what you want?

that's what i've ended up doing....

There is a pivot function in oracle from 11 onwards i think.

http://www.oracle.com/technetwork/articles/sql/11g-pivot-097235.html

It will basically mimic the picot table you are creating in excel but i'm wondering if there is some other left join shenanigans you can do to calculate this stuff.

I'm in access.....

http://stackoverflow.com/questions/6571470/ms-access-2010-sql-query-help

and maybe cross tab?

https://support.office.com/en-us/article/Make-summary-data-easier-to-read-by-using-a-crosstab-query-8465b89c-2ff2-4cc8-ba60-2cd8484667e8

(Sorry these are place holders while i'm in this dull meeting)

I’m writing some VBA to automate a process I’ve inherited at work. It involves updating a couple of ‘master’ excel files with various bits of data from a scheduled Business Objects report which is then compared with previous year data pulled from archived reports. There is one part that has me stumped, and I’d be grateful for any suggestions – I’m fairly new to VBA so it’s likely I’m missing something obvious.

One sheet has a column on the left hand side populated with week-beginning dates. I need to find a way programmatically to look for the date in said column closest to “today’s” (as in the day I’m running the process) date and then populate a corresponding cell with a value from the scheduled report. I’ll be running the process on a Monday, apart from on bank holidays.

So for example when I run it next Monday it would find “today’s” cell (or the nearest preceding one) which happens to be A44 (4/7/16) and populate Q44, Z44, AI44 and AR44 with various values from the scheduled report.

Any ideas?

MS Access (I know) headache:

Got data on an old s.sheet but queries and updates working easier on db so I built a basic one around the table.

When importing the field "Age" it was a value not calculated based on dob, so remains static not current.

Trying to fix this using =datediff ('yyyy', [DOB],Date ()) via an append query for a new 'Age' column (deleted original as no value, but could re-import easily).

Error is about validation rules, when there are none in the db as nothing is that complex. Preview view works perfect in that it shows a year value based on each DOB in the table... Some rows blank as no DOB.

Anyone used this similarly and got it working? Cheers.

P.s. used same expression in regular query to export data as part of export to load data to email campaign sender, it works fine for that... (although rounds up some ages but hey, what difference does a year make?)

=Now() Excel function?

It could be the null values that are causing the validation errors, can you stick an nvl() around the dob to populate it with a dummy value?

Cheers, looks like it could help if used as Nz() will try it tomorrow time allowing. http://www.techonthenet.com/access/functions/advanced/nz.php

It's worked in view mode, using

Nz([DOB],0) as part of the Expression.

Sets all where no DOB to 117 for some reason.

Still doesn't update the table though when run as append. Weird.

Can you just ignore the null DOB's for now to see if it runs?

where dob is not null

I discovered the Fuzzy Lookup add-in yesterday

https://www.microsoft.com/en-gb/download/details.aspx?id=15011

I will try. Next Thurs earliest I can look at it again. Ta for advice.

OH HAI.
I have a fairly simple problem.
I've got a table of data for monthly notifications between now and forever.

At some point someone wants to say:
show me the notifications from this date, for variable number of data points on.

So. cell a2 : enter date. Cell a1: number of data points. cell a3: calculates the date n data points (from a1) from cell a2.
Simple yeah?

i have a table to do the control calculations (upper and lower and mean) and graphs to plot.
However. I've made this 18 (which is the maximum n of data points on).

I would think it's fairly easy to filter the table of calculations based on cell values from a1,2, and 3. right?

How? I've tried advanced filter and used ">="&a2 for example.

What have i missed?

excel 2010. should be fucking bombproof and not need anyone to do anything other than enter data.

Where have you entered the ">="&a2?

How the Advanced filter works is you have a sub-table with the same headers for each of the criteria you require and you reference that.

I've attached a very simple example

1 Attachment

• Book2.xlsx

Got. IT.
I was missing the header off the criteria range. D'OH.

thanks.

Quwstion: how come mine doesn't filter automatically? would like to do this.

I don't think it does without a macro to trigger it.

ARGHG. ACK. ACK. ACK.

I thought that might be the case.

It's pretty simple, you can get it to trigger when specific cells are changed, table updated or whatever but you'll still possibly have the ballache of some machines not allowing macros, etc.

yes. that's the possibility....
i can get around that by just providing instructions.