User Profile
JKPieterse
Silver Contributor
Joined 9 years ago
User Widgets
Recent Discussions
Re: Reference error when using local names
I agree local names are useful, but I find it unusual that a name local to sheet1 is used in formulas on sheet2. Perhaps not if you're totaling different sheets. But then I'd argue having data spread across sheets isn't the best design for your data :-)104Views1like1CommentRe: Locking Tabs on a shared spreadsheet
Sounds like this is a navigation issue. Add a Table Of Contents worksheet (make it the first one in the workbook). Then at the top of every sheet add a link back to that ToC worksheet. To prevent adding worksheets, you can consider protecting the workbook (Review tab).50Views0likes0CommentsRe: How to sort calculated Field names in Pivot tables
BetAfter The question is not about sorting the field on values, it is about sorting the list of fields in the Insert Calculated Field dialog. As far as I know all you can do is sort the source data alphabetically by Field name. Even then, newly added calculated fields always end up at the bottom.67Views0likes0CommentsRe: Excel formula to count and sum when value changes?
OK, misunderstood. I though the values in cells A1 and B1 were as you posted them: "10,16,8,12,3". Hence my TEXTSPLIT function example. Definitely needs some (VBA?) programming. How does the data end up in cells A1 and B1 precisely?34Views0likes0CommentsRe: Possible Bug: VBA Debugging Breaks After Selecting a Cell with Nested LAMBDA Functions in Excel 365
It helps if you could share a workbook that reproduces this issue. For starters, once the debugging issue surfaces click Help, Feedback, Report a problem and describe your issue there.60Views0likes0CommentsRe: Problem with Excel fill handle
Whether Excel recognized month names depends on the region you have set in Windows Regional settings. To find out which the month names are, enter a date like 1-1-2025 in a cell and drag this cell down using the RIGHT mouse button. Drag down 12 cells and when you release the mouse you can choose Fill months. Now format these cells to show month names in text, like mmm to show three letter abbreviations or mmmm to show the months in full. Your months must be spelled precisely like this. If Excel shows a different spelling (language) of month names, you can either go change it in the Windows settings, or you can add the list of month names you want it to use as a custom list. Write all twelve month names into a column, then open File, Options and locate the Custom lists button on the Advanced tab close to the bottom. There is a box there to select a range of cells.44Views1like1CommentRe: identifying changes overtime in an excel spreadsheet
Not sure I totally get the request, but I think you can pull this off using a pivot table. Select the data, format it as a table (Home tab or press control+t). Click the "Summarize with Pivot table" button on the Table Design tab, or the Pivot table button on the Insert tab Drag both the Client ID and the Start Date fields to the Rows box Drag the Monthly Amount field to the Values box Right-click any of the numbers in the "Sum of Amount" column in the Pivot table and select "Show Values As", "Difference From". In the small box, choose Start Date in the first drop-down and "(previous)" in the second and click OK25Views0likes0CommentsRe: Excel formula to count and sum when value changes?
I used two helper columns. Here are the formulas I used: C1: =SUM(D1:E1) D1: =LET(items,TEXTSPLIT(A1,","),itemsNext,HSTACK(DROP(items,,1),0),delta,DROP(itemsNext-items,,-1),-SUM(IF(delta<0,delta,0))) E1: =LET(items,TEXTSPLIT(A2,","),itemsNext,HSTACK(DROP(items,,1),0),delta,DROP(itemsNext-items,,-1),SUM(IF(delta>0,delta,0))) C2: =SUM(D2:E2) D2: =LET(items,TEXTSPLIT(A1,","),itemsNext,HSTACK(DROP(items,,1),0),delta,DROP(itemsNext-items,,-1),SUM(IF(delta>0,delta,0))) E2: =LET(items,TEXTSPLIT(A2,","),itemsNext,HSTACK(DROP(items,,1),0),delta,DROP(itemsNext-items,,-1),-SUM(IF(delta<0,delta,0)))63Views0likes3Comments
Recent Blog Articles
No content to show