MS Excel / VBA help thread

I have some training to organise at work and I'd like to use excel, however I'm looking for some relevant tutorials as I'm not sure the best way to do it.

What I'm trying to achieve is the following: I'd like to be able to generate a 'random' scenario, pulling from a database. Essentially, my colleagues will be answering calls from two potential cohorts of customers, within these cohorts there will be additional scenarios and particular requests my colleagues will have to answer, etc.

Does anyone know a good reference guide to do something like this?

Additionally, and this is more of a 'nice to have' thing, I'd like to be able to output a form my colleagues have to fill out as they do these mock calls, so we can gather feedback and identify areas of weakness. Again, any good guides on this?

Cheers!

pulling from a database

Do you mean a pre-existing 'proper' DB, or is creating this part of the project?

Creating it would be part of the project

In Libreoffice Calc, how do I return the hyperlink in a cell?

e.g. A1 has the value "foo", but is linked to "http://www.google.com" - How do I get cell B2 to show "http://www.google.com"

How doeds it work in excel?

https://excel.tips.net/T003281_Extracting_URLs_from_Hyperlinks.html

It needs vba and a custom formula.

I don't think there's an easy way to do it if they've been created using the Insert Hyperlink other than a macro.

If they've been created using a formula =HYPERLINK() then you can use =FORMULATEXT() to get the contents and trim it using search or similar.

Cheers both.

I exported the xml and got them a different way, because ain't nobody got time for macros no more.

Editing the XML also allows you do tricky stuff with URLs that you can't do inside Excel. I think it's possible to have two links in a single cell, for example. Not to say you should.

https://eriksvensen.wordpress.com/2020/01/15/quick-powerquery-trick-get-duration-days-between-two-dates/

is good.

Hey hivemind

I am struggling with excel and was hoping someone could help. This is dumb, but i cannot get it sorted.
I have copied and pasted dates (from my bank statements on a pdf): It starts off 29 January 2021
and copies as 29 January 2021. Great. BUT some go from 30 January 2017 which swaps to 30/01/2017.

Anything in the dd/mm/yyyy format is not recognised as a date. And it is not possible to format that number. NOTHING ive done has worked.

So i have 2 sets of number ranges.

03 February 2021 - format that can be formatted and put into age sequence.
03/03/2015 - non formattable and seems to to be in the sequence of the day (ignoring the month and year)
23/05/2017
25/02/2015
*I have (obvioulsy) tried: Format Cell>Number>anything else, and that will remain as 30/01/2017
*I have tried to use the format painter from everywhere including both the working date as well as the other columns.
*I have tried changing the format to custom then back to something else.
*I have tried to protect and unprotect the sheet

• datevalue(25/02/2015) gives me an error (guessing now)
  
  

Is there any reason why i cannot format it? All i want to do is sort it in cronlogical order.

Anyone ever have an unformatable field esp a date?

stupid thing to try... add another column and then add zero to each date, can sometimes force it to recognise dates as a number rather than text.

Excel is awful for dates, it always tries to be smart and autoformat values and invariably causes some fuckup.

I'm assuming the values you're pasting are all in a consistent format of text like "29 January 2021"? If so, I'd copy and paste them AS TEXT into Excel, then use DATEVALUE in another column to convert them all to a date. (DATEVALUE converts text into a date, not a date into something else, which is why you're getting an error.)

Once you've got everything in a column, and Excel is recognising them all as dates, you can sort in chronological order.

Confusingly, the number format in Excel is just a display layer, you can change the number format or date format without affecting the underlying value. Which sometimes makes it hard to see what the underlying values are.

seems to to be in the sequence of the day

Makes me think there's a space before or after it (or some other annoying value that's not showing easily), and it's seeing it as text. Can you trim the contents before trying to format?

datevalue(25/02/2015) gives me an error (guessing now)

The value needs to be a string there, i.e. datevalue("25/02/2015")

You could always try rebuilding the string...
If annoying field is in A1 and looks like 23/05/2017

try
=DATEVALUE(RIGHT(A1,4)&"-"&MID(A1,4,2)&"-"&LEFT(A1,2))

then once you have a separate column of 'good' dates then you can copy/paste values back to where you need to remove the formulae and just keep the good dates

Text to columns and identifying that column as date fixes that most of the time.

add another column

done

and then add zero to each date, can sometimes force it to recognise dates as a number rather than text.

didnt work :-(

speaking of dates, why the fuck is adding a hierarchy in apivot. fuck off. i don't want it. how do i turn it off?

I'm assuming the values you're pasting are all in a consistent format of text like "29 January 2021"?

yup

copy and paste them AS TEXT into Excel

Copied to word and copied back. nothing.

then use DATEVALUE in another column to convert them all to a date. (DATEVALUE converts text into a date, not a date into something else, which is why you're getting an error.)

error value

Once you've got everything in a column, and Excel is recognising them all as dates, you can sort in chronological order.

sadly no

Confusingly, the number format in Excel is just a display layer, you can change the number format or date format without affecting the underlying value. Which sometimes makes it hard to see what the underlying values are.

can you paste into notepad and then back in again?

(post dredge)

Text to columns and identifying that column as date fixes that most of the time.

not this time sadly

can you paste into notepad and then back in again?

(post dredge)

Did the whole lot onto notepad. The amendable version has no issues swapping formats. The borked ones are still borked.

Ive even copied it to a new sheet. Fresh column. Copied from the Notepad to the new sheet.

I'll do it for you, send us your bank details :D

As in the grouping function when it groups weeks into years etc? If so then yes you can change the default. In fact you can make and then save a default pivot table format, turning things like subtotals etc on and off.

Can you share the workbook (obviously without the financial data...), I have come across most date issues at some point in the past, but there are so many it is hard to diagnose!

Also does your bank give you other download options?

Must be some bad data in there somewhere. Some non visible whitespace or similar that's breaking the conversion

@ChainBreaker if it is white space then =trim(data) may remove some of it, if not you can use the substitute function as well.