I'm struggling with index match simply because it's easy to minus say row 49 from 49 across different sheets but when country takes over another in one sheet, i haven't yet figured how to have it fill down but automatically recognise that the value in row 49 in sheet two has moved to row 48. Shall perservere.
strawberry float it, the vlookup function's good enough.
Anyone completed a good Excel course before? So many online ones that it's difficult to choose. Plus I'm not sure what level to sign up for, I'm not great but concerned that beginner ones would be like, 'here's how you save a file and type in data'.
Don't bother too much with an Excel course, just learn as you go. You can find anything by Googling, Excel is very popular. Maybe just watch a few videos on how to work Lookups etc, Pivot Tables. The basics.
Qikzen wrote:YES YOUR SPREADSHEET YOU HAVE 20 WORKSHEETS ON WITH THOUSANDS OF CELLS ON ALL LINKED TO DIFFERENT SPREADSHEETS IS SLOW. strawberry float YOU STOP TELLING ME TO FIX IT I CANT.
One of the monthly reports I used to receive took 1 minute 40 seconds to open on my laptop. Once the laptop gets over the shock of what it is opening, it normally runs fines.
The difference is, you don't tell support to fix that right? You know that it's an issue you can't fix.
I rarely tell support anything because they are a bunch of clowns. Most of the time I will fix issues myself.
I don't know who your support are, but it's USUALLY the users who are the clowns. Just saiyan.
Nope 100% support people are the clowns. They don't know anything. For example if they did, why would my Excel crash so much? I just want it to automatically send emails full of pictures, how am I asking too much?
Harry Ellis wrote:+1 for Index and Match.
My job basically requires me to live in Excel.
I find this hard to believe. I have a hate/love relationship with Excel. Although the hate is mainly due to the crashes/slow downs, which is due to support IT incompetence.
I've started using google sheets a lot more compare to excel, basically the same but the query tool in sheets is very useful, a bit like SQL but I find it a lot easier for trial and error as well as there being a lot more help online with videos/forums etc.
But I do wish they actually taught you all the key excel functions (pivots, lookups etc) at school.
I love Excel. It’s a very powerful piece of software and there’s always more to learn.
I’ve used MrExcel.com quite a lot in the past and can recommend it.
Re courses, skip any basic course as that will be new/open/save/close type stuff. Intermediate is where you should look if you really want to go on one but I find self teaching through trial and error and the occasional YT video or MrExcel thread helps me through.
I’m on iPad so can’t view your spreadsheet but the Index & Match, VLookup tips above are likely to be a good bet.
VB is awesome (I did a course 12 months ago) but never get to use it so have forgotten quite a lot. VB will sort it out for you I’m sure but someone else will need to help you I’m afraid. Sorry.
So anyway, how many excel whizzes in the house without a spouse. Sexual favours shall be provided for a solution.
I’m trying to write a macro that will automatically update my spreadsheet for week on week growth. To give an example, for the UK the last week's figures were 745, hence why there’s a 6 as the last number - it grew by 6 from last week.
To calculate the growth between sheets I’ve done a simple SUM calculation. I wrote a macro to do that too but realistically just doing one cell and filling down is quicker just now - what I really want is a macro that would take into account changing positions in the league table from country to country. Is that even possible?
Again to give an example, if Poland gained another user they would take over the Netherlands but currently my macro doesn’t realise this so would give incorrect growth figures for these two countries.
I’m hoping it’s not too difficult, just I’d forgotten that macros even existed in Excel until this morning. Any help would be mad appreciated.
Thought I'd give this a look and just trying to get my head around it.
First thing: I can't see a difference between the pictures, did you mean to only show it once but duplicated it by accident?
Secondly: Am I right in thinking this is one of many very similar sheets, one for each week, so each week the figure on the far right changes and basically column 5 is a sum of "previous week total (from previous sheet) + number in column 6"
It's meant to be the same image I was just struggling to get it to show on the forum in any form.
Yeah there's a new sheet for every week - the client provides us with figures for column 5 so column 6 is the only one we need to deal with, which is the week on week growth. The simplest manual way to do it was to subtract this week's figures from last week's, then boom that's the growth. Was just trying to write a formula that would take into account when countries change rank so i could easily fill down the function and it would update automatically. Don't spend too much time thinking it through though, my Vlookup seems to do the job to an ok standard.
New data goes in the "RawData" sheet. Then on the "WeekView" sheet you choose the week you want and it put the country in order and tells you their total and how much was added that week.
Have a look and let me know.
Jesus man that looks like it took a while, i hope it didn't! That's awesome though gonna be studying it for a while to get it nailed down for the real stats tomorrow but even just looking at the functions used is interesting as.