Small Excel Question Not Worth Its Own Thread

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

«‹›82 »

HootTheOwl: May 13, 2012; Hootin and shootin

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

Adbot: ADBOT LOVES YOU

# ? Apr 28, 2024 01:08

Alkanos: Jul 20, 2009; Ia! Ia! Cthulhu Fht-YAWN

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

Xenoborg: Mar 10, 2007

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.

It's mostly only good when you need to check something quick, Evaluate Formula is usually better.

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

Quixzlizx: Jan 7, 2007

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

AG3: Feb 4, 2004; Ask me about spending hundreds of dollars on Mass Effect 2 emoticons and Avatars.; Oven Wrangler

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:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Application.Intersect(Target, Range("A1:M600")) Is Nothing Then

If Target.Cells.Count > 1 Then
Exit Sub
End If

Selection.Worksheet.Cells.FormatConditions.Delete
Selection.FormatConditions.Add xlExpression, , "TRUE"
Selection.FormatConditions(1).Interior.Color = vbWhite
End If
End Sub

This sets the background color of the selected cell to white instead of whatever color it normally is. This works perfectly on my work PC Excel, but not the Excel version on my other PC. They're both Microsoft 365, though I don't know if there's a version difference since they are from two different providers. It's not that the code crashes or anything on the other Excel version, it just doesn't do anything and I can't figure out why, and all the other code in the sheet works just as it should.

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

HootTheOwl: May 13, 2012; Hootin and shootin

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:
code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Application.Intersect(Target, Range("A1:M600")) Is Nothing Then

If Target.Cells.Count > 1 Then
Exit Sub
End If

Selection.Worksheet.Cells.FormatConditions.Delete
Selection.FormatConditions.Add xlExpression, , "TRUE"
Selection.FormatConditions(1).Interior.Color = vbWhite
End If
End Sub
This sets the background color of the selected cell to white instead of whatever color it normally is. This works perfectly on my work PC Excel, but not the Excel version on my other PC. They're both Microsoft 365, though I don't know if there's a version difference since they are from two different providers. It's not that the code crashes or anything on the other Excel version, it just doesn't do anything and I can't figure out why, and all the other code in the sheet works just as it should.

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.

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

DRINK ME: Jul 31, 2006; i cant fix avs like this because idk the bbcode - HTML IS BS MAN

^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

AG3: Feb 4, 2004; Ask me about spending hundreds of dollars on Mass Effect 2 emoticons and Avatars.; Oven Wrangler

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

HootTheOwl: May 13, 2012; Hootin and shootin

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

Ninja.Bob: Mar 31, 2005

HootTheOwl posted:

I think you deleted the parenthesis around your method calls in the bottom block.
...FormatConditions.Delete()
Selection.FormatConditions.Add(xlExpression, , "TRUE")

VBA has two different syntaxes for function calls depending on if they are on a line by themselves or not. Adding parenthesis would change the meaning of the program, see: https://stackoverflow.com/a/15519085/3317913

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:

Another thing to check would be that the code is in the correct worksheet object.
Given you mention changing locale settings, if your language is not English then you may need to change

Visual Basic .NET code:

Selection.FormatConditions.Add xlExpression, , "TRUE"
'e.g. german
Selection.FormatConditions.Add xlExpression, , "WAHR"

# ? Nov 15, 2023 00:39

AG3: Feb 4, 2004; Ask me about spending hundreds of dollars on Mass Effect 2 emoticons and Avatars.; Oven Wrangler

Ninja.Bob posted:

Given you mention changing locale settings, if your language is not English then you may need to change
Visual Basic .NET code:
Selection.FormatConditions.Add xlExpression, , "TRUE"
'e.g. german
Selection.FormatConditions.Add xlExpression, , "WAHR" 

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

DRINK ME: Jul 31, 2006; i cant fix avs like this because idk the bbcode - HTML IS BS MAN

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

GD_American: Jul 21, 2004; LISTEN TO WHAT I HAVE TO SAY AS IT'S INCREDIBLY IMPORTANT!

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

Wandering Orange: Sep 8, 2012

Is the Book1 file already open when you run it and get that error?

# ? Nov 21, 2023 04:35

GD_American: Jul 21, 2004; LISTEN TO WHAT I HAVE TO SAY AS IT'S INCREDIBLY IMPORTANT!

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

Wandering Orange: Sep 8, 2012

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

GD_American: Jul 21, 2004; LISTEN TO WHAT I HAVE TO SAY AS IT'S INCREDIBLY IMPORTANT!

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

HootTheOwl: May 13, 2012; Hootin and shootin

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

DRINK ME: Jul 31, 2006; i cant fix avs like this because idk the bbcode - HTML IS BS MAN

GD_American posted:

Workbooks("Book1.xlsx").Activate

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

Ideal Paradigm: Aug 7, 2005; Trouble at the old mill

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

HootTheOwl: May 13, 2012; Hootin and shootin

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

Ninja.Bob: Mar 31, 2005

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?

Yes, but the only method I know uses vba. There may be better solutions than this.

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:

Option Explicit

' source table
Const sSheet As String = "Sheet3"
Const sTable As String = "Table3"
Const sColumn As String = "ID"

' target table 1
Const t1Sheet As String = "Sheet1"
Const t1Table As String = "Table1"
Const t1Column As String = "ID"

' target table 2
Const t2Sheet As String = "Sheet2"
Const t2Table As String = "Table2"
Const t2Column As String = "ID"


' run when Sheet4 is calculated i.e. when Table3 is filtered.
Private Sub Worksheet_Calculate()
    
    ' get an array of filtered ids from table 3.
    Dim ids() As String
    ids = GetFilteredIds
    
    'apply the filter to other tables
    ThisWorkbook.Worksheets(t1Sheet).ListObjects(t1Table).ListColumns(t1Column).DataBodyRange.AutoFilter 1, ids, xlFilterValues
    ThisWorkbook.Worksheets(t2Sheet).ListObjects(t2Table).ListColumns(t2Column).DataBodyRange.AutoFilter 1, ids, xlFilterValues
    
End Sub

Private Function GetFilteredIds() As Variant
    
    Dim sourceRange As Range
    Set sourceRange = ThisWorkbook.Worksheets(sSheet).ListObjects(sTable).ListColumns(sColumn).DataBodyRange
    
    Dim ids() As String
    ReDim ids(0 To sourceRange.Count)
        
    Dim n As Long
    n = 0
    
    Dim c As Range
    For Each c In sourceRange
        If Not c.EntireRow.Hidden Then
            ids(n) = c.Value
            n = n + 1
        End If
    Next
    
    ReDim Preserve ids(0 To n - 1)
    GetFilteredIds = ids

End Function

# ? Nov 23, 2023 06:44

HootTheOwl: May 13, 2012; Hootin and shootin

That's not vb.net code!

# ? Nov 23, 2023 07:08

Ninja.Bob: Mar 31, 2005

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

esquilax: Jan 3, 2003

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 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!

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

Ideal Paradigm: Aug 7, 2005; Trouble at the old mill

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?
Because to do this you need to know the relationship between tuples and you've saying the primary key is unreliable

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.

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:
Option Explicit

' source table
Const sSheet As String = "Sheet3"
Const sTable As String = "Table3"
Const sColumn As String = "ID"

' target table 1
Const t1Sheet As String = "Sheet1"
Const t1Table As String = "Table1"
Const t1Column As String = "ID"

' target table 2
Const t2Sheet As String = "Sheet2"
Const t2Table As String = "Table2"
Const t2Column As String = "ID"


' run when Sheet4 is calculated i.e. when Table3 is filtered.
Private Sub Worksheet_Calculate()
    
    ' get an array of filtered ids from table 3.
    Dim ids() As String
    ids = GetFilteredIds
    
    'apply the filter to other tables
    ThisWorkbook.Worksheets(t1Sheet).ListObjects(t1Table).ListColumns(t1Column).DataBodyRange.AutoFilter 1, ids, xlFilterValues
    ThisWorkbook.Worksheets(t2Sheet).ListObjects(t2Table).ListColumns(t2Column).DataBodyRange.AutoFilter 1, ids, xlFilterValues
    
End Sub

Private Function GetFilteredIds() As Variant
    
    Dim sourceRange As Range
    Set sourceRange = ThisWorkbook.Worksheets(sSheet).ListObjects(sTable).ListColumns(sColumn).DataBodyRange
    
    Dim ids() As String
    ReDim ids(0 To sourceRange.Count)
        
    Dim n As Long
    n = 0
    
    Dim c As Range
    For Each c In sourceRange
        If Not c.EntireRow.Hidden Then
            ids(n) = c.Value
            n = n + 1
        End If
    Next
    
    ReDim Preserve ids(0 To n - 1)
    GetFilteredIds = ids

End Function

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?

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.

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

esquilax: Jan 3, 2003

Ideal Paradigm posted:

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.

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

HootTheOwl: May 13, 2012; Hootin and shootin

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

Ninja.Bob: Mar 31, 2005

HootTheOwl posted:

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?

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

HootTheOwl: May 13, 2012; Hootin and shootin

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

Hughmoris: Apr 21, 2007; Let's go to the abyss!

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

HootTheOwl: May 13, 2012; Hootin and shootin

Hughmoris posted:

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?

Yes: You can simply subtract B1 from D1. (=D1-B1)
Then format column E using the mask H:MM

# ? Nov 27, 2023 22:34

Hughmoris: Apr 21, 2007; Let's go to the abyss!

HootTheOwl posted:

Yes: You can simply subtract B1 from D1. (=D1-B1)
Then format column E using the mask H:MM

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

Wandering Orange: Sep 8, 2012

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

Loiku: Jul 10, 2007

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

HootTheOwl: May 13, 2012; Hootin and shootin

=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

Hughmoris: Apr 21, 2007; Let's go to the abyss!

You all are some wizards.

HootTheOwl posted:

=D1-B1+(24*(C1-A1))
End time minus start time, plus 24 for every day across the range
Then format the cell

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.

for example =(C3 - A3)*24 + HOUR(D3 - B3) + (MINUTE(D3 - B3)/60)

This worked like a champ. Decimals are actually better now that I'm looking at it. Thanks!

# ? Nov 27, 2023 23:17

HootTheOwl: May 13, 2012; Hootin and shootin

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

TheLastManStanding: Jan 14, 2008; Mash Buttons!

HootTheOwl posted:

=D1-B1+(24*(C1-A1))
End time minus start time, plus 24 for every day across the range
Then format the cell

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.

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

Adbot: ADBOT LOVES YOU

# ? Apr 28, 2024 01:08

HootTheOwl: May 13, 2012; Hootin and shootin

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.

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]

I tested it before I posted it, the times were already split out

# ? Nov 28, 2023 05:36

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

«‹›82 »