MS Excel / VBA help thread

Maybe floor the sale date to a reporting week and then use max available date in that reporting week

Two ways off the top of my head (so details might be off)

1) in powerquery join the fx rates table to a date table so you end up with every day in the date column with null in the fx rate column where there is no rate for the day. Then apply fill down on the fx rate column

2) in PowerPivot use a formula that tests the fx rate for blank(). If true use calculate to filter the date table joined to the fx table to a suitable date (e.g. the max date less than the current date where fx rate is not blank)

Cheers, I'd considered the second but seemed a bit clumsy. First option is neat, I'll give that a try.

I'm thinking of moving on from my ancient copy of Excel 2010 (Office, but I never use the other applications).

• Is there any significant downside to just using the free web version of Excel, other than you have to work on your files in OneDrive (which would be quite annoying, as I've just migrated away from Dropbox + OneDrive -> GDrive)?

• If I find that too annoying, is there any better option than either:

  • £60/year Office 365 Personal
    
  • £115 Office 2021
    
    

Both seem pretty limited - 1 user, 1 PC, esp. given I'd only be using Excel. Would like to use on 2 machines.

I don't want to mess around with dodgy too-cheap-to-be-true software keys....

I don't want to mess around with dodgy too-cheap-to-be-true software keys....

Why not?

They seem to work don't they if you go for the more know websites?

To be honest I have no idea where to go, having never bought one. And 'seem to work' is potential hassle I don't want.

I hate online excel. With a fucking passion.

Fair enough.

To clarify by seem to work, I meant no issues based on my sample size of three (my OH, Sumo and Sumos folks).

Read from this post down:
https://www.lfgss.com/comments/16716910/

But totally understand why for work or whatever you don't want to risk the hassle. My logic was it was risking £20 lost vs quite a lot of cost for a personal use laptop. I'll be doing the same when I finally reinstall windows on my laptop.

Yes!

The fucking spellchecker not being triggered by F7 is a real bugbear of mine. I basically just use the search bar for everything now.

+1 for moody keys. Also for full pro windows 10.

Can we add Mac excel to the list?

I tend to have price trackers for O365. You can get the personal for under £40 and the 5 users for under £60.

If you're using the online storage it's very good value, maybe not if not.

Excel online is missing enough stuff to be annoying for me but it depends on your use case.

Thanks (and others) for pointers. Haven't decided yet (and need to sort Win 11 licence first, having unluckily switched desktops just after my Win 10 licence (upgraded from retail Win 7) stopped being transferable...😬

I have a test sheet that I want to auto fill.
There are several device types, and each type has a rating, and that has a maximum permitted value associated with it. Eg type: 88-2, rated 2 Amp, max value 33.1 ohms.

Would it be tricky to create a drop down menu for the type? Eg 88-2, 88-3, 60898 etc
And then have another cell that has a drop down menu for the rating: 2, 4, 6, 10 etc
And then have a 3rd cell which automatically produces the maximum value associated with those two values.

In my mind (not knowing anything about it), it would be something like:
IF A1=88-2 and A2=2, then A3=33.1

And then finally, a fourth cell where if an inputted number is greater than A3, the cell will go red to indicate a failure.

It seems like none of it is particularly complicated, but I don’t know where to start.

It's definitely possible (although realistically most stuff is in Excel depending how much effort you want to put in).

This may be what you're looking for with the drop-downs
https://trumpexcel.com/dependent-drop-down-list-in-excel/

Not quite sure where A3 is coming from, whether it's a formula or an existing set of data or something.

And something like this for colouring your cell red
https://www.ablebits.com/office-addins-blog/excel-conditional-formatting/

I went on a ramble about making all the possible combos of a, b, and c in a reference table and then using filter based on cell value to spit out the answer.

that's how much i dislike drop down menus.
you could still use if and statements but type them into cells that the statements reference rather than a drop down.
that's again how much i hate drop down filters.

So the value for a3 wouldn’t be a calculation, but rather a point taken off a chart.
I’ve included a picture of the chart for 88-2, and you can see below 2A, the value is 33.1.

The value for A3 is just a defined number that is a combination of type and rating.
On excel would it just be a case of putting all the values into the sheet somewhere, so you could say if a1=88-2, a2=2A, a3=z1 (z1 being where the relevant figure is recorded somewhere on the sheet).

1 Attachment

do you have all the combinations of a1 and a2 that would yield the value of a3 written down somwhere?

Also: what is it you're doing? someone else might have done it already and you can just rip it off

Also: is 88-2 a text value or is it =88-2 which is 86

Is it this:
https://www.dungannonelectrical.co.uk/dun1-shop/pdf/loop-impedance.pdf

MAXIFS is your friend here

column a = standard
column b = rating
column c = resistance

turn that into a table and then
=MAXIFS(Table1[Resistance],Table1[Standard],G3,Table1[Rating],H3)

where you put in the standard in cell g3
and the rating in h3

That’s the table, but that’s also reminded me that there are more variables than I thought.

I’ll have a bit of a dive into MAXIFS and see how I get on. It’s mostly just because there’s only so many times you can manually fill something out before your brain gets annoyed, when you know there must be a way to automate it.

The data entry will be the longest thing. If you were to take the table I linked to then I would do this
Column A = Type = fuse or circuit breaker
Column B = standard
Column C = rating
Column D = resistance

You could use maxifs or you could use filter (https://support.microsoft.com/en-gb/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759) which would let you say
"for type and standard at this rating what is the resistance?"

or if you wanted a flashy dashboard you could use a couple of slicers (https://support.microsoft.com/en-us/office/use-slicers-to-filter-data-249f966b-a9d5-4b0f-b31a-12651785d29d#:~:text=Click%20anywhere%20in%20the%20table%20or%20PivotTable%20for%20which%20you,every%20field%20that%20you%20selected.)

Thanks this is really helpful. I’m deffo keen to try and implement something! But with my highly basic skills, this is probably more involved than I realised. I’ll report back next week to see if I can get anything that resembles a working sheet.

If you've got lots of standards you have to work with for things, then most of your time will be transcribing them into excel.
Are they particular standards? Like BSI?
It might be worth getting in touch with them directly and asking for them as a csv rather than having to do it all by hand.

That’s about the only set of data that we have to grab the regs book for, all other data is recorded on site on paper and transcribed. There might be minor gains to be had if we could sync the data on the sheet with existing data that’s on the site database - but it’s been attempted already and was a mess apparently. I’m just a subcontractor who is too lazy to find the book every time.