MS Excel / VBA help thread

I've also realised, for what I want to do (pivot tables, slicers, graphs), that 42 tables is sliced the wrong way.
It only requires tables for the 7 things.

still. eh.

Have you considered using a database for your database rather than trying to achieve database using a spreadsheet?

I prefer, and so do the users, the "click this slicer button" to make changes and show data.

Here's my weak thinking

data on select.survey
data needs to be stored.
data comes out as a single table.
store in access
slice up
link excel to access (because graphs and slicer control)

Is there anything wrong with using excel to do the graphing here?
Most of the users will want something they can open up, knowing the data has been done, and they can just click some buttons and get some graphs.

Have you looked at things like Power BI? https://powerbi.microsoft.com/en-us/

I am a drone in the civil service. I work with what I'm given. Not with what might be out there.

The desktop version is a free office add-in if you can find someone friendly in the IT department with administrator access

LOLZ.

I've made a request...

Watch out, if you can get it all your users might need the same

I think I've been here before.
Request swanky add on.
Realise the rest of the world is still in 2010. Or worse.
Remember why I'm doing things the way I am.

Asked. Got 64 bit version.

Can't connect to my access db, because we're running 32 bit. Have asked for 32 bit version. will see what happens.

i can't wait till we upgrade to office 2016. The future looks clean.

You're entering a world of pain.

Who's crazy idea was this?

I was sort of happy doing it the way I was doing it.

(if it works I'll be happy, i get to use it for a bit, add it to my cv and then cry)

Also: when can i say i'm a competent user of powerBI?
when I've created a dashboard and a report from a linked access database?

Planning my escape and need to add skillsets.

whats up with this formula:
=VLOOKUP(A4,Modules!A2:B243,2,0)
A4 is a student ID.
Modules is another sheet with a list of IDs and Modulenames (2 columns)
I want the modules matched to the IDs on sheet 1.

Need to make the range absolute

with some dollarsigns?

Can one student ID have more than one Modulename linked to it in the Modules sheet?

The range should be $A$2:$B$243 (so that when the formula is copied to other cells it still points to the same range).

https://support.office.com/en-us/article/create-a-map-chart-f2cfed55-d622-42cd-8ec9-ec8a358b593b

pow.
excel 2016 update to do maps??

Do you have one set of IDs formatted as text and another as numbers? A common and annoying error.

This was it - I also needed a weekend off. A fresh look monday morning and it all worked great. Thanks!

I'm sure it's in here. But I can't find it.
I get answers to a survey. It gives me an age range. When I open the CSV guess what happens to the age range? Yes it's a date now!

How do work around?

Ive got a table in another worksheet I'm using as a reference list for a find rplc array for loop. I've tries, but not successfully, to put the serial numbers and what I want them replaced with, but its not working too well.

Rather than opening it, go

Data
From Text

and import it. You can set the format of each column.

I have 150ish columns. If i import as text, the data values come in as text.

I did this:
i've got a spreadsheet with a table in it, with two columns, Find and Replace.
All the bits i want to find and replace go in there.

the VBA looks in that table and changes all the field namesin the headers to something more manageable.

then inserts a column next to the fucked up age range, pulls out the month and date from the fucked up age range and plonks it in as text. it then fills down.
then it formats
then it splits by one column
saves the book
tells me how long it took to do that.

Any suggestions on working out which bits of my spreadsheet are taking ages to calculate.

I assume it's the vlookups but (for this and future work) I'd like something that actually told me how long different parts of a workbook take to calculate rather than me just generally knowing which formulas take a while to calculate.