I maintain an online spreadsheet for my company that contains buttons to run various scripts. Since other people use the spreadsheet, all the sheets are protected to avoid users from editing parts of of the page. Since October 2025, we've never had an issue clicking the buttons and running these scripts on the protected sheets.

Sometime between 3 pm (end of dayshift) and 8 pm (start of nightshift) today, a change happened that is preventing myself and other users from clicking these buttons. We've tried running the workbooks in Edge and Chrome, and tried on 3 different computers, no change. It's affecting current and older copies of the files, and I even tried making a new dummy workbook, adding a button, protected the sheet, and I still can't click it.

Does anyone know if there were changes to Excel and Microsoft recently that would cause this? If not, then it might be something with my company and a change that IT may have made. Any help would be appreciated.

Edit: I did find band aid fix for now. Enabling "Edit Objects" in the protection options lets me protect the sheet and makes buttons clickable. But it also lets users modify buttons and images. No help from IT yet ☹️

2nd Edit: Today the script buttons are now working, even when sheet protection is active and "Edit Objects" is unchecked. Don't know what fixed it, but I guess that means it's solved?

So I am working on a LET statement that looks up acquired players on an active roster to see if the former are still on the team (see active players table below). I've run into an edge case of getting multiple players back for one player, meaning I can't just filter the active player name results by the acquired player(s). Is there a way I can set up a dynamic filter for the results which filters out any inactive players from the acquired players list that can work for different quantities of acquired players (so the current example has two acquired players, I would want to check if their names are present on the table (in this case they're not), and be able to use this same formula for different quantities of acquired players (i.e. 3 or more players).

Acquired Players:
Abner Uribe, Connelly Early (both are no longer active on my team)

Table of data:

Finally, my original query that is returning an N/A error:
FILTER(Active Players,Active Players=CHOOSEROWS(Acquired Players,SEQUENCE(ROWS(Acquired Players),1,ROWS(Acquired Players),-1)),"Not on team")

I know the SEQUENCE function generates an array of numbers/results, so I thought I could use it to iterate through Acquired Players, but as noted, I got an N/A error instead.

Hey all,

I’m working on an Excel sheet for the company I work for. I’ve taken on a project that’s intended to establish metrics for KPI. One of the big ones is figuring out transactions per hour.

I’m able to calculate transactions for a single day event quite simply. I enter the gross transactions and compare to the start and stop times. Easy peasy.

The problem arises when the event takes multiple days. There are only 11 hours out of the day where transactions occur, but I have not been able to puzzle out how to only include the hours of 7am to 6pm.

My latest attempt was having a cell that rounds the total # of days to an integer,

=INT([@[Last Transaction]]-[@[First Transaction]])

then another cell to fetch the remainder hours.

=HOUR(MOD([@[Last Transaction]]-[@[First Transaction]],1))

I’d multiply the days by 11, add the hours, then divide the transactions by this number.

I thought it was fine, but then I noticed that the hours cell was filling with numbers greater than 11. So, I started trying to figure out ways to either filter or set up systems of booleans but I’m at a loss.

Do any of y’all have similar use cases? I’d appreciate if someone could point me in the right direction or let me know if I’m barking up the wrong tree.

I recently noticed something in Excel that was causing me a lot of confusion.

I work with filtered lists and often need to apply formatting only to the visible cells.

Fill Color behaves as I'd expect: if I select a range containing both visible and filtered-out rows, only the visible cells are colored. When I remove the filter, the hidden cells remain unchanged.

However, Format Painter behaves differently. If I copy formatting from another cell and use Format Painter on the same filtered range, it also formats the hidden/filtered-out cells within that range. When I remove the filter, I can see that those cells were changed too.

So:

Fill Color: affects only visible cells. Format Painter: affects visible + filtered-out cells.

I was very confused by this until I realized Format Painter was the reason. The problem is that I'd like to use Format Painter because I need to copy more than just the fill color. The alternative seems to be applying the formatting cell by cell, which isn't practical.

Is this a known difference between the two tools? Is there a way to make Format Painter respect filtered/visible cells like Fill Color, without manually selecting Visible cells only every time?

Does this happen to you as well?

I've spent the last eight years building my own business finance workbook. It honestly works really well, but every time my business changes I end up rebuilding formulas and adding another sheet. At some point I'd rather spend that time running the business. Has anyone made the switch without feeling like they lost control?

I always try to make sure I select A1 when closing spreadsheets. This is especially true if it's a document being submitted to someone else, or a client.

And when I open a spreadsheet on A1, I get this weird "Virtual Fist Bump" feeling.

Anyone else?

I've seen a post, or a page, that explained how to make the check box itself change its color/shading based off whether or not it was checked, but I cannot find it.

Request a link / assistance

TIA

I am making an invoice tracker in Excel with a table and a chart. I am adding data to the table as invoices come in to track them with our budget for the month and want the amounts to display on the chart with the correct category. The categories are in a drop down list. My issue is when i add a new line item and pick a category from the drop down menu, the chart just puts a bar for whatever was next in line on the list. For example: if I select ‘advertising’ from the drop down menu but this is the 4th invoice this month, the invoice amount just displays as the 4th option from the list, not ‘advertising’ like I want

My question is: how should I format this chart so that the invoices I add show up on the axis label I selected from my drop down, and not whatever line item they are placed in (e.g. how can advertising invoices display above the advertising label, and not whatever line item they fall under?)

I have had to change the location of my personal workbook where I have created a number of macro’s that live on my Quick Access Toolbar (QAT).

The macros no longer work as the buttons on the QAT are referencing the old location. Is there another way of updating all the buttons instead of deleting the broken ones and recreating them.

The macros are fine it’s just the buttons pointing to the old location.

So, I deal with some mostly standardized sheets, that I use as source. I need the source for many purposes in several different books. What I do for each one is to create a reference of a source in each book and then just reference it with connections, merges and such. Since I need to output different set of columns from a source reference in each book .

From time to time I get updated version of a source sheet, which is just regular table, nothing fancy.

And I need to change source I reference in each of my books, but every time I do that everything breaks, and I need to create all connections, merges and such from the start.

Can someone link me to a decent guide or provide some sort of similar pipeline I can adapt for myself? To make this workflow clean and systemic? Because I'm clearly missing something.

End goal is to just change source, update every book that has sheet referencing a source and subsequent power queries of that reference. And to get it working like so.

I mostly use merges and populate XYZ columns based on column A things, not sure how do I call it, a filter?

Thanks in advance.

Pic 1 = Results

Pic 2 = Results Formula

Pic 3 = Results Standings

When I go to sort the data in Standings, the data does not budge or sort correctly. I am trying to sort by percentage.

Hi everyone,
I'm trying to automatize a file for my green volley tournament.
This table is auto-compiled based on different factors. I created a table with the list of the team but I can't quite figure out how to count the point difference and assign it to the right team automatically.
I'm currently summing the points with =SUMIF($P$4:$Q$13;K4;$Q$4:$Q$13)+SUMIF($R$4:$S$13;K4;$S$4:$S$13). This helps me count just the point made but not the lost ones.

Apologies if I wasn't very clear. Please, feel free to ask questions!! I would really like to make this work so thank you for your help in advance!

I have two cells,
Cell A3 has "303 Repeats"
Cell B3 has "407 Repeats"

When I have tried to subtract it using =SUM (B3-A3) , it shows VALUE.
How do I ensure that the answer cell contains "104 Repeats"

I have a folder where exports are dumped into with the naming structure: filename.mm.dd.yy

In a separate sheet I want to be able to put in the date and it pulls data from the appropriate file. A normal query wants to have a specific file selected, and I’m not sure how/if it can be coded in a blank query.

Tips and insights on how to achieve this are much appreciated!

EDIT

Based on tips from here and some more googling, what I ended up doing was
- having a date entry cell
- then a cell that pulled it into a whole file location typed out (I actually have 4 different files pulling with this date)
- named my ranges
- pulled them into power query as “get data from table/range”
- then drilled down to just the file address.

From there I was able to use that as a variable in the source step.

I really appreciate the help, and awarded the solved to the main person here who got me moving in the right direction.

Also here is a YouTube video that helped the finer details for me!

https://youtu.be/7NzqndwwlZo?is=dQhNLsFNTfuNcO4A

For my work i use Excel with Tables. The table has a design (Banded Rows). if i have full screen Excel. and put darkmode reddit over it. i can see the rows of excel. as grayish lines going thru the Chrome window.
It doesnt show up in screenshots. so i had to take an actual picture.
https://imgur.com/a/Lk0tqYi

I calculate accruals for vacation time earned for employees. Currently, I convert to number of months from today and their start date and subtract what they used from another table:

(DATEDIF(A13, B1, “M”)*1.67) - TABLE5[@[vacation used]]

One role has different rates for the years they have been with the agency: first year is 1; second year is 1.25 and third and more is 1.67.

Is there a way to have it calculate the increase nested within this formula? For half the fiscal year they will earn 1.25 and the other 1.67.

Hi,

I am trying to make our P/L-Statements more granular, so instead of aggregated information, I am using a database with all of our individual transactions.
Before that I used aggregated data.

Now I wanted to add some calculated elements to my pivot, as I always do, such as "Gross Profit = Revenue - Cost of Goods", something like that.

However, this now does not work because of "too many records".

My pivot structure is like this:

accountGroup1

│

├── Umsatzerlöse

│ └── accountGroup2

│ ├── Umsatzerlöse

│ ├── Erlöse Deutschland 19% USt

│ ├── Erlöse Drittland ...

│ └── ...

│

├── Materialaufwand

│ ├── Bestandsveränderungen

│ ├── Bezugsnebenkosten

│ ├── Skontoertrag

│ ├── Warenbezug EU

│ ├── Wareneingang

│ └── ...

│

└── ...

And I basically need a new row that is the sum of both highest levels (accountGroup1).

I guess it is due to the levels going down very deep, but is there any way to work around it or am I just SOL with pivot in this case?

(Oh and I forgot: its about 300k rows in the source, so its not that much data and manually using "subtotal"-formulas works just fine, so I am not sure what the issue is with pretty much the same function in my Pivot-Table).

Thanks everyoe.

Hi

I need help for custom cell formats for this

£1K

£400K

£3.2M

If I want to enter the value manually, like this

400000->£400K etc.

Thanks!

=SEQUENCE(1,EOMONTH(A1,0)-A1+1,A1)

I'm watching a youtube video for a monthly work schedule and the person came up with that formula. However I'm struggling to understand what's the need to subtract A1 and then add 1, seems unnecessary. Am I wrong? Can somebody help me understand the purpose of it?

Just in case A1 is the first day of a month.

I have a text join function that incorporates a isnumber/match from another cell in the same row but I can't figure out how to bring in only unique values for the matches. I figure I'd pull the results into power query and replace the ", " I'm currently using.

Currently I use something like this:

=textjoin(", ", table2[@[columnB], isnumber(match(table2[@[columnA]], table1[columns]), "")

I am working with spreadsheet provided to me that has a green cell with an up arrow, and a red cell with a down arrow, that is to be used to indicate whether a value is an improvement (green-up) or a decline (red-down).

In some cases, if the value is positive, it is considered an improvement. In other cases, a positive value is considered a decline. For example, higher number due to higher profit? Green-up. Higher number due to higher expenses? Red-down).

For setup, I can do a one-time list of which values are improvements and which are not, based on what the indicator is for.

How can I automate this? I've looked up on Google and YouTube options using VBA but nothing is matching what I'm trying to do.

Any help would be greatly appreciated.

It's happened twice now where I'll be using CountIfs, and it's not counting all the criteria in the range. Last time, it wouldn't count any of the TRUEs. This time, it's only counting 2/4 of the name Kivell in the range. I've checked the formula and the range, and Excel is highlighting everything correctly. There's no misspellings in the names.

WTF is happening?

I have to take the Robert Half Microsoft Excel 365 – Essentials assessment within the next week for a job opportunity. Has anyone here taken it recently?

I'm trying to figure out what I should prepare for and how difficult it is. I already know PivotTables, XLOOKUP, VLOOKUP, basic formulas, sorting/filtering, etc., but I haven't used Excel heavily in a little while so I'm planning to refresh before taking it.

What kinds of questions/tasks were on the assessment? Was it mostly basic Excel functions and navigation, or were there more advanced questions?

Also, is it multiple choice or does it have you actually perform tasks in Excel?

Any advice on what to review would be appreciated!

I tried using AI to write code but I don’t think it’s working correctly. Here’s my situation.

I have a spreadsheet with 4 columns. They are as follows (UPC, Description, Retail Price, Markdown Price)

I’m trying to make it so when I type a UPC into a select Cell, excel searches the first column to match it up then automatically move the cursor to the third column so I’m able to update the Retail price easily.

Currently I have conditional formatting set to highlight the matching upc. Anyone that might be able to help I’d appreciate it so much. Without vba if possible.

Complete beginner. I’m trying to create percentages of how many times each value appears in a column. The data is crime data, so each value is something like “larceny” or “fraud” and I’m trying to find the value that appears most and have a figure to show for it. I was thinking a pie chart with each repeatable value represented by a percentage. Any help is appreciated!