MS Excel / VBA help thread

It might not help the performance (and vlookups with lots of records do slow things down!), but you could write some VBA code which would reset your ranges after pasting new data in and then refresh any pivot tables that referred to them at the click of a button.

How big is the Excel file?

index/match with dynamic ranges?

LOL M9 R U HI?

I thought he was looking to speed it up, not slow it down.

Never said anything about using with dynamic ranges, but in general index/Match is a much better function than closeup which sucks

I have no lookups. It is just coutnifs. I have to export from three sources. Then build something that reports across all three. In this way I should be able to plonk data from each source into its own sheet, have calculation sheets look at the respective sheets, pull those counts into graphs and a summary table.

This is only temporary until the data sources are sorted out and it all comes out of one pipe. I tried the offset and it mucked up the calculations. It was nearly home time, so I'll look again today.

Hmmm. I decided to change one named range to OFFSET to try and highlight the problem.

Here is my conundrum:
CONF ='Jan11-Apr15 Cases'!$F:$F

Here is the formula i use it in:
=COUNTIFS(Infection,'DM NA calculations 2015'!R21,CONF,"Suspected")
This returns a number I expect.

when i create CONF2 =OFFSET('Jan11-Apr15 Cases'!$F$1,0,0,COUNTA('Jan11-Apr15 Cases'!$F:$F),1)
and use it in
=COUNTIFS(Infection,'DM NA calculations 2015'!R21,CONF2,"Suspected")

I get #VALUE!

Is it because there are blanks in the range?
I've tried using (from http://www.ozgrid.com/Excel/DynamicRanges.htm)
4:Expand Down To The Last Text Entry

In The Refers To Box Type: =OFFSET($A$1,0,0,MATCH("*",$A:$A,-1),1)

And that no work either.

I think it's probably because your "infection" and your "conf2" named ranges are no longer the same size.

Assume that "infections" is some thing like 'somesheetname'!A:A, it probably needs to be a dynamic named range too which will always need to be the same length as conf2

ah ha. that old countifs chestnut of needing to be the same size range. ok.

Also, blanks in your columns could mess it up as the counta in the named range formula may not pick them up.

I think I started you down this track with an idea that large named ranges could slow down the countifs. Apologies if it's a faff. I don't know the size of your data, but it might be worth trying to see if changing the named ranges from F:F to F1:F10000 (or something) actually does speed up your sheet.

Yeah. It's not working so well.
I'll come back to it.

The data isn't complete, there are blanks all over the shop.

It's a once a month job.
Export data. Import data.
I might build the graphs in a separate book so I don't have to look at the kalkilation sheet all the time.

Hi!!!!!!!
Me again!!!

lolz

Seriously.
I'm looking at the data extraction routine (from Access). It appears to be "run query 1" then "run query2" then "run query 3" then "run query 4".

How do I link them together?
For example 1+2, which simplistically deletes all data from 2013 in one table and then adds in the data from 2013 from another table.

How do I combine (or where can I find some info about how to do it pls):

Delete [table1].[Field1]
From [table1]
WHERE ((([table1].[Field1])>#date#));

with
INSERT INTO [field1], then lots more [fields]
FROM [table2]

Say if you were creating a page of charts. In excel.
You know, to create a panel chart. How can you make sure all the charts are all in line vertically and horizontally? quickly?
does align vertically and horizontally actually do that?

Macros

Add new action
Open query
select query name

Yes.

Also, dragging a shape object while holding the key will sticky align to cells

Hello. Me again.
I have a quick question about embedding charts in publisher.
It's simple right?
As you may have worked out, I export data from access into excel, and then chart it all nicely, and then. Well. I've set up one work book in page break view and have arranged charts on pages nicely, so that I could print out from excel as a pdf.

However, it might be nice to see if I could whack graphs into publisher around a template.Then the process would be export, charts would update in excel, and then when I open publisher. BANG! they'd all be updated as well.
and this would have the advantage of having all the organisational branding and words around it in a nice fashion.

What have i not thought about?
Is it the updating of charts in publisher?
The data should be hidden, so if I print as PDF that'll do that right? Nobody will be able to drill into the charts.

What have I missed?

That easy using access macro thing. That's quite nice isn't it?
I've managed to massively simplify the export of data into one spreadsheet.
Thanks.

I don't know how many people would use this, but I found this code very useful this week:
http://peltiertech.com/change-series-formula-improved-routines/

I've created a 2 panel charts, one of 8 graphs and one of 20. I've replicated this for 6 boroughs in London.
As you can imagine trying to update the series in these graphs was driving me nuts. I found this code and it sorted it all out very quickly.

HIIII GUYZ.
ME AGAIN!!!111!! lol.

I've linked an access table to an excel workbook. If I define/name some formulas in the table in excel, will these work in the same way as a normal defined/named formula on a normal sheet in excel?

I ask because a quick google tells me names / ranges don't go the other way (excel to access) and would quite like to be able to link to the access tables in excel, refresh, and have my formulas do their thing.

Right.
Currently tearing my eyes out of this.

I have a number of schools. I know the age range of the students.
e.g.
0 - 11
low age = 0
high age = 11

I have categories
PreReception
Reception
Yr 1 Yr 2 Yr 3 Yr 4 Yr 5 Yr 6 Yr 7 Yr 8 Yr 9 Yr 10Yr 11Yr 12Yr 13
FE/HE

these are
Min age Max age Median
0 3 1.5 PreReception
4 5 4.5 Reception
5 6 5.5 Yr 1
6 7 6.5 Yr 2
7 8 7.5 Yr 3
8 9 8.5 Yr 4
9 10 9.5 Yr 5
10 11 10.5 Yr 6
11 12 11.5 Yr 7
12 13 12.5 Yr 8
13 14 13.5 Yr 9
14 15 14.5 Yr 10
15 16 15.5 Yr 11
16 17 16.5 Yr 12
17 18 17.5 Yr 13
18 99 58.5 FE/HE

what i want to do is quickly populate

school name and all the categories it will have based on the age range.

e.g.
School Age rangePreReception Reception Yr 1 Yr 2 Yr 3 Yr 4 Yr 5 Yr 6 Yr 7 Yr 8 Yr 9 Yr 10 Yr 11

     0 - 11 0   TRUE                        TRUE             TRUE   TRUE    TRUE    TRUE    

I've got some inkling about median and something.

Or is there an iterative thing?
or?

If you only have those two bits of data (low age and high age), I'd do it like this:
=IF(D$5<$B9,FALSE,IF(D$4>$C9,FALSE,TRUE))

1 Attachment

Looking at it again you probably wanna tweak the logic to get the categories the way you want:
=IF(D$5<=$B9,FALSE,IF(D$4>=$C9,FALSE,TRUE))

Example attached in case its of use

1 Attachment

• school example.xlsx

Thanks. Thanks VERY much.
I was unaware I could do the : IF TRUE, "VALUE", IF FALSE "ANOTHER IF"

thanks. That's really helpful.

I'd been trying: If low age <Min age, 1, 0 things and then not being able to make the upper boundary work prperly.

"ANOTHER IF"

Your life is now going to be blighted by nested if statements. Enjoy.

Have you considered alternative tools rather than (ab)using spreadsheets for everything?