|
Kind of have an interesting issue. I'm working on making a Dashboard and want it to look nice. I'm trying to set the background cell color to blue, however when the pivot tables resize it resets the cell colors to white. Is there any way I can keep them blue? Or, is there a way that I can force the height of a pivot table? Adding a scrollbar would be fine.
|
#
?
Nov 22, 2019 18:43
|
|
|
|
| # ? May 24, 2024 15:13 |
|
|
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
|
|
|
hmm trying that seems to change the pivot table themes and colors, but just the worksheet colors will still change if the pivot table is resized. From say 15 rows to 10 rows, the 5 rows will now be white instead of blue. Edit: Found kind of a dumb workaround. I took a screen snip of some of the blue, and then just loaded that as a background image. Seems to work. Harminoff fucked around with this message at 20:10 on Nov 22, 2019 |
|
#
?
Nov 22, 2019 20:03
|
|
|
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
|
|
|
I've created a spreadsheet for my job to advise people on planning for when to file for their Social Security benefits, that takes their DOB, their chosen month to retire, and their PIA (Primary Insurance Amount- basically SSA's base calculation for "what you're gonna get", which gets adjusted for age), and gives them what their benefit would be. It also has a table that shows what their benefit would be for each month from filing at their first eligibility at age 62 all the way to the maximum benefit at age 70. So essentially, it displays (9x12) 108 incremental amounts. I'd like for the whole office to use it, since we don't have a nice presentable handout to give folks with this information (partly by design- senior management wants us to push everyone to online services). Since using macro-enabled worksheets give security popups and I don't trust their computer skills enough not to panic, I'm trying to do it all with formulas instead of macros. So that means a bunch of nested IF statements. It doesn't help that they have a bunch of hosed up technicalities that affect your calculation; for example, SSA considers you to have attained your age the day before your birthday. So if I'm born on April 1st, 2000, I'm actually going to turn 20 in SSA's eyes on March 31 of next year. This is important because you can file for retirement benefits the first month that you are 62 in the entire month. So b-day on April 1 or 2, 2000? You can retire April 2062. Born April 3, 2000? You get to wait until May 2062 for your eligibility to start. Another wrinkle is your full retirement age (the one they're stepping out gradually from 66 now to 67 in a few years). Your full retirement age is your birth month, so in the above example, all 3 of those options would be their full retirement age in April 2067. Except, oh wait. You attain your age the day before your birthday. So a birthday April 1, 2000 would mean you reach your full retirement age in March 2067. So you have a bunch of different technicalities that change the calculations, which are all actually pretty simple. But to cover all the possibilities, I ended up with 4 different possible calculations (own benefit born on the 1st/not born on the 1st, and spousal born on 1st/not on 1st). Each of those has the full table of 21 different customer classes with 108 months of calculations. So I'm getting drat near 10,000 dependent calculations every time a new DOB is entered, and while my test spreadsheet seems to run fine and stable, it's not very tidy on the calculation end. Plus the actual display sheet has in those 108 months a nightmare of nested IFs (If customer type A, this cell, if customer type B, this cell, etc all the way to customer type U. So I'd like to take another pass at this. Is there a real worry of things getting twitchy or slow with these many numbers running? Or is this essentially irrelevant?
|
|
#
?
Dec 1, 2019 07:11
|
|
|
It seems like you should just be able to do all the calculations once rather than calculating a zillion permutations each time (why can't you just calculate the retirement age at the start rather than calculating tables based on whether someone's birthday is or isn't the first?), but maybe I'm off base since I'm not looking at the spreadsheet. If it works fine I'm not sure really matters anyway aside from being unmaintainable? You're only performing the calculations once so even if there was a one second delay after you entered the birthday it doesn't seem like it would cause much if a problem.
mystes fucked around with this message at 14:21 on Dec 1, 2019 |
|
#
?
Dec 1, 2019 14:01
|
|
|
The things that vary between the 21 different customer classes are: 1. full retirement age (FRA- stepped out from 65 to 67 in 2 month increments) 2. delayed retirement credits (like someone born in x year gets 7/12ths of a % additional credit each month they wait over FRA, but someone born the next year gets 15/24ths of a %). I don't see a way of doing it without the customer classes, but I guess I could take all the age 62 to age 70 tables and just shrink them down to one table with a whole shitload of nested IFs. As it is, it makes all the calculations for the numbers given and then just takes the appropriate ones. This would have to be quicker, right? Like, the cell for retirement amount at age 64 and 6 months would be this calculation if customer class A, this calculation if customer class B, etc.
|
|
#
?
Dec 1, 2019 16:54
|
|
|
GD_American posted:The things that vary between the 21 different customer classes are: I don't know the exact details of the calculation, and you might need to have a whole table for the credits depending on how it works, but you still haven't described anything that suggests that you need to calculate the values for each month more than once in your spreadsheet. mystes fucked around with this message at 17:04 on Dec 1, 2019 |
|
#
?
Dec 1, 2019 16:57
|
|
|
Newer Excel versions have the SWITCH function, which can help tidy up nested IF statements.
|
|
#
?
Dec 1, 2019 17:23
|
|
|
mystes posted:I don't know the exact details of the calculation, and you might need to have a whole table for the credits depending on how it works, but you still haven't described anything that suggests that you need to calculate the values for each month more than once in your spreadsheet. Yeah, that does seem to be the big change I need to make. Thanks for the help. Zorak of Michigan posted:Newer Excel versions have the SWITCH function, which can help tidy up nested IF statements. Hmmmm gonna look into this, thank you
|
|
#
?
Dec 1, 2019 18:15
|
|
|
Fun fact I discovered today when trying to get SWITCH and IFS to work today; we have Excel 2016 at work. You get IFS and SWITCH if you got Excel 2016 with Office 365. If you got just Office 2016 with a license, you don't get those and other functions! So I did take your advice and shrink it down to one table. I would have loved to killed the nested IFs, but I at least figured out a way separate all the IFs on a scratchpad sheet and concatenate them, making it easier to error-check before assembling Iffenstein's Monster. When or If I submit this to our completely worthless automations person (the one time when I asked her an Excel question, she emphatically told me she had an English degree and cut the conversation short), I can't wait to hear someone ask why I did it the way I did.
|
|
#
?
Dec 3, 2019 06:31
|
|
|
https://imgur.com/a/NpsXPsA As you can see by the above picture, I want to show the number of orders split by country (USA, Canada, and Japan) from January 2019 to December 2019 along with the rate of acceptance between January and December. The problem I'm having is that the % line is connected between the countries when I do not want that. Is there a way to fix this?
|
|
#
?
Dec 16, 2019 10:53
|
|
|
Zorak of Michigan posted:Newer Excel versions have the SWITCH function, which can help tidy up nested IF statements. Hot drat. I am unreasonably excited about this discovery.
|
|
#
?
Dec 16, 2019 15:30
|
|
|
Can you just format the line series and turn off line color, leaving only marker color enabled?
|
|
#
?
Dec 16, 2019 15:31
|
|
|
Is there any way to tell Excel NOT to change the formatting when doing a Replace All query? Here's what I'm trying to do. I have a bunch of genotypes, in a format that is not appropriate(e3/e3). I need to switch them to the appropriate format(3/3). If I do a Replace All action, EVEN IF I tell it to format the cells as "Text" and EVEN IF the entire row is already formatted as "Text", Excel will replace them with its numbering system behind dates, so 43938 or whatever the gently caress it is. Is there an easy way to resolve this? I haven't tried removing just the 'e' character because I actually am unsure how, maybe that is a better method than trying to Find & Replace?
|
|
#
?
Jan 2, 2020 18:58
|
|
|
Can you replace with an apostrophe in front, as in '3/3 ?
|
|
#
?
Jan 2, 2020 19:06
|
|
|
totalnewbie posted:Can you replace with an apostrophe in front, as in '3/3 ?  Someone literally told me to do this exact thing, and I forgot alllllllllll about it. Thanks!
|
|
#
?
Jan 2, 2020 19:29
|
|
|
Sheets questions ok? This is dumb and I am dumb. I need to parse column A, then show the results of IMPORTXML into concurrent columns. Basically, I need to insert the title (Column A) into the middle of the query string and iterate it downwards. In addition, I need to have column titles (thus the pathetic attempt at the IF statement). Here's the code I'm trying to make work, and here's a sample doc. Sheet 1 is the broke rear end one and sheet2 is an example of it working with a single title listed as part of the string. code:https://docs.google.com/spreadsheets/d/1isCkn3jf2KoSXJyn_PB9BmT9zfQGXgHBaoeefB2rbYE/edit?usp=sharing
|
|
#
?
Jan 4, 2020 21:29
|
|
|
I don't understand why you need an array formula. Can't you just put your formula in cell B2, point it at A2 and copy it down as many rows as you need? You can pass a custom sort order to the sort function using this notation {1;5;2;...} =IF(ISBLANK(A2),"",TRANSPOSE(SORT(IMPORTXML("http://www.omdbapi.com/?t=" & A2 & "&r=xml&apikey=29e7c8aa","//@plot|//@director|//@writer|//@genre|//@actors|//@rated|//@imdbID|//@country|//@year"),{5;8;2;4;7;1;6;3;9},true))) I added a sheet to that link with how it could work.
|
|
#
?
Jan 6, 2020 04:58
|
|
|
I don't know - I had looked at some sample code and it appeared to show that as the best way to nest the IF. The sample code is ideal, especially the SORT tip! Thanks very, very much. [EDIT] Closed edit access because someone deleted everything. Any further suggestions or ideas are welcome of course! Ninja.Bob posted:I don't understand why you need an array formula. Can't you just put your formula in cell B2, point it at A2 and copy it down as many rows as you need? pipebomb fucked around with this message at 22:09 on Jan 6, 2020 |
|
#
?
Jan 6, 2020 17:57
|
|
|
I'm working on a caseload tracker for a supervisor here at work. The idea is that there is a shared workbook containing 11 tabs/sheets... 1 sheet is the supervisor's summary sheet, and the other 10 are the individual team members' sheets. The supervisor enters each case onto the summary/supervisor sheet, assigns it a unique ID, and assigns it to a team member. That info then gets sent to their individual sheets, providing them with their caseload (this part I have covered). Then, the team member has information that they need to enter about the case (status, comments, dates, etc.). I need THAT information to populate back onto the supervisor's sheet, based on the assigned unique ID. Basically I'm needing excel to search across multiple tabs/sheets to return the value of a particular cell, based on the unique ID. After doing some research, it looks like I need to use Index/Match/Indirect to accomplish this, but I can't seem to get the formula right. I have it figured out for one individual sheet: =(INDEX(AR!Q:Q,(MATCH(A2,AR!A:A,0)))) A2 is the location of the ID # "AR" is the team member's sheet column Q is where the information I need returned is stored I'm just not sure how to re-write this with the Indirect function. Any help is GREATLY appreciated!
|
|
#
?
Jan 21, 2020 19:19
|
|
|
Scarf posted:I'm working on a caseload tracker for a supervisor here at work. The idea is that there is a shared workbook containing 11 tabs/sheets... 1 sheet is the supervisor's summary sheet, and the other 10 are the individual team members' sheets. it is going to be very difficult to do this without macros to iterate through each page, combine, and post the results to the next page. Even if you were to do it in a single equation per cell... think about what will need to happen when you get another employee on the team. Also, this workbook can only be checked out to one person at a time. Across 11 users you will be stepping on each other constantly. Do you have Access or a team SharePoint site? bespoke workflow management for single teams is like the only reason to keep those two products in the enterprise anymore. They can do what you want pretty much with wizards/templates alone, and will be far better at handling multiple, concurrent users.
|
|
#
?
Jan 21, 2020 19:46
|
|
|
The way to use INDIRECT for a dynamic sheet reference is =INDIRECT([the cell that contains the name of the sheet goes here] & "![the cells you want goes here]") Maybe I'm not understanding your question, though. BTW make sure you're using ctrl+shift enter to make it an array formula (should have { } ) in case that's the problem.
|
|
#
?
Jan 21, 2020 20:11
|
|
|
if you have access to an enterprise microsoft license you may also have access to Power Automate (formerly Flow) and Forms, and I'm fairly sure you could do all of that with those two things it might be trickier than setting up an access db & front end, but building new things in access in 2020 sounds like a bad time
|
|
#
?
Jan 21, 2020 20:22
|
|
|
kumba posted:if you have access to an enterprise microsoft license you may also have access to Power Automate (formerly Flow) and Forms, and I'm fairly sure you could do all of that with those two things oh yeah, access is definitely not good. best case iis probably SharePoint with something like Nintex or just out of the box workflows.
|
|
#
?
Jan 21, 2020 20:30
|
|
|
I have some euro amounts in the following format: 6,923€ 10,021€ etc. But I want to shorten it to 6,9k€ and 10,0k€. How do I go about this? I looked at the following site and used the following format #,##0,“k” but it's only showing me rounded up so 7k€ and 10k€ https://wmfexcel.com/2015/03/14/show-number-in-thousand-k-or-in-million-m-by-using-custom-format/
|
|
#
?
Jan 28, 2020 21:25
|
|
|
The closest you can get with straight formatting is ##.0,"k" which will give you 6.9k and 10.0k. If you really want 10k instead of 10.0k, you'll apparently have to use conditional formatting to give a different format when the decimal would be 0.
|
|
#
?
Jan 28, 2020 22:05
|
|
|
Busy Bee posted:I have some euro amounts in the following format: #,##0.0,k€ may work.
|
|
#
?
Jan 31, 2020 18:36
|
|
|
pipebomb posted:I don't know - I had looked at some sample code and it appeared to show that as the best way to nest the IF. The sample code is ideal, especially the SORT tip! Thanks very, very much. https://www.youtube.com/watch?v=HtUOELH_SdU
|
|
#
?
Feb 6, 2020 08:32
|
|
|
Does anyone know of a way to filter data by wildcard in power query? For example, I want to filter out Filter this out Filter that out I'd think you could do Filter*out To catch both, but it doesn't seem to work?
|
|
#
?
Feb 6, 2020 17:02
|
|
|
Harminoff posted:Does anyone know of a way to filter data by wildcard in power query? Could you check for 'starts with Filter' and 'ends with out' instead? Can't seem to find docs on power query wildcards but it doesn't look as easy as * or %.
|
|
#
?
Feb 7, 2020 04:38
|
|
|
Long: I have to create reports in word with tables in them that I link to excel. I link through the standard copy the table in excel, paste special -> link in word. These reports are 100+ pages of text and tables sometimes with many different people working on them, sometimes people outside of our company. Internally it's fine because it sits on the server, but if we need to email the document to one of our clients all of the links break. This is a problem, especially if our client needs to make edits to a draft of the document. Is there a way to reestablish the links without going paste -> special for every table if the excel document is used? TLDR; Is there a way to change the location on a drive where a word document looks for an excel document that it pulls linked tables from?
|
|
#
?
Feb 11, 2020 23:39
|
|
|
Do your clients need to edit the document directly? Or could you just send them a pdf copy to make notes on so that you can make the changes?
|
|
#
?
Feb 12, 2020 00:03
|
|
|
Karia posted:Do your clients need to edit the document directly? Or could you just send them a pdf copy to make notes on so that you can make the changes? That is the current system and for  reasons it's extremely less efficient than it would be if we could both edit it. There's two admin teams involved and every split hair results in a ton of formatting changes. And there's a lot of hairs to split. Their process of formatting is kind of bad but we don't have an efficient way to take it over when they add new stuff, change things, and it's their document. It'd save us a ton of time if we could give it to our admin team to format whenever major changes are made (which is frequently) while they can update the information in the tables without having to imbed 40 links every time. We have admins that do this and only this specifically, and are very good at it.I'm just wondering if there is some accessible property within the word file that tells a link the file name and directory it's pulling a link from, and a way to access and edit it.
|
|
#
?
Feb 12, 2020 01:10
|
|
|
Put it on an externally accessible SharePoint site.
|
|
#
?
Feb 12, 2020 10:30
|
|
|
Scarf posted:I'm working on a caseload tracker for a supervisor here at work. The idea is that there is a shared workbook containing 11 tabs/sheets... 1 sheet is the supervisor's summary sheet, and the other 10 are the individual team members' sheets. Revisiting this, and my needs have flip-flopped... I had previously been sending the information from the supervisor's sheet to each team member's sheet via a "Microsoft Query", and then with some visual basic after the query seemed to cause some corruption of the file. However, we work in a Microsoft Teams/Sharepoint environment since we have a need for team members to access the file simultaneously, and neither of those environments support VBA/macros because Microsoft is loving dumb. So as it stands now, I'm using the following formula to pull each "assignment" from the supervisor's sheet and create a new row with the information the team member's sheet, based on the name associated with the assignment (column B): {=IFERROR(INDEX(TestSup!A:A,SMALL(IF(TestSup!B:B = "Jane Doe",ROW(TestSup!A:A)-MIN(ROW(TestSup!A:A))+1),ROWS(TestSup!$B$4:B4))),"")} This is working fine, but I'm having to use this formula for each column, A thru L, and it's completely bogging down Excel. Is there a way that I can alter this formula to pull the range of cells A:L rather than one column at a time? I was hoping something like this would work: {=IFERROR(INDEX(TestSup!A:L,SMALL(IF(TestSup!B:B = "Areli Munoz",ROW(TestSup!A:L)-MIN(ROW(TestSup!A:L))+1),ROWS(TestSup!$B$4:B4))),"")} But no dice...
|
|
#
?
Mar 6, 2020 17:44
|
|
|
Fresh question (Sheets): I am importing a csv file using IMPORTDATA. Columns A-D are always the same. E through ZZZ (ZZZ being a wildcard as I don't know how long this poo poo will run - currently at BA) are dated. What I would like to do is code:
|
|
#
?
Mar 11, 2020 17:46
|
|
|
I'm having some trouble with getting conditional formatting. I am trying to format D column which is for evaluation closed date. I want the dates in this column to be highlighted orange when it is 30 days past the date displayed in column D Evaluations are due to us no later than 30 days after the closeout date shown in column d. I'm trying to get column d to automatically highlight any date that is 31 days past the date that is shown. I can't figure out how to formulate this within conditional formatting so I would greatly appreciate help. In this image I manually highlighted some of the fields that are 30 days past the date that is highlighted. Alternatively, I wonder if I could just sort by Oldest to Newest, highlight those dates that are 30 days or more past due, and then change it back. Might not even need conditional formatting. 
|
|
#
?
Mar 17, 2020 03:30
|
|
|
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
|
|
|
|
| # ? May 24, 2024 15:13 |
|
|
DRINK ME posted:Im not great with conditional formatting but this seems to work: That seems legit. How do you do format pasting?
|
|
#
?
Mar 17, 2020 16:18
|
|