MS Excel / VBA help thread

Force validation on them so they cant, we use userforms for something similar

Use separate cells for start and end time.

My plan is

Person date start finish concatenation of start: finish helper column

Which you can pivot to get an auto update calendar and counts/sums/averages of times worked

I'm not sure what they want though and will find out today.

For those keen of interest, and that doesn't include me.

It turns out people express an interest in their shift availability by writing in the cell "Y" or "N". The allocators then turn the cell yellow or blue respectively, and then write in the time.

I've made them a sheet where people can express interest for a date.
Then the sheet that tells them their hours allocated goes yellow or blue and then the allocator adds their time in.
to calculate hours worked i used
=((TEXT(RIGHT(D5,5),"hh:mm")-TEXT(LEFT(D5,5),"hh:mm"))*24)-0.5

and i'm going with a simple sum per column for hours available per day.
I sppose i can add in a countifnotblank so i can get the number of people working a day too.

=((TEXT(RIGHT(D5,5),"hh:mm")-TEXT(LEFT(D­5,5),"hh:mm"))*24)-0.5

Fragile

Use separate cells for start and end time.

Yes

Any suggestions on how to get a table that updates each time a spreadsheet is opened.

Basically want a table that reflects new data that has been input into another sheet. The other sheet has a unique, key column which I feel could be useful.

Effectively each time I open it I want to clear out the existing data and replace with anything that has been newly input in the source worksheet. Data only gets added at the bottom of the source workbook.

I could do it with a macro but that would be quite convoluted (it's to integrate into a flow and they can't use macro enabled workbooks). Am I missing anything or should I just bite the bullet and take that approach?

this should just be part of the data connection options

https://support.microsoft.com/en-us/office/refresh-an-external-data-connection-in-excel-1524175f-777a-48fc-8fc7-c8514b984440

Set refresh options when you open or close a workbook You can refresh
an external data range automatically when you open the workbook. You
can also save the workbook without saving the external data to shrink
the size of the file.

Select a cell in the external data range.

Select Data > Queries & Connections > Connections tab, right click a
query in the list, and then select Properties.

In the Connection Properties dialog box, on the Usage tab, under
Refresh control, select the Refresh data when opening the file check
box.

If you want to save the workbook with the query definition but without
the external data, select the Remove data from the external data range
before saving the workbook check box.

What ifyou used power query lastrefresh? Would that help as in, "were these records here when last refreshed?"

Cheers but I can't see an option to only show new data. So far as I can tell the only option is to refresh and all data will appear in the table. I could add a query but can't think of how I'd filter it.

@Chalfie I tried that out but when I refresh the refresh date for all data in the table becomes the last refresh date so can't think of how I'd use it.

So....dax
I have a measure that calculates the percentage of a value. And it's calculated by sum of number / denominator.

Because of relationships, this is dynamic and spits out percentages for different categories from a look up table.

So you can say
:
% of people who are xyz and have had this

I can display what the max of this measure is using maxx and summarize.

What I want to do is show the type of peopl (the xyz).

Do I need to create a dynamic table and then do a lookup value based on max of the measure? That seems like a painful way to do it .

How do I return the xyz with the max and min %?

If I've written

Maxx(summarize (table, people, measure-%))
How do I swap that to return people?

I bet that's clear as mud?

https://stackoverflow.com/questions/59757923/in-power-bi-how-can-you-return-the-category-label-based-on-the-max-value-for-al

That's the answer btw

I have a workbook that I want to automatically refresh itself.

I have 3 spreadsheets (say A, B and C) with A populating B and B populating C. If I don't open B to refresh it then C will have old data, therefore I want B to auto-refresh (C can't be populated from A due to access permissions for some team members).

I need B to be refreshed daily, preferably without my machine having to be on (although I do leave it on enough that this wouldn't be a deal breaker). The obvious seems to be something using Excel online (everything is on Sharepoint) but a bit of testing doesn't seem to be getting that work (full thing here https://superuser.com/questions/1672620/excel-online-automatic-refresh-issues ).

Anyone any bright ideas? Cheers

Can you make a data flow in the power bi verse and feed it to the spreadsheet?

Wait.
I think you could do it this way.
Store files on SharePoint.
Load A to the data model of C
Load B to the model of C

Do the things that put a into b
And then put them into c

?

I've tried replacing Workbook B with Powerquery but it won't refresh in the browser for some reason, I get an error message which, having done a bit of googling, suggests that it isn't supported yet.

A can't load to C as most people accessing C don't have permission to access A. B is a subset of A with sensitive stuff stripped out and a few lookups.

Nothing works in the browser version of Excel.

There is supposed to be a release soon that will let you access data flows from bi.

This suggests a work around but I'm not sure if it's ott

https://www.thebiccountant.com/2021/01/24/workaround-to-connect-to-power-bi-dataflows-from-power-query-in-excel/

Question for the ninjas here...

I have 31 excel sheets linked in Power Query where I have created an Append with all the data in it. I can view the successful Append in Power Query editor.

When I open Power Pivot, the Append has 3 million rows of blank data!
What am I doing wrong?

Screenshot of the connections below

1 Attachment

Cheers but I think it's a bit overkill for what I'm doing.

You've not defined the data in the sheets as a table and it's bringing in all the blank rows from 31 sheets?

I thought it was when I was looking at it...

When I do stuff like this, I tend to save a as a table and then have c do the load and transform but only load a as a connection.
Dunno if that will be sufficient protection of a.
There should be some form of flow or automate to allow for this, but I don't think our licence gives me this power.

Thanks.. figured it out.. the headers need to match in the 31 sheets!

probably a basic query but that is about my level.

I have a list of 1,000 email addresses and want to pivot them on the domain name. Is there a relatively easy way of doing this?

I don't VBA

text to columns using @ as the criteria?

If you want to get the domains dynamically, use a formula like:

= RIGHT(A1, LEN(A1) - FIND("@", A1))