MS Excel / VBA help thread

Actual and potential spends over 12 months grouped into ranges

0-10000 - 5 x accounts
10000-20000 - 5 x accounts
20001-30000 - 5 x accounts

Ok.
You can do this a couple of ways, I think.
If I’m teaching you to suck eggs right now i apologise in advance and I’m not trying to be a patronising prick.

You can use ifs or a lookup to define the ranges of your spend (like we did a couple of posts back?). So in a cell next to your actual and potential spend you’ll have the grouped value.

Then in another part of the spreadsheet or another sheet you’ll have your groups of data (100001 etc) and just use a countif to look at the field with the group category.

Your groups you’re using aren’t real numbers. The row formulae aggi gave you (or the sequence fill) are concatenations of two values to form a category. You’re moving a continuous variable (the price) into a categorical variable.
You can also do pivot grouping to get you there as well.

There’s pivot table grouping too.

https://www.myonlinetraininghub.com/excel-pivottable-grouping

Brain melt over a simple formula. Thinking sumif/xlookup combo but might be overlooking something simple.

180 rows of financial lines

Column A has category check values b, c or m

Column C has sub category check values b or e

Column E-P has £ value per month Jan-Dec respectively.

I want a mini table of 3 sum totals for the categories be, ce of me.

Not a pivot table ideally.

Brain has got stuck. Any ideas?

Helper columns for be, ce, me, and then a pivot table.

Can you use the filter function?

So filter original table where A = C and B = E, then sum.

Or sumifs(A=C, B=E, column to sum)

Ideally, I'd unpivot the data and then put it in a pivot.

But FILTER should do it (obviously fine tune as desired):

=SUM(FILTER($C$3:$N$31,($A$3:$A$31=P2)*($B$3:$B$31=Q2)))

I don't think SUMIFS works over that kind of range.

1 Attachment

Is it possible to have a recursive LAMBDA function without first defining the LAMBDA formula you want to call in name manager?

I'd like it to be a one line formula that other people could use without having to work out how to import the function but I can't quite see how you'd do that.

Thanks all, looks like @aggi option should do it, appreciated. Thanks @Drakien & @Chalfie too.

I've never used this

but this https://www.ablebits.com/office-addins-blog/write-recursive-lambda-function-excel/#:~:text=Basically%2C%20a%20recursive%20function%20works,complex%20problems%20with%20no%20coding.

or this
https://www.reddit.com/r/excel/comments/pcnpit/is_it_possible_to_create_anonymous_recursive/

Cheers. This is definitely the question I was asking which is further than I got in my googling. Now to see if I understand the answer.

I think, not sure, but it's kind of dax-y when I think about it:
https://www.sqlbi.com/articles/variables-in-dax/#:~:text=You%20can%20define%20a%20variable,you%20provide%20the%20expression%20itself.

Out of interest what are you using the lambda function to do?

Just playing about with it at the moment. Looking at a function to remove all letters or numbers or whatever from a string as an example.

It's made me think about the data cleansing I do in PQ and how I could do it more efficiently!

If you can do it in PQ I'd keep it in PQ. Much easier to track and keep on top of than random formulas.

Although this kind of stuff seems ripe for the new Python integration that is being introduced (although I would have to learn Python first).

Edit ignore - data validation drop down cell + xlookup has worked. Ta

I have a 'form' in excel from finance. I need to complete multiple times quick filled with data from an excel table. They won't accept return in word, must be excel.

My solution is to create a check cell to allow me to control which row is being looked up.

E.g. I type 1 = looks up A1, i change it to 7 it looks up A7. I can file >;save as to create each copy for submission.

I can't get this bit working even though it seems simple though. Can anyone help. I think it might need =Indirect() but I'm not getting anywhere.

Can I gripe about UpWork here?

I created an account on UpWork today, I wrote the in-depth detailed friendly description about a job I wanted done, I verified my gmail email and phone number, and my account was immediately blocked for somehow violating ToS. I’ve sold on eBay, it’s not that different, so I wrote to UpWork to request more info. They replied quickly requesting a Govt photo ID, a video with the ID, and something else I’ve forgotten, and they’d get back to me within 48 hours. I wrote back saying the process is more than what it takes for me to open a bank account, they’d have my details with my payment card anyway, please cancel my account. Waste of time.

I just wanted a solution for transferring content from several template-based word documents to another template-based word doc., in a manner more efficient than our current copy paste. :(

(Rant.)

Found a practical use for the Time() function today. Quite useful.

Might be a shot in dark but would bing/gpt or even power automate AI builder thingy offer solution methods via some careful prompts?

I missed this, anyone explored?

https://techcommunity.microsoft.com/t5/excel-blog/announcing-python-in-excel-combining-the-power-of-python-and-the/ba-p/3893439

https://yourcfoguy.ck.page/alt-excel-cheat-sheet (needs sign up but looks useful)

I need/want to select all rectangles or groups in powerpoint and mark them as decorative (ACCESSIBILITY), i don't want to select pictures (as they need alt texting).
HOW?

I have 400+ of these fuckers to deal with.