|
DRINK ME posted: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? 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. Turkeybone fucked around with this message at 15:14 on Dec 31, 2020 |
#
?
Dec 31, 2020 15:06
|
|
|
|
| # ? May 11, 2024 05:33 |
|
|
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). 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
|
|
|
 This is in a table with name MainTable. What formula can I put in M5 to make it take the average of all columns to the right of it? Hard coding N5:R5 worked, until I added 'A N Other'. Sad Panda fucked around with this message at 11:56 on Jan 2, 2021 |
|
#
?
Jan 2, 2021 11:51
|
|
|
Sad Panda posted:
You could just put in N5:XFD5 and call it a day.
|
|
#
?
Jan 2, 2021 14:47
|
|
|
As long as you keep A N Other as the final column of the range and insert any other columns you want before that, you can just do [@MS]:[@A N Other]
|
|
#
?
Jan 2, 2021 17:17
|
|
|
Sad Panda posted:
code:code:TheLastManStanding fucked around with this message at 03:14 on Jan 3, 2021 |
|
#
?
Jan 3, 2021 00:44
|
|
|
TheLastManStanding posted:Assuming you aren't going to insert a column between Average and MS: Thank you to yourself and the other two solutions. This is the best one given MS to A N Other are names that will be different on each iteration. I tried that and it returns #VALUE! edit: Looking at the logic. INDEX(MainTable, ROW()-ROW(MainTable)+1, COLUMNS(MainTable)) Shouldn't that be ROW(MainTable)+1 - ROW()? As in get the first row of the main table, add 1 to it, and offset the current row to work out which row of the main table we are in? INDEX doesn't seem to like having the array of ROW(MainTable) passed into it and won't just take the first value from the returned array. edit: ulmont posted:You could just put in N5:XFD5 and call it a day. Strangely tempting. Sad Panda fucked around with this message at 15:32 on Jan 3, 2021 |
|
#
?
Jan 3, 2021 15:13
|
|
|
Sad Panda posted:I tried that and it returns #VALUE! If it isn't liking ROW(MainTable), then MainTable probably isn't actually the name of your table. Also, it will only work inside the table itself. The index needs the row number of the table, so we're taking the current row number of the sheet and subtracting the row number of the first row of the table, then adding 1. Doing it the way you suggested would give an increasingly negative number. You could alternately write it as ROW() - ROW(MainTable[#Headers]).
|
|
#
?
Jan 3, 2021 20:23
|
|
|
TheLastManStanding posted:If it isn't liking ROW(MainTable), then MainTable probably isn't actually the name of your table. Also, it will only work inside the table itself.  Excel is telling me it is called MainTable seems to be and highlighting the table appropriately. When I hit enter, it tells me #VALUE! 6.5 in the cell below is just because that uses =AVERAGE(N6:AZ6)
|
|
#
?
Jan 3, 2021 21:00
|
|
|
Not sure why it's not working. Everything looks right. I've sent you a PM. I've also come up with another solution using OFFSET. code:
|
|
#
?
Jan 3, 2021 23:18
|
|
|
Harminoff posted:Should be able to import with power query and than refresh that to get updated data. No need to copy/paste I never use power query so I spent like an hour today overthinking how to do this without breaking anything and now that I have it figured out I feel dumb as hell. There's... a lot of things I should have been using power query for. 
|
|
#
?
Jan 4, 2021 21:52
|
|
|
Here's a pretty dumb question. I have a spreadsheet where I need to count all instances of specific text appearing different columns and rows. For example, the cells that I want to look in are C5:C49, D5:D49...N5:N49. Let's say the text I'm looking for is "Value". Is there an easy way to use the COUNTIF or COUNTIFS function to search all those cells for "Value" and poop out the total number of times it appears? On a related note, is there a way to put that word "Value" in a cell and use the formula and point it at that cell and have Excel recognize that's what I'm looking for in the function? I'm not very good at Excel, so I apologize if these are either super basic or really dumb questions. Edit: You know, I think I figured the above out. It was a lot easier than I thought it was when I just played around with the function a little bit. Lucas Archer fucked around with this message at 20:27 on Jan 15, 2021 |
|
#
?
Jan 15, 2021 20:16
|
|
|
If the value you're looking for will only appear once in a cell or you only want to count the cell once then you can use =countif(range,"*value*"). If the value is going to appear more than once then you would want to use something like =(LEN(text)-LEN(SUBSTITUTE(text,word,"")))/LEN(word). Note that substitute is case sensitive. This formula only works on a single cell at a time so you would need to sum over the results.
|
|
#
?
Jan 15, 2021 22:34
|
|
|
Loiku posted:If the value you're looking for will only appear once in a cell or you only want to count the cell once then you can use =countif(range,"*value*"). If the value is going to appear more than once then you would want to use something like Forgive me if I respond in a dumb way. The first thing you mentioned is correct - the value might appear multiple times, but only at maximum once per cell. So I used the =COUNTIF(Range,"Value") and that seemed to do the job - thanks for your help there. I didn't use asterisks though - will that screw something up? The LEN formula confuses me though, since I don't know how that would do any counting? Excel tells me it's for returning the number of characters in a text string - is that the same thing? I'll go digging into more excel tutorials to see if I can learn anymore. I'm essentially learning on the job here, my boss sent me a spreadsheet and said "figure out how to get excel to count these things quickly so we don't waste time manually counting".
|
|
#
?
Jan 15, 2021 22:56
|
|
|
Effectively what it's doing is counting the number of characters total then subtracting the number of characters not associated with your value which gives you the number of characters associated with your value. Then it divides that by the number of characters in your value to give you the number of times your value appears. So if you took a string like "bye hi hi bye" and used =(LEN("bye hi hi bye")-LEN(SUBSTITUTE("bye hi hi bye","hi","")))/LEN("hi") it would calculate to (13-9)/2 meaning it was 13 characters long, 9 of which were not associated with your word. So your word took up 4 characters. Then it divides by the length of your word which was 2 meaning your word "hi" appeared twice. Substitute in this case is reducing the total length of your input string by however many characters your target value is multiplied by the number of times it is present by substituting any instances it finds of "hi" with "" (nothing). I'm not sure if I made that any clearer but let me know if that makes sense. You could also make this not case sensitive by using UPPER or LOWER on your string and your search term. So the substitute portion would become: SUBSTITUTE(UPPER("bye hi hi bye"),UPPER("hi"),"") The rest would stay the same as LEN() won't care about case.
|
|
#
?
Jan 15, 2021 23:21
|
|
|
Lucas Archer posted:Forgive me if I respond in a dumb way. The first thing you mentioned is correct - the value might appear multiple times, but only at maximum once per cell. So I used the =COUNTIF(Range,"Value") and that seemed to do the job - thanks for your help there. I didn't use asterisks though - will that screw something up? The asterisks in “*value*” just means replace the *value* with what you wanted to look for. Quotation can be quite important in excel functions so it’s important to make sure you don’t exclude them in a “Enter [blank] without the quotes” way. Chances are you actually do need the quotes.
|
|
#
?
Jan 16, 2021 20:02
|
|
|
Kibayasu posted:The asterisks in “*value*” just means replace the *value* with what you wanted to look for. Quotation can be quite important in excel functions so it’s important to make sure you don’t exclude them in a “Enter [blank] without the quotes” way. Chances are you actually do need the quotes. I should have been more clear on that, I was using the asterisks there as a wild card since countif allows those and I wasn't sure where in the cell his value would be. So in my previous example if you wanted to use countif to count a cell containing "bye hi hi bye" you would use =countif(<range>, "*hi*") where the range is the address of the cell (such as A1 or RC[-1] and you can have multiple cells since it's a range such as A:A for column A or C[-1] for the column left of the cell) but as I said before it would only count each cell once regardless of how many times the criteria appeared in that one cell. The * in that example allows any number of characters (including none) to be before or after 'hi'. If you ever wanted it to be exactly 'hi' you would just use =countif(<range>, "hi"). If you wanted a specific number of characters before your search term in the criteria, like say you wanted 4 characters before and any number of characters after your search term, you would use =countif(<range>, "????hi*") where ? represents a single wildcard character (can be useful for validating the formatting of strings). Countif is particularly useful because it allows wildcards and a lot of excel formulas don't.
|
|
#
?
Jan 17, 2021 06:21
|
|
|
Sort of related to this discussion (I think) - We have an email newsletter at my organization and we're trying to figure out who isn't opening it month after month so they can be taken out of distribution. What approach would I use if I had five or six columns of email addresses and I wanted to find out who is consistently in each/all of them? The number of records in each column would likely be in the 20-30k range.
|
|
#
?
Jan 21, 2021 11:43
|
|
|
If your sheet could handle it I'd paste them all into one column, create a copy of that, Remove Duplicates on the copy and for each row of the copy do a Countifs on the column which still had duplicates. It'd stretch spreadsheet capability, but with 6x20k records it shouldn't be completely unworkable.
|
|
#
?
Jan 21, 2021 13:46
|
|
|
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
|
|
|
Thank you both - I haven't yet had a chance to try these solutions. I grossly underestimated the amount of data per file. It's more like 45k to 55k records across six files. I considered today that our email platform provider (Salesforce) may be able to assist us with this from their back end of things. Before I try going forward with the unwieldy process of managing so much data in Excel, I am going to see what they come back with. I opened a case with their support team earlier.
|
|
#
?
Jan 21, 2021 22:59
|
|
|
me your dad posted:Thank you both - I haven't yet had a chance to try these solutions. I grossly underestimated the amount of data per file. It's more like 45k to 55k records across six files. I considered today that our email platform provider (Salesforce) may be able to assist us with this from their back end of things. Before I try going forward with the unwieldy process of managing so much data in Excel, I am going to see what they come back with. I opened a case with their support team earlier. I know this is the Excel thread, but have you considered/are you familiar with using SQL? Having both SQL and Excel familiarity this is something I might try and figure out with PowerQuery, even. The Excel gurus here probably have a better idea how PowerQuery could pull it off, but if you wanted to approach it from SQL side, I'd probably create a table from each file and join each table on mailer1.emailaddy=mailer2.emailaddy and then count either all of the null values or all of the non-null values for each returned row.
|
|
#
?
Jan 21, 2021 23:06
|
|
|
Lib and let die posted:if you wanted to approach it from SQL side, I'd probably create a table from each file and join each table on mailer1.emailaddy=mailer2.emailaddy and then count either all of the null values or all of the non-null values for each returned row. Wouldn't it be better to create a single table with emailaddy and datereceived columns, and then just do code:
|
|
#
?
Jan 22, 2021 12:09
|
|
|
Whybird posted:Wouldn't it be better to create a single table with emailaddy and datereceived columns, and then just do Probably, but there's nothing I enjoy more than over engineering a solution.
|
|
#
?
Jan 22, 2021 12:53
|
|
|
edit: nvm I figured out how to array
Turkeybone fucked around with this message at 17:55 on Jan 28, 2021 |
|
#
?
Jan 28, 2021 16:43
|
|
|
I was trying to use a VLOOKUP on a PivotTable before. The PivotTable has a name. When I put the PivotTable name in to the VLOOKUP I get #name. If I select the pivot table instead then it works just fine. Is there something that stops using the name of a pivot table in a VLOOKUP? eg =VLOOKUP("M", PivotTable1, 2) gives me #NAME Whereas =VLOOKUP("M", A3:B6, 2) gives me 10.86.
|
|
#
?
Feb 9, 2021 15:22
|
|
|
I'm not sure what you're trying to achieve, but I think the GETPIVOTDATA function might be more applicable.
|
|
#
?
Feb 9, 2021 15:29
|
|
|
nielsm posted:I'm not sure what you're trying to achieve, but I think the GETPIVOTDATA function might be more applicable. Basically just trying to do a regular VLOOKUP but on a PivotTable rather than on a regular table/series of data. Never tried GetPivotData! I'll give that a look, just seems strange that you can't give a PivotTable name when you can give a regular table/named range to VLOOKUP.
|
|
#
?
Feb 9, 2021 15:49
|
|
|
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 posted: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. Glad to hear that it doesn't seem to be possible and it's not just me. I've had my Excel do funny things before that made no sense so it's good to have it clarified. It seems like PivotTable names and Named Ranges exist in two different places so you can have a PivotTable and a named range with the exact same name if you want to be confused. The baffling thing is that GetPivotData also doesn't seem to use the Pivot Table name unless I'm mistaken by that. I was following https://support.microsoft.com/en-us/office/getpivotdata-function-8c083b99-a922-4ca0-af5e-3af55960761f and ended up with =GETPIVOTDATA("Price Paid", $A$3 ,"Name of movie",J36) This gets me the 'Price Paid' for the 'Name of movie' found in J36. I have to use $A$3 as a random cell in the pivot table. When do I actually use the name of the Pivot? I'm new to them and the only thing I've used it for so far is selecting which PivotTable + PivotCharts get impacted by a slicer.
|
|
#
?
Feb 9, 2021 19:32
|
|
|
I'm not sure pivot table names come up anywhere but possibly in macros. Pivot tables are a much older feature in Excel than data tables after all, and the design philosophy was different at that time.
|
|
#
?
Feb 10, 2021 00:02
|
|
|
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. EDIT- I guess I could start by adding a PAID? column and counting the yes and nos, that's a start, though to my shitbrain, that column kind of uglies up the whole mawfk. Narzack fucked around with this message at 02:30 on Feb 12, 2021 |
|
#
?
Feb 12, 2021 02:14
|
|
|
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. 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
|
|
|
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?
|
|
#
?
Feb 13, 2021 03:32
|
|
|
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
|
|
|
Well, I
|
|
#
?
Feb 13, 2021 19:56
|
|
|
have ... have you just not tried a pivot table? it exists to make these types of reports easy
|
|
#
?
Feb 13, 2021 20:41
|
|
|
Narzack posted:Well, I 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
|
|
|
Wiggly Wayne DDS posted:have ... have you just not tried a pivot table? it exists to make these types of reports easy I don't know what that is
|
|
#
?
Feb 13, 2021 22:22
|
|
|
|
| # ? May 11, 2024 05:33 |
|
|
DRINK ME posted:Yeah sorry I should have explained it better, normally you’d have it all as a single table. Okay, I'll give that a go, thank you! EDIT: Well, wait, if I do that, won't I have to reset the range anytime I go beyond that? EDIT2: Is there a way to just exclude the Table4 line 1? Narzack fucked around with this message at 22:48 on Feb 13, 2021 |
|
#
?
Feb 13, 2021 22:23
|
|