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 »

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

Looten Plunder posted:

I'm creating an agent performance dashboard at work. I run a report and paste the data in excel then have a dashboard that summarises it by employee. I have a bunch of rules that filters based on the employee name that's input into cell B3. I currently have the following macro for filtering a pivot table based on the employee name in B3 to show the assessments that were completed for them
code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xPTable As PivotTable
    Dim xPFile As PivotField
    Dim xStr As String
    On Error Resume Next
    If Intersect(Target, Range("B3:B3")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Set xPTable = Worksheets("SummarySheet").PivotTables("EvalSummary")
    Set xPFile = xPTable.PivotFields("Agent Name")
    xStr = Target.Text
    xPFile.ClearAllFilters
    xPFile.CurrentPage = xStr
    Application.ScreenUpdating = True
End Sub
Currently, if I don't have an employee name in cell B3 it displays every assessment. How do I alter that macro to filter to none unless an employee name has been input?

I think if you move the target text and set it to something that won�t get any results, whether that�s a string or �(Blank)� or ��
I�m not sure what will work off the top of my head but I think one of them should do it.

code:

�Leave this line where in the same place but don�t exit. Just set the value that will get no results and then continue to filter the pivot

 If Intersect(Target, Range("B3")) Is Nothing Then
    xStr = �nothing to filter�
Else
    xStr = Target.Text
End if

    Application.ScreenUpdating = False
    Set xPTable = Worksheets("SummarySheet").PivotTables("EvalSummary")
    Set xPFile = xPTable.PivotFields("Agent Name")
    xPFile.ClearAllFilters
    xPFile.CurrentPage = xStr
    Application.ScreenUpdating = True
End Sub

# ? Jul 21, 2022 12:52

Adbot: ADBOT LOVES YOU

# ? May 18, 2024 00:42

Lib and let die: Aug 26, 2004

I don't understand lambdas in python, and I don't understand them in excel. why create a function that's just getting thrown away???????

# ? Jul 21, 2022 13:33

nielsm: Jun 1, 2009

Read the above page about it. In Excel you can put the lambda function into the Name Manager instead of in a cell, and then you suddenly have a custom named function, without using VBA.

# ? Jul 21, 2022 13:35

HootTheOwl: May 13, 2012; Hootin and shootin

Lib and let die posted:

I don't understand lambdas in python, and I don't understand them in excel. why create a function that's just getting thrown away???????

Lambdas let you define your own function ad hoc and they're amazing and cool and they're my friend.

# ? Jul 21, 2022 13:44

HootTheOwl: May 13, 2012; Hootin and shootin

Looten Plunder posted:

I'm creating an agent performance dashboard at work. I run a report and paste the data in excel then have a dashboard that summarises it by employee. I have a bunch of rules that filters based on the employee name that's input into cell B3. I currently have the following macro for filtering a pivot table based on the employee name in B3 to show the assessments that were completed for them
code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xPTable As PivotTable
    Dim xPFile As PivotField
    Dim xStr As String
    On Error Resume Next
    If Intersect(Target, Range("B3:B3")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Set xPTable = Worksheets("SummarySheet").PivotTables("EvalSummary")
    Set xPFile = xPTable.PivotFields("Agent Name")
    xStr = Target.Text
    xPFile.ClearAllFilters
    xPFile.CurrentPage = xStr
    Application.ScreenUpdating = True
End Sub
Currently, if I don't have an employee name in cell B3 it displays every assessment. How do I alter that macro to filter to none unless an employee name has been input?

Doesn't exit sub exit the current block and proceed?
So you're only ending your if block. I think you want return there instead.

# ? Jul 21, 2022 13:50

Harminoff: Oct 24, 2005; ð½

Don't know how new it is, but flash fill (cntrl e) is pretty amazing

https://dollarexcel.com/what-does-ctrl-e-do-in-excel/

# ? Jul 21, 2022 14:06

Looten Plunder: Jul 11, 2006; Grimey Drawer

HootTheOwl posted:

Help

DRINK ME posted:

Help

Thanks for your help guys. Both were logical and help me diagnose the issue. Unfortunately both your solutions just resulted in the sheet crashing/closing but I found a workaround.
I just added a blank row to my data and hid it and added

code:

If xPFile.CurrentPage = "(All)" Then xPFile.CurrentPage = "(blank)"

which did the trick.

# ? Jul 21, 2022 14:59

neogeo0823: Jul 4, 2007; NO THAT'S NOT ME!!

Thanks for the suggestions so far, everyone. I'm still kinda trying to wrap my head around what I need to do, but I'm getting the pieces together.

So, the current question I have is, is there a way to take an input from a cell, find it in a row, and have the cell number be able to be used as a variable in a formula? The way the grid is set up, all the hours are listed in individual cells in row 2. If I input an hour value, I want to have excel find that value, then take that cell number and look down from it for every check mark in that column, and every row it finds a check mark, I want it to pull data from the left of that check mark and paste it in a different area.

So, if someone types in, say, 16500 into the input box, I want the formula to search for the hour value in row 2. It'll find that in cell P2. Then, I want the formula to use P2 as a variable, and search down the entire P column for check marks. Every row it finds a checkmark in, P5, P9, P12, etc, etc etc, I want it to copy the data in cells A5:C5, A9:C9, A12:C12, etc etc etc to a different area. How can I best accomplish that? Even more helpful would be if you have any examples I can follow for a start.

# ? Jul 21, 2022 15:50

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

You can do that with the INDIRECT function, but be wary as that's one of those volatile functions and that way be dragons depending on how much data you're working with

# ? Jul 21, 2022 18:34

nielsm: Jun 1, 2009

To me that sounds like a "definitely not well suited for a spreadsheet" use case. Have you considered using a real database?

# ? Jul 21, 2022 18:53

neogeo0823: Jul 4, 2007; NO THAT'S NOT ME!!

nielsm posted:

To me that sounds like a "definitely not well suited for a spreadsheet" use case. Have you considered using a real database?

I'd have to be given access to a real database to do that. As it is, I'm being tasked to make something happen with essentially a few .pdf files, a Microsoft Office suite, and a whooooooole lot of looking poo poo up and cross referencing things. I don't have an office, I'm not a keyboard jockey. I'm a wrench turner, who happens to have enough familiarity with computers that I ended up being assigned to basically all of the logistical crap we need to do. It is what it is, is essentially what I'm saying.

I really do appreciate the help from all of you though.

# ? Jul 21, 2022 19:20

neogeo0823: Jul 4, 2007; NO THAT'S NOT ME!!

Double post, but aha, I managed to get the first part working.

I have a sheet titled "PM List", with the grid and all the stuff I want to copy, then another sheet for testing code and formula and the like. Using CELL, INDEX, and MATCH, I'm able to get a result that I think I can use for the next step of this thing. The formula ended up looking like this:

=CELL("col",INDEX('PM List'!F2:AC2,MATCH(E3,'PM List'!F2:AC2,0)))

That allows me to type in an hour interval into cell E3 and have F4, where I've set this code, output the column number of the result from the other sheet. At first, I tried the address info type, but instead of just a cell address, it also outputs the whole document name and sheet name. So, if I can work the column number into the next step of whatever code monster this turns out to be, then I can progress from there.

If any of you guys have an idea to proceed from here, my next step is figuring out how to have it look down the given column, and copy and paste the data every time it finds a checkmark.

# ? Jul 21, 2022 20:04

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

Looten Plunder posted:

Thanks for your help guys. Both were logical and help me diagnose the issue. Unfortunately both your solutions just resulted in the sheet crashing/closing but I found a workaround.
I just added a blank row to my data and hid it and added
code:
If xPFile.CurrentPage = "(All)" Then xPFile.CurrentPage = "(blank)"
which did the trick.

Nice work. I�ve never used that way to filter a pivot so I was guessing, I do it like this (now that I�m in front of a computer):

Sheets(�sheet name�).pivottable(�pivot name�).pivotfields(�field name�).currentpage = variable

I probably used the macro creator to record me filtering the pivot table and then adapted it from there.

# ? Jul 22, 2022 04:05

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

neogeo0823 posted:

Double post, but aha, I managed to get the first part working.

I have a sheet titled "PM List", with the grid and all the stuff I want to copy, then another sheet for testing code and formula and the like. Using CELL, INDEX, and MATCH, I'm able to get a result that I think I can use for the next step of this thing. The formula ended up looking like this:

=CELL("col",INDEX('PM List'!F2:AC2,MATCH(E3,'PM List'!F2:AC2,0)))

That allows me to type in an hour interval into cell E3 and have F4, where I've set this code, output the column number of the result from the other sheet. At first, I tried the address info type, but instead of just a cell address, it also outputs the whole document name and sheet name. So, if I can work the column number into the next step of whatever code monster this turns out to be, then I can progress from there.

If any of you guys have an idea to proceed from here, my next step is figuring out how to have it look down the given column, and copy and paste the data every time it finds a checkmark.

I was thinking about this and what if you did something like this?
https://exceljet.net/formula/unique-values-with-criteria

I gave it a quick try and it seems to work really well. I�ve used Unique before but never with Filter, Unique is great because it does the spill thing where a formula in A2 can populate all the rows below it with values. You may need to wrap the whole thing in Iferror because unique + blank breaks.

# ? Jul 22, 2022 05:58

Looten Plunder: Jul 11, 2006; Grimey Drawer

Re Office/Excel 365

Is there a way to share a workbook and allow edits/changes but not allow those changes to be saved?

I want users to be able go in and create a summary using my dashboard, make changes, edits, delete any unused stuff then allow them to export or save those changes as a standalone summary, but I still want my initial default dashboard template with all the formulas to be what loads up when the file is clicked on again.

Is it going to be a case of having to completely lock down the file to begin with and force users to download a local copy before they're allowed to change anything?

# ? Jul 22, 2022 06:17

nielsm: Jun 1, 2009

That sounds like you want a Template file. XLTX isn't it?
The basis of those is that if you open it normally from File Explorer by double clicking you get a new file that's a copy of the template.
But I don't think it works properly with SharePoint document libraries.

# ? Jul 22, 2022 06:27

neogeo0823: Jul 4, 2007; NO THAT'S NOT ME!!

DRINK ME posted:

I was thinking about this and what if you did something like this?
https://exceljet.net/formula/unique-values-with-criteria

I gave it a quick try and it seems to work really well. I�ve used Unique before but never with Filter, Unique is great because it does the spill thing where a formula in A2 can populate all the rows below it with values. You may need to wrap the whole thing in Iferror because unique + blank breaks.

This ended up leading me down a hole that ended up solving the next step of the problem. I ended up not needing the UNIQUE function, as there's no repeats in the PM List. So, what I did here, was create a named range that encompassed all of the data in the PM_List sheet, then used it as an array reference for using dynamically defined ranges based on cell values. I still need to clean it up a bit more, but the formula currently looks like this:

=FILTER('PM_List'!A1:C200,INDEX(PM_data,1,F4):INDEX(PM_data,F7,F4)=B4)

F4 is the cell in my code sheet showing the column number that the hours result from my previous posts was found in. F7 is the total number of rows in the PM_data named range, and B4 is a cell containing a checkmark to compare against. What this does, is look at the column result from selecting the hour interval, then using the above data, it sets the range to be the correct column of checkmarks, then pulls the results and pastes it like it should. So now, I just need to get the needed materials into a properly formatted list of their own and I can repeat this for that and I'll have my dynamic list all set.

One question I have now is, most of the procedures don't require much, but a few of them are major jobs that will require dozens of parts. It's not really feasible to put all those parts into a single cell. Is there a way to have a group of cells be the entire result when searching? Like, is there a way to set it up so that when I FILTER for, say, a PM number? Like, if I have two results I want to see, and one of them is like 3 cells total, and the other is 12 cells total, is there an easy way to FILTER both groups into the results?

# ? Jul 22, 2022 17:42

HootTheOwl: May 13, 2012; Hootin and shootin

Nested ifs and concatenation

Sorry, I thinki misunderstood what you asked for:
I would try for a macro

# ? Jul 23, 2022 21:24

Looten Plunder: Jul 11, 2006; Grimey Drawer

I'm trying to create a macro to copy a worksheet, create a new document with a filename based on the value in A3, paste the worksheet with just the values, columnwidths and cell formatting.
I can get get the macro to work up to the pasting part and then I get an Error 400, which in debug mode pops as a "Run time error 1004: Application defined or object defined error"

This is what I've cobbled together:

code:

Sub MySheetCopy()


    Dim mySourceWB As Workbook
    Dim mySourceSheet As Worksheet
    Dim myDestWB As Workbook
    Dim Path As String
    Dim myNewFileName As String
    
'   First capture current workbook and worksheet
    Set mySourceWB = ActiveWorkbook
    Set mySourceSheet = ActiveSheet


'   Build new file name based
    Path = "C:\Users\Redacted\Desktop\DesktopDocs\Test Folder"
    myNewFileName = Path & Range("A2") & ".xlsx"
   

'   Add new workbook and save with name of sheet from other file
    Workbooks.Add
    ActiveWorkbook.SaveAs filename:=myNewFileName
    Set myDestWB = ActiveWorkbook
    
'   Copy over sheet from previous file
    mySourceWB.Activate
    Cells.Copy
    myDestWB.Activate
    Range("A1").Select
    ActiveSheet.PasteSpecial Paste:=xlPasteFormats
    ActiveSheet.PasteSpecial Paste:=xlPasteFormatsxlPasteColumnWidths
    ActiveSheet.PasteSpecial Paste:=xlPasteFormatsxlPasteValuesAndNumberFormats
    Application.CutCopyMode = False
    
'   Resave new workbook
    ActiveWorkbook.Save


End Sub

In debug mode I can run it successfully all the way through to the "Range("A1).Select line.
Can anyone help me out?

# ? Jul 24, 2022 14:31

Fingerless Gloves: May 21, 2011; ... aaand also go away and don't come back

Not got anything up to test, but would you also need to define a worksheet? Along the lines of

activeworkbook.activeworksheet.range("A1").select

# ? Jul 24, 2022 17:25

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

Looten Plunder posted:

I'm trying to create a macro to copy a worksheet, create a new document with a filename based on the value in A3, paste the worksheet with just the values, columnwidths and cell formatting.
I can get get the macro to work up to the pasting part and then I get an Error 400, which in debug mode pops as a "Run time error 1004: Application defined or object defined error"

This is what I've cobbled together:
code:
Sub MySheetCopy()


    Dim mySourceWB As Workbook
    Dim mySourceSheet As Worksheet
    Dim myDestWB As Workbook
    Dim Path As String
    Dim myNewFileName As String
    
'   First capture current workbook and worksheet
    Set mySourceWB = ActiveWorkbook
    Set mySourceSheet = ActiveSheet


'   Build new file name based
    Path = "C:\Users\Redacted\Desktop\DesktopDocs\Test Folder"
    myNewFileName = Path & Range("A2") & ".xlsx"
   

'   Add new workbook and save with name of sheet from other file
    Workbooks.Add
    ActiveWorkbook.SaveAs filename:=myNewFileName
    Set myDestWB = ActiveWorkbook
    
'   Copy over sheet from previous file
    mySourceWB.Activate
    Cells.Copy
    myDestWB.Activate
    Range("A1").Select
    ActiveSheet.PasteSpecial Paste:=xlPasteFormats
    ActiveSheet.PasteSpecial Paste:=xlPasteFormatsxlPasteColumnWidths
    ActiveSheet.PasteSpecial Paste:=xlPasteFormatsxlPasteValuesAndNumberFormats
    Application.CutCopyMode = False
    
'   Resave new workbook
    ActiveWorkbook.Save


End Sub
In debug mode I can run it successfully all the way through to the "Range("A1).Select line.
Can anyone help me out?

Your pastes are a little bit wrong I think, you don�t need the xlpasteformats in front of them. Quickest way to get them right is record a macro and then do those actions, or you can copy these:

code:

Activesheet.pastespecial paste:=xlpasteformats
Activesheet.pastespecial paste:=xlpastecolumnwidths 
Activesheet.pastespecial paste:=xlpastevalues

In the description you said the filename is in A3 and in the code it�s using A2. Probably just a typo in on of them since it�s proceeding past that point.

# ? Jul 24, 2022 22:30

fosborb: Dec 15, 2006; Chronic Good Poster

Rakeris posted:

Nope....indexing and matching over here. When I first saw xlookup I was like holy poo poo that is awesome! Then found out a few minutes later I couldn't use it hah.

haha holy poo poo this is amazing

quote:

Example 6 uses the SUM function, and two nested XLOOKUP functions, to sum all the values between two ranges. In this case, we want to sum the values for grapes, bananas, and include pears, which are between the two.

https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929

this would have saved so much time for 10 years ago me

# ? Jul 25, 2022 01:46

HootTheOwl: May 13, 2012; Hootin and shootin

Looten Plunder posted:

I'm trying to create a macro to copy a worksheet, create a new document with a filename based on the value in A3, paste the worksheet with just the values, columnwidths and cell formatting.
I can get get the macro to work up to the pasting part and then I get an Error 400, which in debug mode pops as a "Run time error 1004: Application defined or object defined error"

This is what I've cobbled together:
code:
Sub MySheetCopy()


    Dim mySourceWB As Workbook
    Dim mySourceSheet As Worksheet
    Dim myDestWB As Workbook
    Dim Path As String
    Dim myNewFileName As String
    
'   First capture current workbook and worksheet
    Set mySourceWB = ActiveWorkbook
    Set mySourceSheet = ActiveSheet


'   Build new file name based
    Path = "C:\Users\Redacted\Desktop\DesktopDocs\Test Folder"
    myNewFileName = Path & Range("A2") & ".xlsx"
   

'   Add new workbook and save with name of sheet from other file
    Workbooks.Add
    ActiveWorkbook.SaveAs filename:=myNewFileName
    Set myDestWB = ActiveWorkbook
    
'   Copy over sheet from previous file
    mySourceWB.Activate
    Cells.Copy
    myDestWB.Activate
    Range("A1").Select
    ActiveSheet.PasteSpecial Paste:=xlPasteFormats
    ActiveSheet.PasteSpecial Paste:=xlPasteFormatsxlPasteColumnWidths
    ActiveSheet.PasteSpecial Paste:=xlPasteFormatsxlPasteValuesAndNumberFormats
    Application.CutCopyMode = False
    
'   Resave new workbook
    ActiveWorkbook.Save


End Sub
In debug mode I can run it successfully all the way through to the "Range("A1).Select line.
Can anyone help me out?

First, your filepath is wrong: you're missing the slash between test folder and cell A3 (A2?)
Second I don't believe you can call .activate a workbook that isn't open
E: third, save as is going to make a complete copy of your work book, no? There's got to be a better way to do that.

HootTheOwl fucked around with this message at 04:31 on Jul 25, 2022

# ? Jul 25, 2022 04:27

Looten Plunder: Jul 11, 2006; Grimey Drawer

DRINK ME posted:

Macro Help

Fingerless Gloves posted:

Macro Help

Thanks heaps guys - both of your input helped me out, along with someone else from Reddit. It ended up looking like this and appears to work great.

code:

    Sub MySheetCopy()
    
    
    Dim mySourceWB As Workbook, myDestWB As Workbook
    Dim mySourceSheet As Worksheet, myDestSheet As Worksheet
    Dim Path As String, myNewFileName As String
        
    '   First capture current workbook and worksheet
        Set mySourceWB = ThisWorkbook
        Set mySourceSheet = ThisWorkbook.ActiveSheet
    
    
    '   Build new file name based
        Path = "https://companyname.sharepoint.com/" & "sites/Redacted/Document%20List/AP%20Documents/BAU%20Documents/Consultant%20Eval%20Summaries/"
        myNewFileName = Path & Range("A2") & ".xlsx"
       
    
    '   Add new workbook and save with name of sheet from other file
        Workbooks.Add
    Application.DisplayAlerts = False
        ActiveWorkbook.SaveAs filename:=myNewFileName
    Application.DisplayAlerts = True
        Set myDestWB = ActiveWorkbook
        Set myDestSheet = ActiveWorkbook.ActiveSheet
        
    '   Copy over sheet from previous file
        mySourceSheet.Cells.Copy
        myDestSheet.Range("A1").PasteSpecial Paste:=xlPasteFormats
        myDestSheet.Range("A1").PasteSpecial Paste:=xlPasteColumnWidths
        myDestSheet.Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    Application.CutCopyMode = False
        
    '   Resave new workbook
        ActiveWorkbook.Save
    
    
    End Sub

# ? Jul 25, 2022 05:57

neogeo0823: Jul 4, 2007; NO THAT'S NOT ME!!

HootTheOwl posted:

Nested ifs and concatenation

Sorry, I thinki misunderstood what you asked for:
I would try for a macro

Eh... After looking at the data I want to pull... gently caress it, I'll just put all the numbered parts, all the consumables, and all the extraneous tools into 1 cell for each group and just have some cells be jam packed with lots of information. That way I can just copy the code used for the first half, change the cell references, and have it work for the latter half. It's nice that I get to sit down at a computer and just tune out work for a bit, but I am not getting paid extra to develop this tool, and frankly this will work far and away better than the set expectation of "make a list we can reference" that's been placed in front of me, so, I call it a win either way.

Thanks though, to everyone that helped me out! Other than the scope of work the latter half of this project is turning into, it's been fun.

# ? Jul 25, 2022 23:03

neogeo0823: Jul 4, 2007; NO THAT'S NOT ME!!

Is it really a double post if you posted yesterday?

Anyway, I have a hopefully easily solvable question. I'm working on the 2nd half of my spreadsheet, and it's coming along nicely. I'm having a cosmetic problem though, concerning data being pulled with lots of blank cells. Blank cells are fine and good to pull. I want there to be blank cells. Not every entry is going to have data in every cell. but when I FILTER those cells, the blank cells get pasted to the new section of the sheet with a zero in them instead of being completely blank. is there anything I can do to make those cells just be blank? I attempted to add a " " in the [if_empty] section of the FILTER function, but that didn't seem to change anything.

# ? Jul 26, 2022 23:39

HootTheOwl: May 13, 2012; Hootin and shootin

Try nesting ISBLANK into your filter condition
Or, more easily: <> ""
(Not equal to emptyquote)

HootTheOwl fucked around with this message at 23:50 on Jul 26, 2022

# ? Jul 26, 2022 23:45

neogeo0823: Jul 4, 2007; NO THAT'S NOT ME!!

HootTheOwl posted:

Try nesting ISBLANK into your filter condition
Or, more easily: <> ""
(Not equal to emptyquote)

Wouldn't that result in the empty cells being excluded from the FILTER, and thus not getting pasted over to the result area? Or am I reading it wrong?

# ? Jul 27, 2022 01:21

HootTheOwl: May 13, 2012; Hootin and shootin

neogeo0823 posted:

Wouldn't that result in the empty cells being excluded from the FILTER, and thus not getting pasted over to the result area? Or am I reading it wrong?

Right. Sorry I meant: In your filter you basically have two outputs: The desired one and then a special branch if the value is blank.

code:

if isBlank
  return ""
else
  return what you have now

Are you copying numbers? Is your destination cells formatted to put blank data as numbers which are forcing one signifignt digit?

HootTheOwl fucked around with this message at 01:40 on Jul 27, 2022

# ? Jul 27, 2022 01:36

neogeo0823: Jul 4, 2007; NO THAT'S NOT ME!!

HootTheOwl posted:

Right. Sorry I meant: In your filter you basically have two outputs: The desired one and then a special branch if the value is blank.
code:
if isBlank
  return ""
else
  return what you have now
Are you copying numbers? Is your destination cells formatted to put blank data as numbers which are forcing one signifignt digit?

I'm copying over strings of text. I did try formatting the destination cells under General, and Text, but both showed no change to the output. The formula I'm using is essentially the same as my previously posted one, which is:

=FILTER('PM_List'!A1:C200,INDEX(PM_data,1,F4):INDEX(PM_data,F7,F4)=B4)

How do i format that properly to include this? I had tried adding , " " between the B4 and the ) at the end, which the tooltip showed as being in the [if_empty] section of that function's code, but it didn't change anything that I could see.

# ? Jul 27, 2022 01:56

HootTheOwl: May 13, 2012; Hootin and shootin

A lambda :swoon:

But also 0 is just what index/match returns when the cell is blank, so you might need to just manually assign all 0's as blanks. Of course this won't work if zero is an acceptable value.

# ? Jul 27, 2022 02:20

neogeo0823: Jul 4, 2007; NO THAT'S NOT ME!!

HootTheOwl posted:

A lambda

But also 0 is just what index/match returns when the cell is blank, so you might need to just manually assign all 0's as blanks. Of course this won't work if zero is an acceptable value.

Thankfully, 0 is not an accepted value, and I found an answer. Turns out I needed to go to File > Options > Advanced, and under "display options for this sheet", uncheck the box that says "show a zero in cells that have zero value". It was really well hidden, comparative to other programs where I've had to do similar.

# ? Jul 27, 2022 12:43

neogeo0823: Jul 4, 2007; NO THAT'S NOT ME!!

You know, I keep thinking I'm done with bugging you guys, but I keep finding small poo poo I gotta figure out.

So, recall how I said I was just gonna cram a bunch of stuff into certain cells? Is there any way to automatically have the cells word wrap when they flood from the FILTER formula cell? I've selected the entire area, plus a bit extra in each direction, where the information could potentially spill into and set it to word wrap, but once you change the input and have the formula rework itself, the word wrap stops working and most of the information just disappears behind the next cell. If I select all the cells that flooded, I can toggle off-then-on the word wrap, and that works, but obviously I don't wanna have to do that every time.

EDIT: Googling around a bit, I could try inserting line breaks into the source cells? The columns for output are going to be a fixed width so that everything will print on a sheet of paper, so I can possibly format around that width requirement. Any other ideas anyone else has?

neogeo0823 fucked around with this message at 00:08 on Jul 28, 2022

# ? Jul 28, 2022 00:05

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

I can�t tell if this is a bug or you�re talking about something else and I can�t quite picture it:
*cell a2 (for example) has wrap text enabled. Text is wrapped and height is correct for the length of the text
Formula changes and:
*cell a2 has wrap text enabled. Text is no longer wrapped? Height is the same/adjusted?

If you are just talking about height you can use Cells.EntireRow.AutoFit to update it after your formula changes.

# ? Jul 28, 2022 01:56

neogeo0823: Jul 4, 2007; NO THAT'S NOT ME!!

DRINK ME posted:

I can�t tell if this is a bug or you�re talking about something else and I can�t quite picture it:
*cell a2 (for example) has wrap text enabled. Text is wrapped and height is correct for the length of the text
Formula changes and:
*cell a2 has wrap text enabled. Text is no longer wrapped? Height is the same/adjusted?

If you are just talking about height you can use Cells.EntireRow.AutoFit to update it after your formula changes.

So, I've got a formula in cell A2, that pulls data from other sheets based on input from a drop down list in cell B1. It populates that data into cells A2:F100, depending on how many rows it ends up pulling from the other sheets. The cells in this sheet are not wide enough to display the data without text wrapping. So, I select a value in B1, and A2 populates, let's say, 20 rows. The text needs to be wrapped, so I select A2:F100, and hit the text wrap button. The text wraps just fine like it should. Now, I select a different value from the list. A2 recalculates and populates, say, 30 rows. However, the text doesn't wrap like it should. The cells A2:F100 still have wrap enabled, and toggling wrap off and on on cells A2:F100 gets them to wrap properly.

What is the most advised way to automatically fix that?

# ? Jul 28, 2022 12:26

nielsm: Jun 1, 2009

Is wrapping enabled on the source data in the other sheets?

# ? Jul 28, 2022 12:42

neogeo0823: Jul 4, 2007; NO THAT'S NOT ME!!

nielsm posted:

Is wrapping enabled on the source data in the other sheets?

Yes, and that did not change the outcome.

# ? Jul 28, 2022 13:35

HootTheOwl: May 13, 2012; Hootin and shootin

And Autofit is enabled?

# ? Jul 28, 2022 13:41

neogeo0823: Jul 4, 2007; NO THAT'S NOT ME!!

HootTheOwl posted:

And Autofit is enabled?

I'm not sure how to enable it, and Google isn't helping much. I know that when I click the autofit button, it seems to work, but I don't see an option to turn it off or on.

# ? Jul 28, 2022 17:03

Adbot: ADBOT LOVES YOU

# ? May 18, 2024 00:42

HootTheOwl: May 13, 2012; Hootin and shootin

neogeo0823 posted:

I'm not sure how to enable it, and Google isn't helping much. I know that when I click the autofit button, it seems to work, but I don't see an option to turn it off or on.

Try this?

Microsoft support says posted:

On the Home tab, in the Cells group, click Format. Under Cell Size, click AutoFit Row Height. Tip: To quickly autofit all rows on the worksheet, click the Select All button, and then double-click the boundary below one of the row headings.

# ? Jul 28, 2022 17:10

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

«‹›82 »