MS Excel / VBA help thread

(obvs the template is now changed, but the doc i'm working on...)

I need to filter on either of 2 items of many in a column to give a cleaner view auto filter isn't an option as it is a shared workbook with multi users so I'm creating a mirror worksheet elsewhere to give the clean view.

My solution was to have the 2 items as lookup cells, then create a data validation picker to choose either option, then point the Filter function to use that cell to define what to filter by.

This works at first, then when I change the data validation picker the results don't change.

I can't see why. Is there something really simple I've overlooked (calculation option is automatic)?

I've just tested this on a set of test data I had open and it works fine for me on O365 (I assume you must be on a pretty up-to-date version if you have the FILTER formula). Updates automatically when I change the dropdown.

1 Attachment

• Filter test.xlsx

It worked here until I shared the file with a colleague... might that be causing the problem?

Thanks for testing and confirming it should work in principle too.

Who knows. We have all types of weird permissions shit going on when we try and share stuff. It does work on Excel Online for me though.

Do you need the data picker? They’re always a fucking pita

I tried OR with it to pull all of either item through, but it cooked my brain, I'll revisit tomorrow

https://youtu.be/m15ccyLspuY

I Think this is good.

OR is pretty easy (use the + sign between conditions) but you need to make sure you put both conditions completely in brackets which gets glossed over in a lot of tutorials.

Thanks @aggi & @Chalfie, appreciated.

Bit different from the normal formula questions, but thought ppl here might have experience or bright ideas.

I've been conducting some internal interviews to give feedback/improvements for a client.

I generally use excel for notes and a lot of other text work because I can add categories, etc. and move and group things easily, so did it in xls.

Looking ahead I'd like to make a template for future use. But I'm struggling with how you have a nice first/second column with the topic which then spans multiple rows of comments, where I want columns with themes/tags to pull out later.

I feel like there must be a better method that people who do this use. Or do people have specialist tools so xls isn't used.

I've used excel for something similar before. I always have the topic field entered on every row, because I might want to filter by topic later.
If you want it to print nice with row spanning you could always conditional format it to white text if the value is the same as the one above.

That's a really good shout.

I'd like to add it as a company template doc, hence wanting to make it look nice. Otherwise I'd just go with a windows 95 aesthetic.

Or maybe there's something with the Group / sublevel / subtotal function which would make the same effect. I forgot what it's called.

You need to think about this as long vs wide data. I think. About to go and do something else, this is a placeholder for me.

I'd use separate tabs for the inputs. Ref tab with the standard stuff (name, position etc) with a unique ID for each person. If I had three attributes I wanted to document, I'd create a tab for each attribute and use the unique ref and name with columns for all the stuff I wanted to record. I'd join all the tables I've created in each tab using the unique ref and create a master data sheet in PQ which I'd copy and edit into the required outputs zooshed back into the spreadsheet with all the formatting etc. Basically a series of input and output tabs.

MS Access...

*pukesoncock*

I've got a set of FX rates that are missing some dates. In Excel this would be no trouble, I'd do an xlookup with it matching to closest rather than exact.

However, if I pull this data into the Data Model and create a relationship based on date in Power Pivot, how do I get it to choose the nearest date with a value rather than returning blanks for the dates that aren't present in my FX rates?

Survey data and qualitative stuff:
If you want to get into it, it's better to structure your data in a long format rather than a wide format.
In this way you'll have category fields and topic fields on every row for every comment. It might also be good to start thinking about positive and negative sentiments and also can you score it?

Date tables:
if you create a separate date table, so there are no missing dates, you can then reference this as your one date to rule them all.

that's a bit cryptic but i think it's the way to go.
Have a single date table acting as your calendar, then do some sort of lookup like you did in sheet excel rather than power excel.

👍

+/- values are an excellent idea.

If you’re collecting free text, it’s probably best to associate themes as well.

Yes, I've got the date table in to link them. But the trouble is when I have a sale on 1/07/18 but no FX rate on 1/07/18 (it was a Sunday) then it returns a blank as there is no corresponding record.

If I'm working on forum.xls and save a snapshot backup copy as forum_bak.xls my other workbooks that were looking at forum.xls start looking at fourm_bak.xls instead. Is this something I can prevent in the settings somewhere?

I’m trying to remember how we got around this with specimen dates in our reporting for Covid and can’t remember right now

I think it was by adding days or something but I think that was to set to a reporting week.