|
Xenoborg posted:Is ther a way to resolve all of the references in a formula? For budgeting, I have a cell of all my credit cards bills due that is =$F$19+$F$20+$F$21+$F$22. When all statements for a month are in, I paste values, and it becomes a flat -2500, but what I really want to see is for it to bring in all the refs and make it like =-1000-1000-200-300, which would make checking a lot easier when something is wonky. Click the fomula tab and then there's an evaluate formula button which will yet you step through each reference.
|
# ? Oct 6, 2023 16:38 |
|
|
# ? May 11, 2024 16:44 |
|
Xenoborg posted:Is ther a way to resolve all of the references in a formula? For budgeting, I have a cell of all my credit cards bills due that is =$F$19+$F$20+$F$21+$F$22. When all statements for a month are in, I paste values, and it becomes a flat -2500, but what I really want to see is for it to bring in all the refs and make it like =-1000-1000-200-300, which would make checking a lot easier when something is wonky. This might not be helpful, but you can highlight portions of the formula and hit F9 to resolve them to test things out. So using your example, you can highlight $F$19 in the formula, hit F9, and it'll replace that with -1000. (You can escape out afterwards if you don't want to actually change the formula.) You can repeat this with each reference, or even for parts of the expression. Fair warning that this doesn't respect order of operations so be careful. For example, if your formula's =A1+B1*C1 and you highlight A1+B1 and hit F9, it'll just add those together, which won't be a useful answer. It's mostly only good when you need to check something quick, Evaluate Formula is usually better.
|
# ? Oct 6, 2023 17:37 |
|
Alkanos posted:This might not be helpful, but you can highlight portions of the formula and hit F9 to resolve them to test things out. So using your example, you can highlight $F$19 in the formula, hit F9, and it'll replace that with -1000. (You can escape out afterwards if you don't want to actually change the formula.) You can repeat this with each reference, or even for parts of the expression. Fair warning that this doesn't respect order of operations so be careful. For example, if your formula's =A1+B1*C1 and you highlight A1+B1 and hit F9, it'll just add those together, which won't be a useful answer. Thanks, this was the most useful. Evaluate Formula is nice and I use it a lot, but here I'm comparing several cells with formulas at once, like going down a bank statement line by line and matching them to my formulas.
|
# ? Oct 6, 2023 18:07 |
|
Is there any way to map individual pins/data points by zip code using Excel, rather than a filled/heat map? I also tried using PBI, but there's a limit of objects that can be mapped, so PBI ends up leaving half the map blank. It's frustrating that I can't seem to figure out how to replicate basic Microsoft MapPoint functionality.
|
# ? Oct 12, 2023 17:39 |
|
I have a slightly modified piece of VBA code I got from google that I've been using for one of my Excel sheets at work, but for some reason I can't get it to work on the Excel version on my other PC. It goes like this:code:
The only appreciable difference between the two that I can think of is that I manually changed the list separator and decimal signs in my work PC's locale in order to make Excel CSV exports work with our web shop, because I couldn't get it to import CSV files that used semicolon as a list separator instead of comma, and I also changed the decimal sign from comma to period to match English. I did try doing the same on the other PC, but that didn't seem to make a difference in getting the code to work. AG3 fucked around with this message at 14:14 on Nov 14, 2023 |
# ? Nov 14, 2023 14:10 |
|
AG3 posted:I have a slightly modified piece of VBA code I got from google that I've been using for one of my Excel sheets at work, but for some reason I can't get it to work on the Excel version on my other PC. It goes like this: I think you deleted the parenthesis around your method calls in the bottom block. ...FormatConditions.Delete() Selection.FormatConditions.Add(xlExpression, , "TRUE")
|
# ? Nov 14, 2023 14:43 |
|
^Should work without the parenthesis. Excel VBA doesn’t need them. Thoughts on why it’s not working: Make sure you have macros enabled / enable content. You haven’t enabled developer mode. It only works in A1:M600. It only works if you select a single cell.
|
# ? Nov 14, 2023 23:03 |
|
That's what's puzzling me. Everything is working perfectly on my primary work PC, but on my other work PC all of the code in the file except the background color changing works and I can't find any reason why that would be the case. No amount of stopping and restarting the code seems to make the color changing function come on either. FormatConditions seems like such a basic function too, so I can't think of a reason why only that would be blocked and not the other code. I'll have to test on a couple of other PCs at work and see if there's a setting on mine specifically that's making things strange.
|
# ? Nov 14, 2023 23:44 |
|
AG3 posted:That's what's puzzling me. Everything is working perfectly on my primary work PC, but on my other work PC all of the code in the file except the background color changing works and I can't find any reason why that would be the case. No amount of stopping and restarting the code seems to make the color changing function come on either. FormatConditions seems like such a basic function too, so I can't think of a reason why only that would be blocked and not the other code. I'll have to test on a couple of other PCs at work and see if there's a setting on mine specifically that's making things strange. Add some breaks and error handling Use message boxes to check properties if the returned object HootTheOwl fucked around with this message at 23:52 on Nov 14, 2023 |
# ? Nov 14, 2023 23:50 |
|
HootTheOwl posted:I think you deleted the parenthesis around your method calls in the bottom block. AG3 posted:I have a slightly modified piece of VBA code I got from google that I've been using for one of my Excel sheets at work, but for some reason I can't get it to work on the Excel version on my other PC. It goes like this: Given you mention changing locale settings, if your language is not English then you may need to change Visual Basic .NET code:
|
# ? Nov 15, 2023 00:39 |
|
Ninja.Bob posted:Given you mention changing locale settings, if your language is not English then you may need to change Holy poo poo, it was actually this. I had to change "TRUE" to "SANN" (Norwegian for true) and the code worked. Now the baffling thing here is that my work PC, where the code worked, IS set to Norwegian locale already, but the actual display language in Windows itself is set to English by default from the provider, while my other PC has a Norwegian display language. It's not the actual locale settings that made it stop working, it's the Windows display language. Thank you so very much, I would never have thought to try that.
|
# ? Nov 15, 2023 09:14 |
|
Nice one - gj Ninja Bob. I’ve done a lot of years of excel as a profession and never come across that one before. I guess small thanks to australia being so committed to english.
|
# ? Nov 15, 2023 11:14 |
|
I'm decent on screwing around with formulas but with VBA I'm a complete Google-cut-paste basket case. A program at work exports raw data to Excel to Book1.xlsx on your desktop I have a spreadsheet that can take all that data and format it how I want; I'm trying to make one dummy-proof button (seriously, it's a big button) that will copy raw data from that day's run (as shown in Book1), paste it into my spreadsheet where everything gets table-ized, automated, and a nice results tab shows up. I can't get past step one. I get an out of range error on the first line: Workbooks("Book1.xlsx").Activate I fully admit that I am screwing around with things beyond my understanding, but I feel like I can jury rig this with mostly recorded macro steps if I just get past this.
|
# ? Nov 21, 2023 03:59 |
|
Is the Book1 file already open when you run it and get that error?
|
# ? Nov 21, 2023 04:35 |
|
Wandering Orange posted:Is the Book1 file already open when you run it and get that error? Yes. It isn't saved or anything, so not sure if the file extension might be the issue (it wants to save as an .xlsx when I try)
|
# ? Nov 21, 2023 05:09 |
|
Apologies I'm a few years out of having done any VBA and also a few beers into a frickin'-mondays-dude evening but I think you do have to save the file if you're going to reference it like that. Otherwise I think you can just activate the specific sheet you need if the file is already open regardless of saved status and ultimately there is some sort of application goto code you could use that references both the file and sheet name. Based on your original description, I would recommend your process start with saving that exported data file to a location that's easily accessible like the Downloads file or Desktop or whatever. Then close the file (or don't, shouldn't matter), open your macro workbook, and click the button. You can either build the macro to always open the Book1.xlsx from the same folder location or you could have the macro do a file-open dialog and you pick the file, whatever floats your boat.
|
# ? Nov 21, 2023 05:43 |
|
I was already leery of using a macro-enabled workbook for this purpose (our intranet pops up a warning when you open one, and some people will freak out), but having a macro that saves something is a complete no-go. To be clear, I can do move all this data around myself (I've used the spreadsheet for years), but I'm trying to dummy-proof and one-step it (as much as practicable) for wider use. If I can't, I'd be ok with just a sarcastically big red arrow that says "post data here, using destination formatting"
|
# ? Nov 21, 2023 05:53 |
|
Workbooks is a collection of the workbook objects currently open by the application. So if book1.xlsx isn't open (or real) you can't access it from the workbooks collection. It sounds like you'll need to make a new workbook and [I]then[/] you can call the activated method
|
# ? Nov 21, 2023 06:51 |
|
GD_American posted:
If it’s not saved that might be the issue, although I thought you could still reference them that way without saving…I’m not in front of excel but I know if you use workbook.new you can then reference the resulting workbook with workbooks.name, so it should work. If you haven’t got anything else open you could try something like: Workbooks(2).activate But that could kind of limit your macros usefulness if you have to say “shut all other excel files first” Once you have it active you can do something like: daily_file = activeworkbook.name And then reference it with: workbooks(daily_file).activate Maybe save the output to wherever first, then use workbook.open if it’s saved in the same location or application.filedialog to pick the file. When you open a file it becomes the active file and you can set you name variable.
|
# ? Nov 21, 2023 08:15 |
|
Is there a way to filter a set of values from one sheet and apply it to two other sheets in the same workbook? I have an excel workbook that has three sheets that have around 10,000 rows of values. The first column are anonymized identification numbers along with different values attached to those ID numbers in the neighboring columns (same row). For example, there would be an ID number in the first column, and then attached values such as favorite ice cream flavor in the second column, and then favorite animal in the third column (these aren't what are actually in the cells next to them, they're just numerical values). The problem is that the ID numbers in the first two sheets don't all match the ID numbers in the third sheet. Hundreds to thousands of the ID numbers in the first and second sheet don't match the third sheet. I would like to take the ID numbers from the third sheet, keep the attached values such as ice cream flavor and favorite animal, and then filter out the first and second sheet so that only ID numbers from the third sheet appear in the first and second sheet. The third sheet will be the "anchor" sheet where those are the identification numbers that matter. The first and second sheet only have some ID numbers that match the third sheet, and the first and second sheet don't necessarily match each other, there may be minimal overlap. Is there a way to perform this type of filtering from one sheet to another? Thank you!
|
# ? Nov 22, 2023 22:27 |
|
If the id numbers don't match how do you know the row in sheet 1/2 truly match a row in sheet 3? Because to do this you need to know the relationship between tuples and you've saying the primary key is unreliable
|
# ? Nov 22, 2023 23:19 |
|
Ideal Paradigm posted:Is there a way to filter a set of values from one sheet and apply it to two other sheets in the same workbook? I've assumed that there are 3 sheets each with a table, i.e. Sheet1 > Table1, Sheet2 > Table2, Sheet3 > Table3, and each table has a column named ID. Firstly, you want to be able to catch an event that fires when the source table's (Sheet3) filter updates. Excel doesn't have a built in event for this, so create a new sheet (Sheet4) that has a single formula in it =SUBTOTAL(2,Table3[ID]) . When Sheet3's table is filtered this formula gets recalculated. This allows us to use Sheet4's Worksheet_Calculate() method to handle the filter update. You can hide this sheet after setting it up. The rest is just getting the filtered ids from Sheet3 and applying them to sheets 1 & 2. In the vba editor add this code to the Sheet4 object, and update the constant values to suit your object names: Visual Basic .NET code:
|
# ? Nov 23, 2023 06:44 |
|
That's not vb.net code!
|
# ? Nov 23, 2023 07:08 |
|
HootTheOwl posted:That's not vb.net code! But it does have pretty colours . We need to go on strike until vba code styling is added.
|
# ? Nov 23, 2023 12:05 |
|
Ideal Paradigm posted:Is there a way to filter a set of values from one sheet and apply it to two other sheets in the same workbook? Are there any issues with adding in a hidden dummy column on Sheet 1 and Sheet 2 that reads Y/N based on whether the ID appears in Sheet 3? You can do this using an If(iserror(vlookup())) formula. Vlookup returns an error, if the value you are trying to find doesn't exist in the lookup table.
|
# ? Nov 23, 2023 13:22 |
|
Thank you all for the help so far, I only know the very basics for Excel. If you're willing to bear with me, I have some other questions.HootTheOwl posted:If the id numbers don't match how do you know the row in sheet 1/2 truly match a row in sheet 3? I have looked through the three sheets in the workbook and by randomly searching numbers in the first two sheets, there are some ID numbers from sheets 1 and 2 that match the third sheet. Ninja.Bob posted:Yes, but the only method I know uses vba. There may be better solutions than this. Apologies, I'm not familiar with the VB code. Should I look this up on YouTube on how to input this? And yes, each sheet just has the values inputted into the cells with it's respective ID number in the first column, along with attached values in the subsequent columns. The third sheet will sometimes have the same ID numbers in multiple rows (always grouped together), with dates attached to when measurements were taken, along with the quantity of the measurement in the third column that lines up with the respective date. esquilax posted:Are there any issues with adding in a hidden dummy column on Sheet 1 and Sheet 2 that reads Y/N based on whether the ID appears in Sheet 3? No, I can create a copy of the excel workbook and modify as needed apart from the original copy. The problem is that sometimes on the third sheet, there are multiple entries for the same ID number in different rows (they're always grouped together though), since the columns next to those ID numbers will have a changed value. For instance, let's say the ID number is 1002, there may be three rows with 1002 all grouped together, one row after another, and then the three columns will have different dates since those dates signify when a measurement was taken. Then in the third column the quantity for that measurement is inputted for those ID numbers.
|
# ? Nov 23, 2023 21:55 |
|
Ideal Paradigm posted:
Your intent though is that since person with ID 1002 is listed at least once in Sheet 3, that it is a valid ID and any/all rows for person 1002 should be shown in Sheets 1 and 2? That's what the method I suggested does.
|
# ? Nov 23, 2023 22:24 |
|
So the ID numbers do match just some of them don't have a mate in the other sheet? Is this what you're after:. If, an ID in sheet 1 or 2 is present in sheet 3 Then, keep the row Otherwise, you want the row filtered out and removed?
|
# ? Nov 24, 2023 00:56 |
|
HootTheOwl posted:So the ID numbers do match just some of them don't have a mate in the other sheet? This is how I interpreted the question. I also thought that the Sheet3 values would have their own filter applied and updated, hence the VBA solution. Ideal Paradigm: if you don't need to filter Sheet3 then go with someone else's solution. It's also worth noting that my solution will need to be tweaked slightly to work on mac. Ideal Paradigm posted:Apologies, I'm not familiar with the VB code. Should I look this up on YouTube on how to input this? To add the code: Open the VBA editor using the Alt + F11 shortcut, or via the ribbon in the developer tab > Visual Basic. The developer tab is hidden by default, so you may need to show it by: right clicking the ribbon in an empty space, selecting customise the ribbon, and checking developer in the right hand panel. In the VBA editor in the left hand side project panel there will be a project that correlates with your workbook with a number of objects nested under it, one for each sheet and another for the workbook as a whole. The code I posted needs to go inside the Sheet4 object (assuming that is the new sheet with the subtotal formula), so double click the object and paste in the code in the main window. The code will run whenever Sheet4 is recalculated, so assuming you have added the formula to Sheet4 as previously mentioned, updating the filter on Sheet3 will run the code and filter sheets 1 and 2.
|
# ? Nov 24, 2023 03:05 |
|
Ninja.Bob posted:This is how I interpreted the question. I also thought that the Sheet3 values would have their own filter applied and updated, hence the VBA solution. Ideal Paradigm: if you don't need to filter Sheet3 then go with someone else's solution. It's also worth noting that my solution will need to be tweaked slightly to work on mac. Yeah I thought the ID columns were unreliable so we were using columns b, c, d, etc to create compound keys
|
# ? Nov 24, 2023 03:54 |
|
Need a little help on duration: I have an Excel spreadsheet with 4 columns: ReportedDate, ReportedTime, ResolvedDate, ResolvedTime. The columns are formated as date, and time. I'd like to create a fifth column that has the duration of the event. Can I do this in a single formula, and avoid creating a new column to concat stuff first? *The event could last more than a day, so I need to take the date columns into account for total duration. Hughmoris fucked around with this message at 22:42 on Nov 27, 2023 |
# ? Nov 27, 2023 22:21 |
|
Hughmoris posted:Need a little help on duration: Yes: You can simply subtract B1 from D1. (=D1-B1) Then format column E using the mask H:MM
|
# ? Nov 27, 2023 22:34 |
|
HootTheOwl posted:Yes: You can simply subtract B1 from D1. (=D1-B1) I did a poor job fully explaining my problem. The issue being the event could span multiple days, so I need to take the dates into account as well.
|
# ? Nov 27, 2023 22:41 |
|
Combine the date and time column pairs so you have a date-time data type (yyyy/mm/dd hh:mm:ss) then subtract the two new date-time cells. You shouldn't need a duration or datedif formula for this as Excel handles it natively. You may need to do some math to get it into just hours though. If you can't combine the columns then that requires more brain power than I'm able to provide right now.
|
# ? Nov 27, 2023 22:44 |
|
Hughmoris posted:I did a poor job fully explaining my problem. The issue being the event could span multiple days, so I need to take the dates into account as well. Doing =(Day 2 - Day 1)*24 + HOUR(Time 2 - Time 1) + (MINUTE(Time 2 - Time 1)/60) should work if you want it as a decimal. If you want the formatted number of minutes you could massage it more but I like decimals. for example =(C3 - A3)*24 + HOUR(D3 - B3) + (MINUTE(D3 - B3)/60)
|
# ? Nov 27, 2023 22:48 |
|
=D1-B1+(24*(C1-A1)) End time minus start time, plus 24 for every day across the range Then format the cell
|
# ? Nov 27, 2023 22:54 |
|
You all are some wizards.HootTheOwl posted:=D1-B1+(24*(C1-A1)) I couldn't find a cell format it liked. Displayed '#VALUE'. I'm guessing 'user error' on my end. Loiku posted:Doing =(Day 2 - Day 1)*24 + HOUR(Time 2 - Time 1) + (MINUTE(Time 2 - Time 1)/60) should work if you want it as a decimal. If you want the formatted number of minutes you could massage it more but I like decimals. This worked like a champ. Decimals are actually better now that I'm looking at it. Thanks!
|
# ? Nov 27, 2023 23:17 |
|
I typed in yours exactly as i saw it an excel automatically formatted the cells. For the new column I used H:MM as a custom mask. But I'm glad you got something in place!
|
# ? Nov 27, 2023 23:20 |
|
HootTheOwl posted:=D1-B1+(24*(C1-A1)) It should just be either of the below: =(D2+C2-B2-A2) [Format as Time - Displays as Hours:Minutes] =24*(D2+C2-B2-A2) [Format as Number - Displays as decimal hours]
|
# ? Nov 28, 2023 05:32 |
|
|
# ? May 11, 2024 16:44 |
|
TheLastManStanding posted:This wouldn't work as D1-B1 would return the difference as a decimal day, and then you are adding the day difference in hours. I tested it before I posted it, the times were already split out
|
# ? Nov 28, 2023 05:36 |