|
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
|
|
|
|
| # ? May 24, 2024 21:15 |
|
|
Single click to do it once, double click to keep doing to every cell you touch until you hit Escape to stop it. Be very careful with the double click. You can put some weird poo poo all over your spreadsheet if you're careless.
|
|
#
?
Mar 23, 2020 05:50
|
|
|
Using school closure time to improve an Excel spreadsheet for marking. At the moment, there are 9 things that are being assessed. If they meet it, they are given a 1. If there is a 1, they get that associated comment added to their list of successes. This is way too long and leads to students not reading comments. I want to limit it to 3. I'm trying to get it so basically... 1) Look at a row for a student, 2) Finds which successes are ticked, 3) Pick 3 ticked ones randomly, 4) Output those as comment 1, comment 2 and comment 3. They should be unique. 5) If there are fewer than 3 successes, pad them from a pool of general comments. At the moment there is an absolute mountain of nested ifs. Ideally, this would be done using built in Excel functionality rather than VBA. It is going to be used by other teachers with 0 VBA skills and needs to be easy to adapt, eg if there are more or fewer than 9 things being assessed in the unit. Ideas?
|
|
#
?
Mar 26, 2020 10:25
|
|
|
To do it with standard formulas this is how I would approach it, the padded general comments may be duplicates though but this could be solved using a similar method to the task comments, i.e. ranking and picking top n.code:
|
|
#
?
Mar 28, 2020 07:16
|
|
|
Ninja.Bob posted:To do it with standard formulas this is how I would approach it, the padded general comments may be duplicates though but this could be solved using a similar method to the task comments, i.e. ranking and picking top n. Thank you so much. That is such a creative and interesting way of making it. Amazing explanation too which means I can write notes on how to adapt it if any of the numbers change at all.
|
|
#
?
Mar 28, 2020 09:51
|
|
|
Hello. I do not have a small question, but I guess I have a large problem that I can break into small questions. Thanks to COVID-19 I have a new project that involves basically a new application every 30 seconds. I came here because I have to figure out how to I guess use Queries to pull data from multiple workbooks stored in OneDrive/Sharepoint/Teams into a master log, format some cells into caps (=UPPER, I've figured some things out kinda), mark duplicates, and tally totals for daily entries, weekly entries, etc. Some of the requirements from above seems easy, like I can probably figure out how to count items in a column that aren't blank to come up with totals, but I've never messed around with pulling data from other workbooks. So let me be reasonable with my requests here: 1) Can a table be formatted so when a new row is added, some cells are already populated? Our master log will be sent to the state so fields like our county will always be the same for us, but still need to be included. If I do something like =A3 where A3 is our county name and pre-populate a few rows so the formula doesn't start looking for A4, A5, etc., will that work in the way I expect? I think I want to query a table instead of a whole sheet, since that would maybe make it easier to avoid null or blank fields. 2) When pulling data through a query, can that data be further manipulated by a formula in the destination? There are some columns that the state needs to be capitalized, and instead of relying on the dozen or so workers to always capitalize things on their own (and also avoid any punctuation, but I don't know of a way to excise those), I think it would be easier to either import the data to column B, hide that column and make column C "=UPPER(Bx)" I don't know how active this thread is but I'm trying to cast a wide net as I do some accelerated learning on the topics. If someone is reading this and holding their head while laughing at what a fool I am, what a simple neophyte I am showing myself to be with my overly complicated solutions, well, fair play but also please throw me a bone. I'm experimenting with things as the night progresses and will hopefully have something to show at my meeting tomorrow since my recent mastery of VLOOKUP has crowned me the Excel wiz of the office. Thanks for reading. e: I have since answered many of my own questions. Once it clicked it was pretty easy. Panicking is just part of my process. LawfulWaffle fucked around with this message at 02:46 on Apr 3, 2020 |
|
#
?
Apr 3, 2020 00:02
|
|
|
LawfulWaffle posted:1) Can a table be formatted so when a new row is added, some cells are already populated? 2) When making your query: Transform>Format>Uppercase.
|
|
#
?
Apr 3, 2020 03:38
|
|
|
TheLastManStanding posted:1) Cells in a table that are functions should auto populate when you add a new row. If you make the reference absolute (ie. press f4 to make the cell =$A$3) then you won't have to pre-populate the next few rows to prevent it from incrementing. These are good tips, I'm gonna try them out. The log I wound up making is, if I may toot my own horn, pretty great. Definitely pushing my boundaries with Excel but I learned a bunch, and it should be super easy for our end users to update their own logs and have the master log pull all that information.
|
|
#
?
Apr 3, 2020 15:53
|
|
|
Alright, I've been running into the same problem a few times with my new Query powers. The way I have it, the Query populates a sheet (Data) and another sheet (Master Log) pulls that information into a template we can submit. However, when I refresh the query/connection, the Master Log winds up eating a few rows and only posting one new line that just jumps to the bottom of the updated query (I know I'm describing this poorly). Like it'll go from code:code:
|
|
#
?
Apr 4, 2020 00:20
|
|
|
Triple posting, sorry, but I have something more manageable than the previous questions (I figured out how to use macros so I made an easy way to fix that previous error). I have a growing list of ZIP codes on applications, and I have a short, static list of approved ZIP codes. I want to compare the big list to the small list and pull out data from the big list when it finds a ZIP code that's not approved. I don't know a good way to do this, especially not in a way that will result in a small list instead of a long list with a bunch of negative results that need filtered out. I'm talking a handful of bad ZIPs out of 300, and that number grows by 100+ each day. These last few days I've probably made 40 Google searches starting with Excel but I can't seem to find a good answer to this problem.
|
|
#
?
Apr 6, 2020 19:35
|
|
|
LawfulWaffle posted:Triple posting, sorry, but I have something more manageable than the previous questions (I figured out how to use macros so I made an easy way to fix that previous error). Sounds like you can just the =countif() function for this. Then just filter for 0 in that column to pull out any rows with zipcodes it can't find.
|
|
#
?
Apr 6, 2020 19:54
|
|
|
LawfulWaffle posted:Triple posting, sorry, but I have something more manageable than the previous questions (I figured out how to use macros so I made an easy way to fix that previous error). How automated does this need to be? I've done similar things in a very manual method (but it'd be pretty easy to macro-automate.) Create two tables: one with the approved list of ZIP codes (this doesn't technically need to be a table, but it makes it easier to expand if you ever need to), the other with the list of all zips. The second tableshould have a second column which checks if the zip code is in the approved list (I usually do ISERROR(VLOOKUP)), making sure to specify exact match, but there's other ways to do it.) Then you can filter that table on the second column, select the first column, and copy/paste as value out of it. It'll only paste the values that are shown, so if you have it filter for TRUE, it'll only copy non-whitelisted values. EDIT: Beaten.
|
|
#
?
Apr 6, 2020 20:05
|
|
|
I can't figure out how to use a Countif() statement without having a table that's just as large as the master list, and since I don't know why my output table that draws from the query keeps needing its formulae pushed down manually after every refresh I want to minimize those sort of interactions. Making another table just as long that just looks at ZIPs and can be filtered would probably be the easiest way though even with the extra step. Once I stop getting new requirements I'd like to make a macro-enabled version to help automate the refresh and formatting. Anyway I wanted to say that I appreciated the help. I like learning new skills in Excel but this current project requires a lot of techniques that are unfamiliar to me. Now I get to worry about a way to make 500+ addresses compliant with USPS naming conventions where the real answer was build the USPS API into the application. There's just no way to break out these addresses and substitute the right info with 100% accuracy.
|
|
#
?
Apr 7, 2020 23:46
|
|
|
LawfulWaffle posted:I can't figure out how to use a Countif() statement without having a table that's just as large as the master list, and since I don't know why my output table that draws from the query keeps needing its formulae pushed down manually after every refresh I want to minimize those sort of interactions. Making another table just as long that just looks at ZIPs and can be filtered would probably be the easiest way though even with the extra step. Once I stop getting new requirements I'd like to make a macro-enabled version to help automate the refresh and formatting. I don't understand this part. All you need is an additional column in the master list to use VLOOKUP or COUNTIF, I don't see why you'd need an entirely seperate table just for this. Unless you really can't edit the master list itself. SQL queries should be able to do this by themselves, though. I've never worked with them in Excel, though, no idea how its implementation works. quote:Anyway I wanted to say that I appreciated the help. I like learning new skills in Excel but this current project requires a lot of techniques that are unfamiliar to me. Now I get to worry about a way to make 500+ addresses compliant with USPS naming conventions where the real answer was build the USPS API into the application. There's just no way to break out these addresses and substitute the right info with 100% accuracy. It really sounds to me like you're hitting the limits of what you should do with Excel. You probably can do everything you want, but there's a certain point where you should really look into an actual database rather than cludging it together with spreadsheets (which have had awkward database functions thrown in.) You're already running into the weird sorta stuff you get when you try to use a system outside of its original design intent: the features have been added, but they aren't as robust as a proper database would be. Even Access would probably be easier to work with, and give you a lot more power to customize the UI. It's still VBA, which is terrible, but at least you already know it! Anyway, it doesn't seem like accessing the USPS API from VBA would be too difficult. Here's an example of someone looking up tracking data. Shouldn't be too hard to extrapolate from there. https://www.reddit.com/r/excel/comments/bck92q/adding_usps_tracking_to_an_excel_sheet/ But if you're gonna stick with Excel, one tip: rather than just writing macros, write custom functions. Then you can just just add a column like "=VALIDATEADDRESS([@DirtyAddress])" in a column of your table and it'll calculate it for all your addresses. No need to call a macro.
|
|
#
?
Apr 8, 2020 01:18
|
|
|
Karia posted:I don't understand this part. All you need is an additional column in the master list to use VLOOKUP or COUNTIF, I don't see why you'd need an entirely seperate table just for this. Unless you really can't edit the master list itself. You're really hitting on some of my core issues here, which is that I know just enough about Excel to make myself appear to know a lot more about it than I do. I didn't know I could make custom functions, for example. Is there a way to format a table so that it resizes itself based on referenced data? If I'm using the variable for the column, =Table1[@[ColumnA]], is there a way to have Table2 insert columns as Table1 gets larger? Or will I have to resize Table2 manually?
|
|
#
?
Apr 8, 2020 18:23
|
|
|
You should be able to query the first table (look into power query) and then just use refresh all to update it.
|
|
#
?
Apr 8, 2020 19:32
|
|
|
Hey Excel thread. I'm not sure which function I need to use for this thing, and I know one of my students is definitely going to ask tomorrow. I have names in column A, and a bunch of sales figures in B:E. I'm wondering which function will give me the content of a cell in A for a given value in B:E. They'll be using a function to find the max in the table, but the question also has the students determine the value from column A that corresponds to the cell containing the max value, which can be anywhere from B3:E7. The instruction is to just look with your eyes or maybe use ctrl+f if you're feeling fancy, but I'm sure there's some lookup and reference function for this, I'm just not sure which one.
|
|
#
?
Apr 26, 2020 21:47
|
|
|
tuyop posted:Hey Excel thread. I'm not sure which function I need to use for this thing, and I know one of my students is definitely going to ask tomorrow. Easiest way would be to add another column: F3=MAX(B3:E3) Then your result cell would be: =INDEX(A3:A7,MATCH(MAX(F3:F7),F3:F7,0)) There are ways to this without the extra column, but they involve array formulas.
|
|
#
?
Apr 26, 2020 22:32
|
|
|
TheLastManStanding posted:Easiest way would be to add another column: Oh thanks! That’s a great idea.
|
|
#
?
Apr 27, 2020 23:35
|
|
|
While I’m here, does anyone have any websites they’d recommend for, uh, challenges? Someone on Lynda posted a “5 day excel challenge” course and I’ve really enjoyed it. Really helped me finally figure out the lookups.
|
|
#
?
Apr 29, 2020 00:12
|
|
|
I'm trying to play around with my Excel markbook and decide based on 3 values which of 6 categories that leads to. The brute for way seems to be an exceedingly long IF statement. Any other advice would be appreciated. A1, A2, A3 hold the values. They can be U, L1P, L1M, L2P, L2M, L2D. Overall result L2D if all 3 are L2D. L2M if all L2M+ or A1 + A2 are both L2P+ and A3 is L2D. L2P if all L2P+ L1M if all L1M+ or A1 + A2 are both L1P+ and A3 is L2P+ L1P if all L1P+ Otherwise a U.
|
|
#
?
Jun 18, 2020 21:52
|
|
|
Sad Panda posted:I'm trying to play around with my Excel markbook and decide based on 3 values which of 6 categories that leads to. The brute for way seems to be an exceedingly long IF statement. Any other advice would be appreciated. That's not that many cases. Just concatenate the inputs, build a lookup table, and use IFNA() to handle the default case.
|
|
#
?
Jun 19, 2020 06:07
|
|
|
TheLastManStanding posted:That's not that many cases. Just concatenate the inputs, build a lookup table, and use IFNA() to handle the default case. As in Concat(A1,A2,A3) and then have a lookup table of all possible concatenations? With 3 values and 6 possible options each isn't that 6^3 (216) different things to have in my lookup table? I guess I can drop it so anything that leads to a U becomes the default value, so then it's 5^3 (125) instead, but that seems rather large. Edit - I implemented this, and it works. Just feels like there's got to be something more efficient than a hardcoded table with 125 values. Sad Panda fucked around with this message at 10:57 on Jun 19, 2020 |
|
#
?
Jun 19, 2020 10:31
|
|
|
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  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 posted: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 It's a teacher markbook. The point value of getting an L2D instead of an L1P might change (that's in a separate table) but the combination of what gets what won't. The long lookup table it is. Even though it's not as pretty as I was hoping for, it's definitely better than that horribly tiered IF statement stuff which would give me a serious headache to put together properly.
|
|
#
?
Jun 19, 2020 19:15
|
|
|
That’s a cool solution but it sounds like the markbook is maybe needlessly complicated, no? How do other teachers in your subject area keep their marks?
|
|
#
?
Jun 22, 2020 04:16
|
|
|
Sad Panda posted:With 3 values and 6 possible options each isn't that 6^3 (216) different things to have in my lookup table? I guess I can drop it so anything that leads to a U becomes the default value, so then it's 5^3 (125) instead, but that seems rather large. I played around in excel for a bit and came up with the formula below. It assumes input values 1 to 6 (1 being U and 6 being L2D). It seems to work, but you'll probably want to check it against your table. code:
|
|
#
?
Jun 22, 2020 07:36
|
|
|
tuyop posted:That’s a cool solution but it sounds like the markbook is maybe needlessly complicated, no? How do other teachers in your subject area keep their marks? It's certainly not critical. I'm just trying to automate as much of the markbook as possible. TheLastManStanding posted:Yeah, I didn't fully parse that your + usage meant any value greater than (that whole L1PL1ML2P system has terrible readability), so I thought you were only dealing with 8 cases; though 125 is still not that many cases. Wow. Even putting it into pseudocode my head hurts trying to parse how that even works, but it seems to from some quick testing. Thank you so much for that. One thing that jumped out, I'm not sure what *1 does? When does x *1 not = x?
|
|
#
?
Jun 22, 2020 16:07
|
|
|
Sad Panda posted:One thing that jumped out, I'm not sure what *1 does? When does x *1 not = x? One important thing to note; based on what you had wrote ("A1 + A2 are both L2P+ and A3 is L2D" ), I took that to mean that A3 is treated differently. So for example: 2, 2, 4 = 3 whereas 4, 2, 2 = 2 If that's not the case and order isn't dependent, then it simplifies to code:I also realized the original code can be simplified code:TheLastManStanding fucked around with this message at 20:50 on Jun 22, 2020 |
|
#
?
Jun 22, 2020 20:05
|
|
|
This strikes me as one of those times when the kludgey solution is best. Sure, the lookup table lacks elegance, but anyone with journeyman Excel skill could see how it works. TheLastManStanding's solution is very clever but in two years, it's going to be a Vancian magic incantation.
|
|
#
?
Jun 22, 2020 20:11
|
|
|
How can I use conditional formatting to highlight these cells red if the date in the column is 31+ days in the future? I want these cells to be highlighted in red if the date is 30 days late. https://i.imgur.com/lDnlYdf.png
|
|
#
?
Jun 23, 2020 15:24
|
|
|
anon from 4chan posted:How can I use conditional formatting to highlight these cells red if the date in the column is 31+ days in the future? I want these cells to be highlighted in red if the date is 30 days late. You could use the TODAY() function in the Conditional Formatting rule itself or have the Conditional Formatting look at a cell with a date. TODAY simply looks at your system date. To use it in the Conditional Formatting itself: If your range of dates is in A2 to A31, highlight that range, Conditional Formatting, New Rule, Use a Formula, and then enter: =A2>TODAY()+30. If you want you can also check it against a date in another cell rather than using the TODAY function in the conditional formatting. So if in B1 you had =TODAY() you would highlight the range again and enter: =A2>B1+30 as the formatting rule. The formatting formula will update relative to each cell as long as you select the entire range you want the formatting to apply to. However using the TODAY() function would mean the cells would stop being highlighted as soon as the system date moves to within 30 days of the date. If I used that in a sheet today it would highlight dates July 24 and later. But if I opened the same sheet tomorrow, or changed my system date and reopened the sheet, it would be highlighting July 25 and later. Is that what you want to happen? Or do you want to check against a specific date rather than whatever the current date is?
|
|
#
?
Jun 23, 2020 17:11
|
|
|
Kibayasu posted:You could use the TODAY() function in the Conditional Formatting rule itself or have the Conditional Formatting look at a cell with a date. TODAY simply looks at your system date. The evaluations are due to us 30 days after the dates I provided in that close out column. What I'm trying to do is highlight cells that are the date plus 30 days. So a cell that is 31 Mar 20 plus 30 days would be red, but say a date of 30 Jun 20 plus 30 wouldn't be red. Does that make sense?
|
|
#
?
Jun 23, 2020 17:58
|
|
|
Zorak of Michigan posted:This strikes me as one of those times when the kludgey solution is best. Sure, the lookup table lacks elegance, but anyone with journeyman Excel skill could see how it works. TheLastManStanding's solution is very clever but in two years, it's going to be a Vancian magic incantation. I think the thing that breaks my brain about it is the MOD just in the middle there. Doing important but mysterious work. And yet, it goes away when we just skip to multiplying by whether or not the result is even. Which is not only possible but matters somehow.
|
|
#
?
Jun 23, 2020 22:41
|
|
|
anon from 4chan posted:The evaluations are due to us 30 days after the dates I provided in that close out column. What I'm trying to do is highlight cells that are the date plus 30 days. In that case, if I'm understanding you, you just need to do the opposite in the formula. Instead of =A2>TODAY()+30 use =A2<TODAY()-30 for the formatting rule. Substitute for A2 whatever the first cell for your table is. That will highlight any dates which are 31 days or more in the past from your system date. If I had a list of dates going from May 1, 2020 to today - June 23, 2020 for this post - that formula would highlight any date in the list before May 24, 2020. Kibayasu fucked around with this message at 23:57 on Jun 23, 2020 |
|
#
?
Jun 23, 2020 23:51
|
|
|
tuyop posted:I think the thing that breaks my brain about it is the MOD just in the middle there. Doing important but mysterious work. And yet, it goes away when we just skip to multiplying by whether or not the result is even. Which is not only possible but matters somehow.
|
|
#
?
Jun 24, 2020 05:06
|
|
|
How does one add functionality to a spreadsheet based on the contents of a cell? For example, I'd like to have a "support enabled? (y/n)" prompt and typing a "Y" into a specific cell would cause additional cells and formulas and functionality to appear. I know I could just have the additional functionality just be there, but I feel like being clever.
|
|
#
?
Jun 24, 2020 16:21
|
|
|
TheLastManStanding posted:(A3>MIN(A1,A2)+1) converts the cell to a boolean, so you multiply it by 1 to convert it back to a number. You're right. A3 is treated differently. Thank you again for the simplified code and also the explanation about using *1 to swap it from a Boolean. It makes sense.
|
|
#
?
Jun 24, 2020 16:39
|
|
|
Agrikk posted:How does one add functionality to a spreadsheet based on the contents of a cell? Depending on how complicated things are, you can just wrap functionality in =IF(A7<>"",your content here,""), or you could go outside my skillset and probably write macros to insert or unhide stuff when a user types the correct invocation, probably.
|
|
#
?
Jun 24, 2020 17:21
|
|
|
|
| # ? May 24, 2024 21:15 |
|
|
Maybe this is a bigger question and only tangentially related to Excel, but here goes: End users will be filling out forms that are created as Excel files. I'll have some control over these forms, but not a lot. Users will then upload those forms through our website. What I'm hoping to be able to do is validate those files against a "master" copy. I can't just hash the files, because users will be inputting data, but I want to be able to check that users haven't altered the framework/layout/general structure of the file so I can trust that I can find the data they've entered. I know we can protect ranges on the forms to try to prevent those edits; is there a way to detect those edits afterward? (It's cool if it requires something like a Java library.)
|
|
#
?
Jul 2, 2020 16:53
|
|
