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

«‹›3 »

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

A Tartan Tory posted:

Hi folks, wondering if you can clarify something for me because I have less excel experience than I would like.

I'm trying to convert the following excel line into code and I really need to understand how it works.
code:
=MAX(SUMPRODUCT($M15:$M$37,$N15:$N$37,$R15:$R$37)/N14,0)/R14
Now as far as I understand this, the SUMPRODUCT will multiply those numbers in the array with each other (m15 * n15 * r15) and add all the multiplications together to get a sum to use with N14 and finally R14.

What I am struggling to figure out is where the MAX comes into play, as I haven't used it before. How does it effect the calculation and in what order does it actually do things? Does it only use the the maximum value generated by the SUMPRODUCT?

The MAX will pick the max/largest number of SUMPRODUCT($M15:$M$37,$N15:$N$37,$R15:$R$37)/N14 or 0. The order it would perform the formula is:
SUMPRODUCT
/ N14
MAX of above result and 0
Result of above / R14

# ¿ Sep 17, 2014 11:05

Adbot: ADBOT LOVES YOU

# ¿ Apr 27, 2024 16:37

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

TheLastManStanding posted:

just select all your data and table it again. Once it is part of the same table you can toggle the drop down menus on and off by clicking the filter button in the data tab.

Do that. It's not in a table, just a filter applied to those rows.
Click somewhere in the data, Ctrl+A to select it all, Insert tab, Table.

# ¿ May 8, 2015 13:00

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

melon cat posted:

EDIT: A second question (if I may): my UserForm also has a combobox with 5 dropdown options. I want to set it up so even if the user leaves it empty, some sort of default text gets entered into the form. Even if it's placeholder text. Either that, or make the Userform Combobox mandatory. What's the best way to do this?

Ignoring the main question because I'd need to be in front of excel to be certain my suggestion would work, the second question is change this line:

code:


   Cells(logRow, 2) = SubmitterBox.Value

to something like this (I think)

code:


   Submittervalue = if SubmitterBox.Value = "" then
   "Default text", SubmitterBox.Value
    end if

Alternatively to make it mandatory I generally just make one of the first lines of code:

code:


If SubmitterBox.Value = "" then
   Msgbox "Please provide a submitter value, biatch", vbcritical, "Missing information alert"
   Exit sub
End if

# ¿ Aug 28, 2015 08:23

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

And I thought I'd seen overuse of Excel before. That is something else...

It's totally possible to send Outlook emails from Excel, you can even choose to send without prompt or simply pop the filled out email for the user to complete, review and send and there's some handy url validity checking code out there. Phone posting so don't have either at hand. Beyond that you're way out of my scope of somewhat-overusing-Excel.

# ¿ Sep 24, 2015 08:52

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

Xenoborg posted:

Is there a way to sort a spreadsheet by column A, but then also group duplicates of column B together? I've got a list of parts and part locations that it would be really useful to see duplicate parts and/or locations together. At the moment I'm resorting the spreadsheet every group of entries.

If you click on the Data tab there's a big Sort button, hit that and you can add levels of Sort By

# ¿ Feb 19, 2016 07:37

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

TheFallenEvincar posted:

similar situation to the OP, try to fake it till I make it, got a pretty good position open I'm aiming for to get my food in the door and I'm getting this as some poo poo that'll be expected of me:
"taking PDFs; converting them to Excel spreadsheets; then parsing up the data into a format we need. The use of advanced scripts and calculations speeds this up far more than copying and pasting."
I've got some Excel/VBA knowledge but I'm kinda lost. I throw myself at your feet, goons, because gently caress if I know how to use "advanced scripts" and "calculations" to speed that up.

Just from my personal experience - PDF->Excel is a road paved with pain and frustration. Admittedly I never attempted to automate it but even getting well formatted, correct, all inclusive data from a PDF once has been horrible. The only good news was I talked to one of our vendors they advised it was just as easy to provide me with .xlsx as a .pdf.

# ¿ Apr 18, 2016 21:21

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

Bob Morales posted:

So you have to be ultra-careful at every step of importing, exporting to make sure you don't lose those. Excel loves to HELP YOU OUT and hide data especially with CSV's. gently caress.

A not uncommon problem with Excels helpfulness

Abstract posted:

The spreadsheet software Microsoft Excel, when used with default settings, is known to convert gene names to dates and floating-point numbers. A programmatic scan of leading genomics journals reveals that approximately one-fifth of papers with supplementary Excel gene lists contain erroneous gene name conversions.

# ¿ Aug 24, 2016 20:27

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

I inherited some similarly stupid poo poo spreadsheet which allows people access certain areas and bounces authentication off the ActiveDirectory server but ultimately having the data in Excel means there is a risk someone will just break it and have access to everything. It�s kind of a trade off between - we made it kind of secure and we made a functional spreadsheet to provide the data they need.

The whole idea gets worse the more you think about it though. VBA isn�t secure at all because you can just replace the password with the hash of a known password and gain access, then view everything. Or if you�re properly malicious you could edit the code to log all the usernames and passwords that are typed in - because you�re doing all of this in Excel.

# ¿ Jun 4, 2018 20:36

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

Shouldn�t be that long, depending on the complexity of the calculation of course, but no longer than pasting them in.

This may help or may just shift where it takes time.

code:

Application.Calculation = xlCalculationManual
Insert formulas
Application.Calculation = xlCalculationAutomatic

If you�re using a recorded macro it may help to adjust the code, as I see a lot of:

code:

Range(��).select
Activecell.formula blah

You can change this to a single line and make things quicker:

code:

Range(��).formula blah

One further thought, it you have Worksheet level SelectionChange type stuff, it�s worth disabling events while your code runs. Otherwise every time your code enters a value the SelectionChange event will fire.

code:

Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Insert formulas
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

Edit based on your above comment. If your formula is moving by the same amount, like where you�d just normally paste something down and it would work you can still make that work with just
Range(�a1:a1000�).formulaR1C1 = �RC[-1]*10�

But I�m not sure if the formula you�re describing is far more complex than my thinking / not something that can be reproduced so simply.

DRINK ME fucked around with this message at 02:47 on Oct 23, 2018

# ¿ Oct 23, 2018 02:41

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

Without being sure how the macro is working, something like LEFT(cell, LEN(search_string)) maybe.

So if it�s doing a loop through the cells like:
Find(search_string, cell)
You could change this to be:
Find(search_string, Left(cell, Len(search_string)))

This would only find where Smith is at the start of the entry. You may have issues with entries starting with �The� though, depending on your data and your searches.

# ¿ Jan 30, 2019 19:47

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

Xaerael posted:

Hey folks. I need a bit of help. My team leader has given me a problem to solve, and I'm utterly stuck. Here's the story...

I work at an organisation that has clients that have treatment pathways. Our board would like a quarterly spreadsheet that shows how many active clients each service has each quarter. I've worked out how to count the ones with dates correctly, but I can't work out how make it also count the clients who don't have an end date (i.e., currently active clients who have yet to complete their service). Here's some screencaps, and the formula I have so far (customer IDs blanked because of obvious reasons):

What the results need to look like:

What the report data on sheet 2 (Customer Start End) looks like in excel (ignore it being in a table, that was me trying different things):

My current WIP formula:

=COUNTIFS('Customer Start End'!B:B,"<=31/12/2014",'Customer Start End'!C:C,">=01/09/2014") (this would be for C7)

Any help would be amazing. I've tried a bunch of different things, but this sort of thing isn't my forte in excel. Ideally, the solution would be as simple as possible to the end user, so anyone can just grab the report and jam it into the second sheet. We try to have a keep it simple policy, so anyone can pick up a job quickly if someone is off ill.

Thanks in advance!

You could count blank end dates and add the two together for a nice easy solve:
=COUNTIFS('Customer Start End'!B:B,"<=31/12/2014",'Customer Start End'!C:C,">=01/09/2014") + COUNTIFS('Customer Start End'!B:B,"<=31/12/2014",'Customer Start End'!C:C,"")

In your report table you may want to consider adding start/end dates, then you don�t need to hard code dates of each formula, you can just reference the above rows and paste the same formula across (I used calendar years instead of financial so adjust as necessary). You can hide these rows if you need a certain layout/look to it.

=COUNTIFS('Customer Start End'!B:B,"<=" & C5, 'Customer Start End'!C:C,">=" & C4) + COUNTIFS('Customer Start End'!B:B,"<=" & C5,'Customer Start End'!C:C,"")

start date is before the quarter end date AND end date is greater then the quarter start date + start date is before the quarter end date AND end date is blank.

e:fixed a typo in formula

DRINK ME fucked around with this message at 22:46 on Jul 2, 2019

# ¿ Jul 2, 2019 20:52

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

FreshFeesh posted:

I'm trying to create a function that will tell me what the change to our bottom line is when a client either joins or departs our service. The table currently looks like this:

Client A onboarded in February, and we performed work through June. Client B stayed constant through the year, with no change to our bottom line. Client C received services through March but was then offboarded. Client D was onboarded in February but offboarded in April, which cancels out any gain we may have seen.

My goal is to have row H display the total effect to our bottom line over this time period, as shown in the above. Client A increased our recurring revenue by $500, Client B didn't change it, we lost $1000 by losing Client C, and Client D ultimately didn't have an effect either. Ultimately this formula/function will be used across a variety of date ranges (quarterly, annually) and across a large number of clients, so manually entering a count isn't feasible.

tl;dr: Is there a way to compare cells against one another such that if there is a change from NULL to (Data) a count will go up, and if there is a change from (Data) to NULL the same count will go down, all within the same row (range)?

What I think you need to do is add a couple of cells for selecting Start period and End period, use data validation | list and point it to c1:g1.

Then in your Change formula you could use something like this
=Index(c2:g2, match([Start date cell], c$1:g$1, 0)) - Index(c2:g2, match([End date cell], c$1:g$1, 0))

Make sure you lock the header range (using the $ symbol) so when you copy the formula down to the other clients it will still work. Hlookup if you�re not comfortable with index(match), same result and unless your dataset is crazy big there�s no difference.

This should allow you to change your start and end dates selection to see the change for whatever period is required and continue working if you insert a new column for July, Aug, etc.

# ¿ Jul 18, 2019 08:11

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

kumba posted:

I'm dealing with some data conversion between systems and I have a question about how to format an export from one system as an import into another that I'm hoping someone can provide some guidance on. I don't have any way to customize the export itself (and that's one of the reasons we're moving away from this poo poo system), so I have to do some finagling in between to get it in the right format.

Here's the rough idea of my data:

This is employee punch data from our timekeeping system, so these correspond to punches in / punches out. Ignore the fact that some of these punch pairs are identical times (e.g. rows 77 & 78) for the moment. Here's what I'm having trouble with:

I need to insert a column to the left of all the timestamps that, for each row under a specific employee, contains the value in the current Column E. So for this example, I need to shift all those timestamps into column B, and in column A, I need the value 1344 in rows 69 - 78, and the value 1842 in rows 80 - 97. The number of rows is going to fluctuate between employees. I'm assuming there's some way to look for the closest row above with a value that is not a timestamp in a column, and retrieve the ID from said row? I just can't wrap my head around how to accomplish that or if I'm making this too difficult and there's an obvious easy answer.

There's a few other things I need to do but I think I mostly have those settled, this is my sticking point. Any help would be much appreciated!!

The quick way to do it is select from E1:E whatever you last row is.
Ctrl G (for Go to), Special, Blank cells, OK - this will select all blank cells within you current selection.
Then type = and press the up arrow key, hold Ctrl and press Enter - this will enter the formula to pick up the value from the cell above.

Now you should have employee numbers on every row, then you can copy-paste as values if needed, otherwise just cut the column and insert in column A.

# ¿ Aug 2, 2019 14:26

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

SymmetryrtemmyS posted:

Make a macro that copies everything to a new sheet, runs =PROPER(), copies it back, and kills the other sheet.

Im not in front of Excel but you might even be able to do it with something like:
For each value in worksheet
Value = worksheetfunction.proper(Value)

But SymmetryrtemmyS solution would definitely work.

# ¿ Sep 6, 2019 07:28

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

CzarChasm posted:

Have an excel question.

I have data on an excel spreadsheet broken into "info cards" (Small 2D Arrays) that I want to turn into just one row each. I want to build a couple formulas that will grab the data from certain cells and then put that into rows on another sheet.

What I'm trying to do for example is pull all the names from these cards. The first person's name is in Cell D2, person 2 is in D23, person 3 is in D44, and so on and so on. Every 21 rows the pattern repeats.

For Cell A1 on my new sheet I have it using =Sheet1!D3 and that works fantastic. What I want is a simple way to make A2 be =Sheet1!D24 and on and on, so it increments the row value of D by 21 every time, without manually typing it. I'm sure there's a quick way to do this but I'm just blanking and I can't come up with the words to google this correctly.

I think you need some kind of =Offset() but not sure I know the best way to do that.

I tried with the below and it seems okay.
=Offset(Sheet1!$D$3, 21*(ROW()-1),0)

That picks up Sheet1!D3 offset by 21 rows times the current row minus 1.
So the first one is 21 * (1 - 1) = 0, so it picks up D3
Second is 21 * (2 - 1) = 24
And so on.

# ¿ Sep 11, 2019 23:47

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

Xaerael posted:

Hi all, back to bother everyone again!

Is there a simple way to do an in-formula check to make sure a cell contains a date, then output a date as text? All I�ve come up with so far is to use the following:

=if(or(isblank(a1),len(a1)<>5),�invalid�,text(a1,�dd/mm/yyyy�))

Which, obviously just checks the very basics of things, then spews out either invalid, or the date in text as required. I really need it to make sure some chump hasn�t put something dumb like xxxxx or something. This is the last column in my validator for HR, and I�d love it to be finished by the start of next week.

I don�t really want to mess with data validation, as this might end up in the hands of people who are bad at excel.

Thanks in advance!!!

Data validation would be my go to... But since you said no to that, if you want to add code (end up with .xlsm/.xlsb workbook), I�ve seen VBA IsDate used before and google has a lot of examples like:

code:

Function IS_DATE(rng) As Boolean
 
IS_DATE = IsDate(rng)
 
End Function

Then in your cell you can use:
=If(Is_date(a1), text(a1, �dd/mm/yyyy�), �invalid�)

And it�s not a bother at all, I bookmark this thread because most of my work week is spent in Excel doing daft things, fixing issues in �critical� workbooks no-one maintains and repeating mantras like �excel is not a database�.

e: Still had google open and saw this which is a nice non-code solution. Much better.
=If(ISERROR(DATE(DAY(A1),MONTH(A1),YEAR(A1))), �invalid�, text(a1, �dd/mm/yyyy�))

DRINK ME fucked around with this message at 14:48 on Sep 13, 2019

# ¿ Sep 13, 2019 14:40

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

I definitely know that feeling. Excel magic.

There�s probably one user in your future that will surprise you though - or possibly that�s just me. Every time I think I know how to persuade/direct/force users into a behaviour in Excel they prove me wrong. I once put a button that said �discount price� which had some non-related code I wanted run, as well as a 5% discount, and it still wasn�t used all the time.

# ¿ Sep 13, 2019 15:38

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

Kibayasu posted:

Weird date formatting.

Bloody strange stuff, possibly a bug in Excel or the other program.

Maybe try formatting both the first and second date (the incorrect and correct) as text or general and make sure the value pulling through in the formula is the same? Maybe the first row is picking up like .0 that�s loving with the format?

Excel stores dates and times as a number representing the number of days since 1900-Jan-0, plus a fractional portion of a 24 hour day: ddddd.tttttt . This is called a serial date, or serial date-time.

Based on your description I�m more inclined to think one of those weird loving bugs you learn to live with because it�s difficult to even google.

# ¿ Oct 19, 2019 02:07

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

I think I overcame that by creating a theme - which looks painful but isn�t too bad once you get going. This got around the pivot table eating any normal cell formatting when filtered, refreshed or changed.

You can also re-use themes for different workbooks but I never found a smart way of doing that, so I copy in a saved pivot with the existing theme and then it�s available to use in that workbook. Helpful for when you get a �nice�/acceptable corporate colour theme you can apply to everything.

# ¿ Nov 22, 2019 19:36

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

Ahh my bad, I was thinking you meant exclusively in the pivot table area.

Backgrounding the colour seems like a workable solution though, only (very stupid) alternative I can think of would be having code to re-fill cells set to OnChange.

# ¿ Nov 22, 2019 20:41

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

Im not great with conditional formatting but this seems to work:
=(today()-d3)>30

I did that in d3 and then format pasted it down the column. Make sure you use the cell reference without the $ as this will only refer to the first cell.

# ¿ Mar 17, 2020 04:03

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

Protons posted:

That seems legit. How do you do format pasting?

There�s a �Format Painter� tool on the home tab of the ribbon or you can paste special - formats.

# ¿ Mar 17, 2020 21:42

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

I think the lookup table is probably the best solution, otherwise it�s going to be tiered IF statements interspersed with lots of OR and AND which is far messier because I�ve done that before :bang:

If the values you use are likely to change often you could create a reference table. Use that table to populate your lookup column with concatenations (=RefTable!A1&RefTable!A2&RefTable!A3, =RefTable!A1&RefTable!A2&RefTable!A4, so on until you have your 6^3 options populated) and then use the same table for data validation cells in your result column so you can easily select the right value. That�s probably overkill though, unless you need to change the values every week or two.

# ¿ Jun 19, 2020 15:32

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

.xlsx is really .zip containing .xml. If you wanted to you could pull out the xml for your sheet and compare known tags I guess. Like you know your template has �<A1>Title</A1>� and compare that to the completed form, repeat for however many you want to check. I�m not smart on how best to do that.

In Excel I�ve done something similar in the past that seemed to work. I copied my template to another sheet and then on a third sheet used =Template!A1=Hidden_Template_Copy!A1, copied that formula for all the cells I wanted to make sure were the same, and then at the bottom of the sheet I did =AND(A1:Z1000). If any of the expected values had changed that cell would show as FALSE. Then in I set those two sheets it xlveryhidden so the casual observer will never see them.

# ¿ Jul 3, 2020 05:16

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

You can use Worksheetfunction.Vlookup in your VBA.
Something like this (not in front of Excel so may need correcting):
My_lookup_value = worksheetfunction.vlookup(A2,Sheet2!A1:B3,2,0)
Range(�A1�) = My_lookup_value

The formatting of the formulas in VBA can be a bit weird, I don�t think you need to R1C1 format but maybe add your formula in the cell and then record it as a macro to use in your code. Saves time dicking about with getting the quotes in the right places and whatnot.

And yeah, the inelegant solution works fine to:
A1 = vlookup
A1.copy
A1.pastespecial as value

�-
edit:
Half an hour later a little voice in my brain told me I was stupid, this would be much simpler:

Range(�A1�) = worksheetfunction.vlookup(A2,Sheet2!A1:B3,2,0)

DRINK ME fucked around with this message at 05:32 on Jul 17, 2020

# ¿ Jul 17, 2020 05:09

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

If I�m reading that correctly: when a value is entered in column B, move to the next row on column A?

You could do something like this in the worksheet code

code:

Private sub worksheet_change(byVal target as range)

If Target.cells.Count > 1 or Isempty(Target) then exit sub

If target.column = 2 then
   Application.Enableevents = false
   Target.offset(1, -1).Select
   Application.Enableevents = true
End if

End sub

The first line checks to make sure it�s not a multiple selection (like a user selecting data) or is not empty (a user deleting data).

The next line checks we�re in column B (column 2). If we are then the current cell is offset by 1 row and -1 columns (the next row in column A).

DRINK ME fucked around with this message at 23:04 on Aug 30, 2020

# ¿ Aug 30, 2020 23:02

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

hummingbird hoedown posted:

Thanks for the help. I just got around to trying this out but nothing happened. When column B is active, I populate a cell in it, then do anything else, the selected cell behaves as it normally does(Enter moves the active cell on row down, Tab moves the active cell one column to the right, etc.)

Weird stuff. Check you have macro/code enabled. Check you have added it to the worksheet you are using - this needs to be against the sheet, if you look at the project browser in the code view / vba there will be Microsoft Excel Objects and your list of sheets. Pick the sheet you are using and add it there.

A good way to check if it is firing or not would be to add something visual to the code. Like in the first line add msgbox �code fired� and then go change a value in column B. If it�s working it should give you a message box pop up.

# ¿ Sep 13, 2020 23:44

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

I get to deal with this sort of stuff for work every now and then and congrats - that�s some of the worst data I�ve seen.

My first step would be to try all the export file types and see if any of them are a bit cleaner once pulled into Excel, but I�m kind of doubtful with what you have here.

Next step would be open the file, run the below to remove line breaks, carriage returns and then see how you�re looking, I think this might fix some of the main issues.

code:

Bad_char1 = chr(7)
Bad_char2 = chr(10)
Bad_char3 = chr(13)
Good_char = � �

Cells.replace what:=Bad_char1, replacement:=Good_char, lookat:=xlpart, searchorder:=xlbyrows, matchcase:=false, searchformat:=false, replaceformat:=false

Cells.replace what:=Bad_char2, replacement:=Good_char, lookat:=xlpart, searchorder:=xlbyrows, matchcase:=false, searchformat:=false, replaceformat:=false

Cells.replace what:=Bad_char3, replacement:=Good_char, lookat:=xlpart, searchorder:=xlbyrows, matchcase:=false, searchformat:=false, replaceformat:=false

I manually rewrote this on iPhone while looking at the work computer so it might have an error but should be pretty close.

If you still need to do more work, the best general advise I can offer:
*Save your code as you work in Personal Macro Workbook so it�s available whenever you need it without finding the file you saved it in.
*Record a macro of you cleaning it up, then you can convert this code to repeatable actions. Like if you always delete columns B, C, E. You can have this in your cleanup macro.

# ¿ Dec 16, 2020 20:05

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

VBA sorry. Wasn�t even thinking of power query, just hoping removing all those like breaks maybe get your data on the same lines to start with

# ¿ Dec 16, 2020 21:43

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

Glad you got there the easy way mate.

I�m very guilty of using VBA when formulas will do the same job but I do temper that compulsion when I�m doing work others have to use.

# ¿ Dec 17, 2020 01:23

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

That sounds a bit strange. If it�s just the source data that�s changing / being refreshed, can�t you just refresh your pivots, which will also refresh the slicers and you�re done?

I feel like there�s a detail missing in your description?

# ¿ Dec 29, 2020 23:27

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

I think you�re stuck with having to fix that one. From memory there�s nothing in the options to sort it and I think the only way to deal with it is what you�re doing.

Depending on the pain level you could probably code something up - If you are using the same workbook + worksheet you could have it fire with the On Change event and it would automatically correct it. Otherwise maybe stick it in your Personal Macro Workbook and run it yourself. Neither is a great solution but unfortunately Excel is just too smart for us users sometimes.

# ¿ Dec 30, 2020 21:38

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

Turkeybone posted:

Let me get in and find the error that pops up -- I'll make a copy of the file so I can mess around with it. Because I can only change the data source one pivot at a time, I think Excel gets mad because a slicer is connected to two pivot tables that don't match up (have different sized data sources because I can't update them both at the same time).

edit: Here's the error that says I need to disconnect, change source, then reconnect-

edit2: Okay once I typed this specific error into Google I found some other databases -- it looks like yes if I turn the data source into a named range / table it should auto-size and be refreshable without all this disconnecting business.

My bad, I had forgotten it did that, haven�t seen it in a long while. Definitely seems you�re on the right track now. Tables are nice for source data because you can just reference them by name and then hopefully just Refresh All instead of doing the disconnect/reconnect dance.

You might just need to check the Slicer settings as they have some option for remembering items once deleted from the source data - never a desired result for me - and you might not have had to deal with that previously when disconnecting/reconnecting.

# ¿ Dec 31, 2020 19:51

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

Similar idea - Put it all into a single column, add a header/name for the column. Then use that data as the source for a pivot table, drop your column into both the Rows and Values fields. That�ll give you a unique list of users and a count of how many times they appear in the list.

# ¿ Jan 21, 2021 22:20

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

AFAIK you can�t reference p�vot tables like you do tables - being the first time I�ve thought about that it seems silly you can�t but I�ve always just accepted it.

Depending on what I�m doing I usually just reference the cells like you have for vlookups or use getpivotdata formulas. They�re fairly easy to use once you get into them and I find them useful for calling out data I want people to pay attention to.

# ¿ Feb 9, 2021 18:51

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

Narzack posted:

I'm trying to build an invoice tracker, and I want to to count the number of outstanding invoices, in addition to the dollar amount, as well as the number of paid and the amount paid. I'm looking at the countblank formula, but I'm not sure if that's the best way to go. Right now, I have in the C Column the amount the invoice is worth. In the D column is the date paid. I was thinking that if I use countblank, then every time I add another row for a new invoice, I'd have to update the formula. But, then I also realized that I need someway to tie somehow the D column to the C column, so that if there is data in the D column, it will send flag the C column cell to throw that dollar amount into a paid total cell. And if there isn't anything in the D column, to send the corresponding C cell to the unpaid total.

Does that make sense or am I attacking it from completely the wrong angle?

I feel like I'm getting into an area where I would need to take an Excel class or something.

That should be workable, maybe consider using a table to put your invoices in as then formulas referencing your columns don�t need to be updated, they just know to read column Amount Paid instead of all of C1:C100 and then if you extend beyond 100 rows and excel decides the formula doesn�t change that day you might suddenly stop seeing updates and spend a good bit of time hunting around for why.

Outstanding invoices =countif(d1:d10,��)
Counting where d has no value, so invoice issued but not marked paid.

The above assumes you have no blank rows and the formula is only looking at rows with invoices. To expand the functionality a bit:

Outstanding invoices =countifs(c1:c10,�<>�&��,d1:d10,��)
Counting where c has a value and d has no value, so invoice issued but not marked paid.

Outstanding invoices value =sumifs(c1:c10,d1:d10,��)
Summing c where d is blank.

Paid invoices =countif(d1:d10,�<>�&��)
Counting where d is not blank.

Paid invoices value =sumifs(c1:c10,d1:d10,�<>�&��)
Summing c where d is not blank

These should be roughly right but I�m typing them by hand rather than copying from Excel so maybe some minor errors, it should put you on the right path though. Oh and if copying from SA you�ll need to retype the double quotes, for some reason they�re read as something different than the ones on your keyboard.

DRINK ME fucked around with this message at 10:50 on Feb 12, 2021

# ¿ Feb 12, 2021 02:54

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

Narzack posted:

That's amazing! Thank you! I converted my columns to tables like you suggested and adapted the formulas as best I can, and it seems to be working swell. The only issue I see is that there is a value of 1 in the paid invoices, when it should be 0. Is that just how the formula is written, that it can't return a value of 0?

Nah, something has gone wrong, it can return 0 and should if the column is empty but my guess is there�s some value it�s reading.

Trying reselecting the range part of the formula - click into the formula and delete the current bit that you have instead of d1:d10 in the example, and then select it again. It may be you selected more than intended, something below the table or above, or the header.

If that doesn�t resolve it, select the blank cells in that column (leave your header alone, just the cells where your paid dates are going to be entered) and hit delete - might be there is a return or a space in one of the cells, or if you�ve been pasting data from other sources an unprintable character.

Not sure if I�ve mislead you or it�s a typo but your plural �tables�. It should be one table for the whole thing, not each column as a table. Hopefully it already is but if not:
Save a copy of your file (as backup in case this fucks up all your work).
Remove the tables (instructions here).
Select the range you are going to have your invoice data in, say A1:D10 and insert table again.
You will need to reselect ranges in your formulas but this will make life easier - if you do actually have multiple tables- because you can tab between cells and get new rows across everything when you hit the end and it�s just generally nicer to work with.

# ¿ Feb 13, 2021 11:26

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

Narzack posted:

Well, I ~~googled~~ searched(what the verb form of duckduck go? is the past tense duckduckwent?) how to make a table, and it said to click on the top of the column and select format as table. So, I did that to all the columns. And instead of just selecting a range and having to redo the formula again if I go beyond that range, I just made the range the table. So, my PAID INVOICES formula is =COUNTIF(Table4[[#All],[Date Paid]],"<>"&""). My date paid column is actually in E, not D. The OUTSTANDING INVOICES formula is =COUNTIFS(Table1[[#All],[Amount]],"<>"&"",Table4[[#All],[Date Paid]],"") When putting in the range, instead of doing, like C1->C100 or whatever, I just clicked on the table.

Yeah sorry I should have explained it better, normally you�d have it all as a single table.

Your selection in your formula is the entire table, including the header, which is what that 1 is in your Paid Invoices formula, instead of being 0. The words �Date Paid� are being counted as 1 non-blank cell. Reselect just the column where the data goes instead and it will clear it up and
=COUNTIF(Table4[Date Paid],�<>�&��)

# ¿ Feb 13, 2021 21:13

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

It�ll be auto-updating with that. When a new row gets added it�s part of the column [Date Paid], as everything in that column is (except the header), so it will be included in your formula.

# ¿ Feb 14, 2021 03:04

Adbot: ADBOT LOVES YOU

# ¿ Apr 27, 2024 16:37

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

Yeah both of those should work, not sure why the former isn�t working. The counta is definitely the easier option because it�s built to do that and the countif using the �<>�&�� is kind of a workaround - it definitely should still get the same answer but there just isn�t an easy way to say notblank: you either user �<>�&�� or maybe the inverse for has a value �*� would get you to the same place as a wildcard for any value.

I don�t have a preferred method, it�s just whatever comes to mind first when I�m entering the formula.

# ¿ Feb 16, 2021 20:04

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

«‹›3 »