Microsoft Excel and Other Office Apps - Qs&As - The Bear and Badger

Facewon

ffs. It's gonna be vba all the way.

While text boxes technically have a formula bar, they literally only work for cell references. Nothing else.

GooberTheHat

Instead of putting a formula in a text box, will a workaround do your job ?

If yes then try the following:

Put your required formula in a cell (preferably select this cell just behind your text box).

Once the formula gives you your desired result, create a text box and

1. click inside the textbox

2. Now click in the formula bar

3. click on '='

4. Click on the cell in which you had written your formula

5. Press ENTER button

The text box will have your desired result

6. Click on the cell containing your formula

7. Select 'white' as the font color (so that it becomes a kind of invisible

We are doing this workaround because as per my knowledge, a text box can have at max a formula just linking to a cell to capture the value of that cell. Beyond this, a text box cannot have other normal/complex formulas which we usually write in excel.

Hope this Helps.

Facewon

urrrgh. VBA makes my brain hurt. DO NOT UNDERSTAND HOW IT WORKS AT ALL.

acemuzzy

There's a code thread if it heads in that direction...

Haven't done VBA in s while but happy to try to help...

GooberTheHat

Facewon wrote:

urrrgh. VBA makes my brain hurt. DO NOT UNDERSTAND HOW IT WORKS AT ALL.

Does my work around not work?

Facewon

GooberTheHat wrote:

Facewon wrote:

urrrgh. VBA makes my brain hurt. DO NOT UNDERSTAND HOW IT WORKS AT ALL.

Does my work around not work?

Yes, yes it does.

At least on first set up. Because data is dynamic, need to see what it does with different responses.

I posted mine before seeing yours.

Will hit the code thread at some point. Thanks muzz. (There's gonna be something where it's a sheet that only I use or I use it to spit something out and VBA will be the answer.)

Facewon

Crosses appendages in hope

Facewon

In better news for excel, linking data from websites is the new hotness for me.

My covid sites spreadsheet is now a thing of beauty.

Hodge360

Thought of you when I saw this:

https://techcommunity.microsoft.com/t5/excel-blog/excel-reddit-ama-on-march-10-at-10-am-pt/ba-p/2108687

Give them hell!

Facewon

lol. Might still make it.

Why can't I do proper forumlas in text boxes? Why do they have character limits when linking from other cells?

Why do numerical answers from forms not register as numbers?

Why aren't searchable drop downs built in?

When will cool 365 formulas hit desktop?

So many.

Meanwhile, instant massive fan of Onenote. Using it to clean up case files/notes which are currently a mess for one of our areas. They better like what we've done.

(I've basically transferred/linked everything I'm working on into a notebook so I can keep track/access more easily.)

Facewon

Posted below to excel forum.

Halp.

Best method to check changes to a monthly database
Hi All,

I feel like I'm missing a really obvious answer to this question, but all the seemingly obvious answers I have thought of have run into snags.

I have a report of current clients that I can run. I want to be able to run it monthly, paste it into a spreadsheet and highlight any changes to it from previous month. Whether that's names removed, added, or simply changes to dates/info. (For instance, if they update an accreditation or something, so we pick up that a Drivers Licence expiry has been updated.

List will always be around 30 names long, so not that massive. Although we're talking probably 30 columns of info.

Simple conditional formatting runs into trouble because lots of things can have the same date.

Match was looking good, and would allow me to filter by the answer to that, but "blank" cells are giving me grief. (My usual solution is to paste data, but then have it populate a second sheet where all the formula work happens, so that the on going workflow is simply cut paste, look at the next sheet. But of course a cell referencing a blank cell isn't blank, it has a formula in it.)

"=AND(MATCH(A2,'Last Month'!A2,0),(MATCH(D2,'Last Month'!D2,0)),(MATCH(AH2,'Last Month'!AH2,0)))"

That's the current formula I'm using. (I tried it with table references too, with similar issue, when it tries to match a blank cell, it looks at the second cell and sees a formula, so says it's not a match.)

Am I missing some really obvious solution?

GooberTheHat

Have you tried spreadsheet compare (from the start menu), or Compare Files in excel itself?

Facewon

Oooh. That sounds cool.

(I think I may have found a solution that uses textjoin which will mean it can be filtered and sorted anyway, but an inbuilt solution would be nice.

handsofblue

acemuzzy wrote:

Use & not CONCAT is my number one Excel tip.

Learnt this one recently. Improved my excel quality of life.

In other news, there's a push at work to host all documents on Google drive. This might mean I'm gonna have to get pretty friendly with Sheets. I hate Sheets. Filter function is borked.

Facewon

Yikes, that'd be tough. I'm sure there's some ok stuff in sheets, but it's close, but different enough that a bunch of second nature right clicks, ctrl options etc are just off every time I use it.

Depending what you create, can you not create in excel, then just clean up in sheets to make sure everything still works?

Facewon

Goddammit our client database shits me.

None of the reports, bar about 2, are remotely useful for basic info.

You can't do custom reports either.

Apparently company paid for the api which means there will be a lot of stuff coming, but they're miles behind.

Out of the box, the database is very much by client/case, as opposed to region or department.

So if I want a list with all active clients for a program, with their important milestone dates, or warnings, or other stuff that is time sensitive and needs to be flagged, I'm shit out of luck.

Blurgh.

handsofblue

Thanks for the commiseration, Face. Agree, it's stuff like adding and removing columns in excel just adjusts the browser magnification in sheets. Really hard things to unlearn. Would also have to set up the system of linking spreadsheets again.

Unfortunately, most my excel docs are active worksheets rather than reference docs - I'd end up dling and uploading them constantly, and google drive is a fucker as well. Ah well ... batten the hatches, I'm fighting this one!

Facewon

https://docs.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference
Cannot work out the syntax that functions want in power automate.

Trying to set up various ways to create a checklist of things that need to happen every time a particular type of email comes through. The easy part, email with particular subject line comes in, create a To Do List task.

Can stipulate the particular list in Tasks/To List, and it allows for adding in due dates and reminder dates, but I need to do functions stuff to get it to work. I can tell it a specific date, and then do "addDays (2)" but I cannot work out how to get it to reference the email received date and time from dynamic data.

Facewon

Goddammit. So to get charts to update live with new info punched in it's best to create a Table and link the chart to it (as opposed to a pivot which just captures the data once and that's it).

Table I'll end up with is gonna be 3x3 or 3x4. I need to get Monthly counts from a larger table that has incidents by date and various criteria.
There is no way around the date being set out like so:

6/12/2020

So even if I change format, for purposes of countif formulas, left, clean, right, etc the month is in the middle of this. So I don't know how to get a count of the incidents in the month.

Doing my head in.

Facewon

Ok, so if I treat it as a number then I can use 1900 date format to do > and < to work out months, I guess, but I can't get that to work within a countif. Fuck.

Facewon

No need for crazy date set up, just use > and < to find date ranges.

Had to set up a bunch of columns to check months, which isn't elegant like folks seem to want, but the job gets done.

Facewon

I love revisiting old sheets with new knowledge. Just cleaned up an old one and boom, simple adjustments and it's tasty and easy for folks to use.

Fuck pivot tables though. All about manual charts.

(also just noticed how easy it is to add "to do" tags in onenote and then see them in a list. Can be from multiple sections or pages too. So perfect for working out what I'm doing each day.)

acemuzzy

Pivot tables are amazing.

Facewon

(I came back around to them quickly.)

Facewon

I may be stumped.

I have a table, list of clients, with various info, including a reference number. It's per event, so Client name could appear more than once. (reference number will stay the same.)

I have a second page which allows search by name, and presents info in a nice readable way. (Unsure how it'll work with multiple instances of name, TBH. Might need to be able to select multiple names.)

I want to be able to add Notes in a further column on the original table, potentially next to multiple instances of the same name. And then have those notes appear on the second page.

hmmmmmmmm.

Facewon

Well, I have largely unstumped myself.

Having now worked out how to present pivot tables like they're regular tables (tabular under design) I've found lots of neat ways of twisting things.

Also realised that using the get data options is generally a better way to extract a table from one sheet to another, even in the same workbook.

Basically, all the crazy formulas I've come up with I've now worked out how to do using built in options in excel. This is both good and bad for my mental health.

cockbeard

hahahaha, welcome, I'm told it's a good place to be, and most of my mates who can drive excel get mad love from colleagues

I can't, I'm still the cunt that breaks Excel by them adding incorrect source data and I ingest it automagically

Facewon

ffs. Thought I had a clean solution to something, but apparently not.

Client list, includes the below dates

Wanted to create a splicer to filter by Month. Many ways to do this, but thought I had an elegant one, as this is coming from another table, when I added the data, I created copies of these date columns.

Changed the format of the copies to present as above.

But still, if I use the splicer, it filters to a single date (2 dates, if there's 2 identical dates)

When I filter using the dropdown on the table, it works properly...

Why, goddamit?

(I realise there's formula ways around this, but I wanna know why this doesn't work.)

GooberTheHat

Are you talking about a pivot chart/table splicer? If so there should be an option to have it splice on day/month/quarter/year.

Facewon

No, I was hoping to not have to go to yet another pivot, you can add splicers to any table.

Howdy, Stranger!

Categories