|
word to the wise: indirect is a volatile function which means it has to recalculate every time any cell in the entire workbook is changed indirect works just fine for small things, but as soon as your workbook starts growing it's time to look for a better solution
|
# ? Nov 8, 2022 15:01 |
|
|
# ? Jun 8, 2024 09:25 |
|
kumba posted:word to the wise: indirect is a volatile function which means it has to recalculate every time any cell in the entire workbook is changed Yeah INDIRECT is one of those functions where if you find yourself using it a lot, you may want to consider using like...an actual database. I work with a data conversion workbook that uses INDIRECT in a lot of places and it's really slick in the sense that holy poo poo a google sheets can do that but I also have to force some pages to update manually by deleting a row, hitting undo, and then sheets will recalculate everything (I don't know if F9 would behave the same way in Excel, in Sheets there doesn't seem to be a manual "refresh all my formulas NOW" option)
|
# ? Nov 8, 2022 15:09 |
|
Also, Indirect is absolute garbage if you want your coworkers, your boss, or you six months in the future to be able to understand why a complex sheet suddenly started giving a weird answer. It's by far the hardest formula to audit.
|
# ? Nov 8, 2022 17:43 |
|
Here's an incredibly dumb question: Can I resize all my columns by just the length of the column header? I seem to recall doing that somehow in the past but I can't figure it out now. grumble grumble loving salesforce export files
|
# ? Nov 8, 2022 20:13 |
|
Lib and let die posted:Here's an incredibly dumb question: A macro which looks at the length of each column, counts the number of characters in the column and makes a guess based on the average kerning.
|
# ? Nov 8, 2022 20:26 |
|
Lib and let die posted:Here's an incredibly dumb question: Highlight the cells you want to fit the columns to. Home->Cells->Format->Autofit Column Width Alt->h->o->i is the alt key shortcut I think it messes up if you use word wrap.
|
# ? Nov 8, 2022 21:23 |
|
esquilax posted:Highlight the cells you want to fit the columns to. oh thank gently caress, i knew it was something incredibly simple but i couldn't even mangle a decent google search to find it lmao
|
# ? Nov 8, 2022 21:26 |
|
For mostly layout and ease of use reasons (so don't put too much thought into it) I'm trying to have a pivot table Filter Field use the value of another cell outside of the pivot table as the filter and, when that cell is blank, have no filter. Basically if I enter "Depot 1" in B1 the filter shown in B2 should also change to "Depot 1". If B1 is blank or doesn't match a filter value then B2 should be All. After a bit of googling I've found several pages which suggest its possible using VBA, even easy to the point where its just "copy this code and make sure the cell/sheet names are the ones you're using" but none of them seem to work. The Filter Field works if I use the regular filter but if I enter a value in B1 the Filter Field doesn't change (and yes its the exact same as the value in the pivot table I want to filter for). I can post the VBA code I found if that will help. I've checked the cell/sheet/pivot table names several times and I feel like I'm missing something really simple.
|
# ? Nov 8, 2022 23:41 |
|
The vba code should just be one line, every column has a column width property and you can just set that to shut number, and if you want them all equal then you just feed column A's width as the argument
|
# ? Nov 9, 2022 05:38 |
|
Kibayasu posted:For mostly layout and ease of use reasons (so don't put too much thought into it) I'm trying to have a pivot table Filter Field use the value of another cell outside of the pivot table as the filter and, when that cell is blank, have no filter. Basically if I enter "Depot 1" in B1 the filter shown in B2 should also change to "Depot 1". If B1 is blank or doesn't match a filter value then B2 should be All. I’m not sure if you mean the code is not firing at all or the code is not firing when it’s a column filter instead of a pivot filter. If it’s the former: Make sure you’ve put you’re VBA in the correct place, probably On Change property of that worksheet. I just googled some random code, something like this should work. I’m not in front of Excel so it might be ClearFilter/ClearFilters instead of ClearAllFilters. You should be able to just record it and pull the correct usage from that. code:
|
# ? Nov 9, 2022 06:24 |
|
DRINK ME posted:I’m not sure if you mean the code is not firing at all or the code is not firing when it’s a column filter instead of a pivot filter. Assuming I put this in the right place it gives me a Run time 1004 error whenever I enter or delete anything in B1. To show what I tried before I got it from here https://www.extendoffice.com/documents/excel/5326-excel-pivot-table-filter-link-to-cell.html. Specifically: code:
|
# ? Nov 10, 2022 18:18 |
|
Kibayasu posted:Assuming I put this in the right place it gives me a Run time 1004 error whenever I enter or delete anything in B1. I think your problem is that you have no error checking on your set. What if xPTable couldn't find depots? What if xPFile couldn't find the pivot fields? What if the target had no test? It's combersome but refactor your code to only advance if the value is found. This will make debugging easier. code:
or better, use therror handling I added HootTheOwl fucked around with this message at 18:44 on Nov 10, 2022 |
# ? Nov 10, 2022 18:41 |
|
After you get that to compile and run (I typed it free hand, so there could by typos and syntax errors that VS would find), add a messagebox to the handling section so you can track which line failed. Just have a string defined as 'nothing' and then after every action you're worried about change the value to 'did X, now trying Y' and then you'll have it. If it's actually truly not erroring, then you're probably not getting the correct values assigned.
|
# ? Nov 10, 2022 18:51 |
|
HootTheOwl posted:After you get that to compile and run (I typed it free hand, so there could by typos and syntax errors that VS would find), add a messagebox to the handling section so you can track which line failed. Just have a string defined as 'nothing' and then after every action you're worried about change the value to 'did X, now trying Y' and then you'll have it. I really appreciate the help! Though if I wasn't clear I have 0 knowledge about VBA and if I ever need it just hope that someone on the internet has posted about precisely what I need and that just copying/pasting the code will work. I wouldn't have the first idea on how to do what you've suggested here, which makes it even more awkward asking for VBA help. That's good to know about "On Error Resume Next" though, I never would have thought there would be code to not tell you the code is wrong, the only thing I could do 10 minutes ago was look at it and go "Yup, that sure is some code alright." Anyways with your changes I'm getting a "Compile error: Syntax error" now so I guess that's progress in that I know something is actually happening. But, see above, I don't know what would be wrong. These lines have been highlighted in red, which I am assuming means that is where the issues are (which might not be a good assumption!). code:
code:
code:
|
# ? Nov 10, 2022 19:40 |
|
Kibayasu posted:I really appreciate the help! Though if I wasn't clear I have 0 knowledge about VBA and if I ever need it just hope that someone on the internet has posted about precisely what I need and that just copying/pasting the code will work. I wouldn't have the first idea on how to do what you've suggested here, which makes it even more awkward asking for VBA help. That's good to know about "On Error Resume Next" though, I never would have thought there would be code to not tell you the code is wrong, the only thing I could do 10 minutes ago was look at it and go "Yup, that sure is some code alright." Ok, so a syntax error means a typo. Code is very picky. Ok, it looks like IsNot isn't supported? which means your statements need to change. I edited the codeblocks for you. (basically you have to use NOT (variable Is Nothing), instead of (variable ISNOT nothing))
|
# ? Nov 10, 2022 20:12 |
|
Yep, those are right now - Excel VBA just uses Not. So either test for Not Nothing or test for the inverse: If Not range(“b1”) is Nothing then Continue Or If range(“b1”) is Nothing then Exit sub
|
# ? Nov 10, 2022 22:27 |
|
HootTheOwl posted:Happy to help. Hey, sorry for vanishing for a few days, long weekend and then this got pushed to the backburner. Anyways I made the code changes but now things are seemingly back to not doing anything if something is entered in B1 or not, as if that "On Error Resume Next" line is back in place (its still commented). Maybe I should just tell people to use the default filter window.
|
# ? Nov 18, 2022 01:05 |
|
Add this to the error block, you need to get your code to signal the errorcode:
|
# ? Nov 18, 2022 03:16 |
|
With the various updates and suggestions, I'm not sure what your code looks like now, but this code should work, just update the const strings as required. Also make sure that the cell is not being updated when the pivot table changes size when the filter updates. I suggest for this function, if an error is thrown then the best thing to do is just quit. This could be because the sheet / table / field doesn't exist in which case you need to fix your code, or the user has entered an invalid value. If the user enters something invalid then the function will clear the filter but not be able to set it again. Depending on your users and use case this might be fine or you might want to add something to let them know. Visual Basic .NET code:
|
# ? Nov 18, 2022 10:37 |
|
HootTheOwl posted:Add this to the error block, you need to get your code to signal the error After entering this I got an error window with: code:
At this point I feel like it must be something I've done but don't know enough to explain.
|
# ? Nov 18, 2022 23:20 |
|
Searching for that error message seems to indicate that Excel can't find the field you are looking for in the pivot table. Check to make sure your the pivotFilterFieldName is correct, I think it should be something like this based on your previous post.Visual Basic .NET code:
|
# ? Nov 18, 2022 23:55 |
|
You're issue is this line:code:
Can you post the whole macro? I might have to actually write this myself
|
# ? Nov 19, 2022 04:55 |
|
Hughmoris posted:Any M-code gurus out there using it daily? If so, your thoughts? I've recently started exploring Power Query inside Power BI, seems like a fun little language. I'm a huge fan. Dunno if that makes me weird or w/e, but most of the ways it works align with how I conceptualize tables when cleaning/manipulating data. Even though I use R/Python/DAX/SQL too, I think M-code hits the sweetspot in terms of ease and simplicity for like 75% of data prep jobs.
|
# ? Nov 19, 2022 07:06 |
|
I'm just getting into sap gui scripting with excel/vba to automate a bunch of our work. About to push my first build to production! It's crazy how well it works and how fast it is. It's taking 8 hours of manual work down to less then an hour! Lots of trial and error though. Not a lot of documentation so kinda hodge podged it all together.
|
# ? Nov 19, 2022 14:06 |
|
Ninja.Bob posted:Searching for that error message seems to indicate that Excel can't find the field you are looking for in the pivot table. Check to make sure your the pivotFilterFieldName is correct, I think it should be something like this based on your previous post. "As far as I can tell" doesn't mean much coming from me but as far as I can tell all the names are correct. I even made sure the letter case matched. HootTheOwl posted:You're issue is this line: I might be misunderstanding you but I'm not using anything else except what you (and Ninja.Bob) posted, or the earlier one I copied from a Google result. Is there something specific you want me to post because otherwise I'd just be posting what you posted back at you. I'm not going to turn down any further help but at this point I'm just asking everyone to tell me what to do word by word rather than solving a small issue of larger whole I know how to do. Unless this has piqued a curiosity that must be satisfied I'm okay with just letting this go.
|
# ? Nov 24, 2022 23:41 |
|
I understand this can be frustrating and not a worthwhile time investment, but given the error you had: "Unable to get the PivotFields property of the PivotTable class" you are very close to a working solution and it's probably something really simple like a trailing space or something similar. You can check for trailing spaces, etc., with a formula in your spreadsheet e.g. =""""&A2&"""" (cell reference based on your earlier screen clip, it should point to the filter name cell in your pivot table) and then copy and pasting the field name wrapped in double inverted commas into your vba as the pivotFilterFieldName. Alternatively, if you run the PrintPivotFields macro below (in the vba editor, click the green arrow, hit F5, or Run > Run, and then select the PrintPivotFields macro) and it will print out all the pivot table filter fields in the current file to the vba immediate window (View > Immediate Window), formatted so you can copy and paste the correct set of lines straight into the original function (also included for reference). If you try these solutions and still have no success then, while I'm still happy to help, I understand if you don't want to spend any more time on it. Visual Basic .NET code:
|
# ? Nov 25, 2022 12:54 |
|
Is there really no straightforward way to change default ctrl-v paste? I want to paste unformatted text into excel from web pages, just the numbers without color and font. I know about the menu you get with a right click, but I just want the ctrl-v to paste unformatted text by default.
|
# ? Dec 19, 2022 11:18 |
|
axolotl farmer posted:Is there really no straightforward way to change default ctrl-v paste? I want to paste unformatted text into excel from web pages, just the numbers without color and font. Can't you hold shift? Also there's a section called paste options where you can change the default E: https://www.causal.app/excel-shortcuts/shortcut-to-paste-values-in-excel
|
# ? Dec 19, 2022 13:27 |
|
HootTheOwl posted:Can't you hold shift? Thanks. For some reason this does not work in Excel. I'm using the Swedish version of Excel365. They also removed the option to paste values only by default for some reason.
|
# ? Dec 19, 2022 13:38 |
|
I always add a shortcut of paste unformatted (well, paste with number formats only) to the quick access toolbar. Then you can use Alt+n instead of ctrl-v as your pasting shortcut -- where n is the position of the button in the quick access toolbar (e.g. the third button shortcut is Alt+3). You might have to search through a few screens of commands to add, but pays off in the long run.
|
# ? Dec 19, 2022 23:23 |
|
I'm currently grading some of my trading cards and keeping track of it in a spreadsheet. I have 7 columns - the 1st column is the name of the card, the 2nd column is the estimated grade of the card ranging from 1 to 10 with 10 being completely mint, and the remaining 5 columns are categorized from Ungraded to 7, 8, 9, and 10 with an average sold price for those respective grades. Essentially, any grade equal to 6 or below will fall under the "Ungraded" column. I want to create a new column where it takes the estimated grade from the 2nd column, finds the average price in column 3, 4, 5, 6, and 7 - and then outputs that number in the new column. What would be the best way of going about this? For example, from the image I attached, the new column will have the following outputs: Row 1 - $355 Row 2 - $380 Row 3 - $280 Row 4 - $235 (I accidentally did not include the "10" grade in the screenshot I shared and now I can't edit it but the formula would still be the same)
|
# ? Jan 5, 2023 13:49 |
|
Use an XLOOKUP, assuming your table is from a1 to f5 the formula would be: =XLOOKUP(A2,$C$1:$F$1,C2:F2,B2,0,1) which matches the estimated grade against the column headers and returns the ungraded column if it's not found.
|
# ? Jan 5, 2023 14:04 |
|
Busy Bee posted:I'm currently grading some of my trading cards and keeping track of it in a spreadsheet. seems simple enough to just use a switch (also, oh gently caress yes excel has built in switch case?) Put this into your new 8th column code:
A with the estimated grade column, C with the 7 column D with the 8 F with 9 G with 10 B with the Ungraded
|
# ? Jan 5, 2023 14:11 |
|
HootTheOwl posted:seems simple enough to just use a switch (also, oh gently caress yes excel has built in switch case?) That worked! Thank you! Now I have another question. Let's say I want another column now to show the estimated price for the one grade above the estimated grade I chose. However, if the estimated grade I chose is any one between 1 to 5, the output will always be the ungraded price while if I chose an estimated grade of 6, 7, 8, or 9 - it will show the average price for the grades 7, 8, 9, and 10. I have no estimated 10 grades in my Google Sheet nor do I intend to have it. I want this new column to show this data in case I want to take the risk of paying to get the card graded in the chance that I will receive a higher grade. How would I go about this? Busy Bee fucked around with this message at 15:03 on Jan 5, 2023 |
# ? Jan 5, 2023 15:01 |
|
Busy Bee posted:That worked! Thank you! The switch function looks for a match and then returns the value when it does, with the last value being no match. So, simply take all the grades in the function, and subtract 1. Then add the 10 pair again (because otherwise it will go to ungraded) code:
code:
|
# ? Jan 5, 2023 15:09 |
|
HootTheOwl posted:The switch function looks for a match and then returns the value when it does, with the last value being no match. Amazing, thank you so much!
|
# ? Jan 5, 2023 16:06 |
Any way to add commas or spaces to a large number in a string that is the result of a calculation? The case here is that I'm making a calculator for a scale model of the solar system for camp counselors to use. If they use a 150m parking lot, they're making a 1:148,000,000,000 scale model but Excel writes it 1:148000000000. Is there any way to add the comma separators in? My spreadsheet is real simple B3 there has =CONCAT("1:",B1/B2) as the formula.
|
|
# ? Jan 11, 2023 20:11 |
|
tuyop posted:Any way to add commas or spaces to a large number in a string that is the result of a calculation? CONCAT("1:", TEXT(B1/B2,"#,###"))
|
# ? Jan 11, 2023 20:34 |
|
tuyop posted:Any way to add commas or spaces to a large number in a string that is the result of a calculation? Something like this: =CONCAT("1:",TEXT(B1/B2,"#,###")) You need to format the division output, and then concat your 1: onto it.
|
# ? Jan 11, 2023 20:37 |
|
|
# ? Jun 8, 2024 09:25 |
Forgot all about TEXT(), oops! Thanks to you both.
|
|
# ? Jan 11, 2023 20:41 |