I don't have the current days start time on the same line and I'm not
|
|
# ? Nov 30, 2014 18:40 |
|
|
# ? May 26, 2024 19:36 |
|
Is it possible to have frozen rows that "stack up" as you scroll down a sheet? i.e. I want to freeze rows 1, 100 and 200 so that if I'm scrolling from row 1 downwards, only the first row is frozen but once I go past row 100, it's frozen underneath row 1 and once I go past row 200, I have 3 rows frozen at top.
|
# ? Dec 1, 2014 23:58 |
|
Ragingsheep posted:Is it possible to have frozen rows that "stack up" as you scroll down a sheet? Not really. See http://www.cpearson.com/excel/DetectScroll.htm You could create a class to handle selection change events, but that would involve changing the active cell by clicking/arrow keys, or page down.
|
# ? Dec 2, 2014 00:16 |
|
I'm trying to sum together some times in a pivot chart so I can track the number of hours given over to separate issues.code:
I can't for the life of me figure out how to get that into the pivot and display it properly. At the moment, I'm trying to use the two fields of Problem in the axis category, and Sum of Problem Time formatted as Time in the Values field, but the graph it creates loops around on the Y axis.
|
# ? Dec 2, 2014 15:39 |
|
Have you tried wrapping your concat functions in a format function? might help the pivot table. so: = format(concat()-concat(), "[hh]:mm") sorry, ignore the above, doesn't work. Is it showing like .020833 in the pivot? if so, right click the total time field in the pivot and select Value Field Settings, then Number Format. Select Custom, then add the [h]:mm. fosborb fucked around with this message at 18:41 on Dec 2, 2014 |
# ? Dec 2, 2014 18:30 |
|
For some reason it wouldn't work with [h]:mm but hh:mm:ss seems to have fixed it. Which is more annoying. Thanks for your help man
|
# ? Dec 3, 2014 10:08 |
|
In Win7 Excel 2013 works as always have been for decades. Now on Win8.1 it works bit differently and driving me nuts. I can't figure out where in bowels of both Excel and Windows settings to set. Windows 7 the decimal is a full stop as in 123.45 Win8.1 it is comma as in 123,45 The parameter list separator is comma as in =func(p1, p2, ....) Window 8.1 it's semicolon! As in =func(p1; p2; .....) So how do I set Excel on 8.1 to behave as before?
|
# ? Dec 21, 2014 07:24 |
|
Vaz posted:In Win7 Excel 2013 works as always have been for decades. Now on Win8.1 it works bit differently and driving me nuts. I can't figure out where in bowels of both Excel and Windows settings to set. Have you checked that your windows regional settings isn't set to some weird rear end location?
|
# ? Dec 22, 2014 00:08 |
|
A weird rear end location like parts of Canada or Europe or probably most of the world except the US? Those settings can be inherited from system settings or overridden inside Excel (in 2007 it's under "Advanced"->"Use system separators").
|
# ? Dec 22, 2014 01:58 |
|
This is for Google Sheets specifically but I figured the solution might be similar in Excel so I figured I'd ask. I have a sheet with two specific columns I'm trying work with. Column A is date, Column F is calories I want to get a "moving average". Cell F2 should be the average using the range of the most recent (using column A) calorie entry, and up to the previous 30 entries. There may be blanks in that range that need excluded. There may be cases where there are fewer than 30 entries. Uziel fucked around with this message at 23:10 on Dec 28, 2014 |
# ? Dec 28, 2014 21:47 |
|
Uziel posted:This is for Google Sheets specifically but I figured the solution might be similar in Excel so I figured I'd ask. You could put this below your column F data and reference the value in F2: =AVERAGE(F54:INDEX(F1:F54, LARGE(ROW(F1:F54)*(F1:F54<>""),30))) That way it'll adjust as you insert rows for new dates. You'll need to ctrl-shift-enter that formula and you'll have to get rid of the letters in column F and just have null values for skipped days. Edit: I just noticed you said there may be times when you want less than 30 days, how do you need that to work? Chernobyl Prize fucked around with this message at 00:07 on Dec 29, 2014 |
# ? Dec 29, 2014 00:00 |
|
Chernobyl Prize posted:You could put this below your column F data and reference the value in F2: =AVERAGE(F54:INDEX(F1:F54, LARGE(ROW(F1:F54)*(F1:F54<>""),30))) The solution is: =AVERAGEIFS(F3:F, A3:A,">"&TODAY()-30, F3:F, ">0") Uziel fucked around with this message at 02:02 on Dec 29, 2014 |
# ? Dec 29, 2014 01:17 |
|
I am attempting to throw together a code for my work that tracks sign in and sign out of jobs through a job traveler Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Dim rCheck As Range Dim r As Range Set rng = Range("E:E") Set rCheck = Intersect(Target, rng) If Not rCheck Is Nothing Then For Each r In rCheck If r.Value <> "" Then r.Offset(0, 1).Value = Date r.Offset(0, 2).Value = Time r.Offset(0, 3).Select End If Next r End If End Sub I've managed to get this up and running, however I also want to have the second initial record the time once they insert something into that box as well. Any help would be greatly appreciated
|
# ? Dec 29, 2014 21:59 |
|
Not sure if this is actually doable in Excel. I want to be able to tally and consolidate lists of redundant data. For example, if I dropped into a table:code:
code:
|
# ? Dec 30, 2014 02:55 |
|
HeyMrDeadMan posted:Not sure if this is actually doable in Excel. I want to be able to tally and consolidate lists of redundant data. For example, if I dropped into a table: Use a PivotTable
|
# ? Dec 30, 2014 03:02 |
|
fosborb posted:Use a PivotTable If it is actually that easy, I am going to be incredibly disappointed in myself. Edit: Mother fucker... It was actually that easy... HeyMrDeadMan fucked around with this message at 04:11 on Dec 30, 2014 |
# ? Dec 30, 2014 04:01 |
|
You could've also used SUMIF or SUMIFS in that case.
|
# ? Jan 22, 2015 19:21 |
|
How do you array formulas on mac?
|
# ? Jan 25, 2015 05:49 |
I have a piece of code to determine how many columns I need for another part of the code. I need to round up to the next highest integer. So if the count is 2.1, then I need to round it up to 3. Right now I have it doing this, but I was wondering if there is a better way to do it. Thanks! code:
Turmoil fucked around with this message at 15:14 on Jan 25, 2015 |
|
# ? Jan 25, 2015 14:16 |
|
Turmoil posted:I have a piece of code to determine how many columns I need for another part of the code. =ceiling(2.1,1) will return 3
|
# ? Jan 25, 2015 16:28 |
|
So I have a semi-advanced question. We've been maintaining an .xlsm workbook, and some of the tabs in the worksheet are "Very Hidden". That is, we set the tab properties in VBA to have visibility 2. Users can pull data from a "very hidden" sheet, and it's completely unencrypted, but you can't actually unhide the sheet and see it. There is a password on the VBA project (so you can't unhide via VBA), but the workbook is otherwise unprotected. The person who maintained the sheet left the company, and he was the only one who actually knew the VBA password. Is there a way to unhide these sheets and/or crack the VBA password to make the workbook useful again? I have a feeling it's possible since the contents of the hidden sheets are completely unencrypted.
|
# ? Feb 4, 2015 22:52 |
|
Unless I did it wrong just now, can't you just open a new workbook, and run code in the new workbook to unhide the sheet in your first workbook? It seems to work fine. Otherwise there's probably a way to break the password. I know that for Excel sheet passwords (not VBA projects) in .xlsx/.xlsm files, all you need to do is open up the OOXML that defines the sheet and remove a line that says that there is a password (I forget the details). It's probably more complicated to break project passwords (the VBA code and settings is all packaged in a binary file iirc).
|
# ? Feb 5, 2015 01:08 |
|
Make a backup of the workbook then rename the new file to a .zip extension. Open it up with 7-zip and edit the workbook.xml in the xl folder and/or the worksheets in the xl/worksheets folder depending on what you need to accomplish. Don't extract the folders or anything--Just dig through the zip and edit the individual files, saving them back to the archive when done. Rename it back to the original extension when finished, and if you get an error when you try to open it, just make a new back up and try again. In workbook.xml delete whatever hidden states you need removed, like:
You can delete sheet protections by going into that particular worksheet's xml in the worksheets folder. Look for a line that says something like:
This also works for other office stuff like Word docs. Just another reason not to put anything private into an excel file that you'll be sharing.
|
# ? Feb 5, 2015 08:44 |
|
Is there actually any way of protecting sensitive information in an excel workbook?
|
# ? Feb 5, 2015 09:02 |
|
Ragingsheep posted:Is there actually any way of protecting sensitive information in an excel workbook? Yes, keep the exel workbook behind the trust barrier. Otherwise I wouldn't rely on any of them to be more than a minor annoyance.
|
# ? Feb 5, 2015 09:15 |
|
ShimaTetsuo posted:Unless I did it wrong just now, can't you just open a new workbook, and run code in the new workbook to unhide the sheet in your first workbook? It seems to work fine. Yeah this worked. Thanks!
|
# ? Feb 5, 2015 14:04 |
|
Ragingsheep posted:Is there actually any way of protecting sensitive information in an excel workbook? No, but that doesn't stop people from trying
|
# ? Feb 5, 2015 23:05 |
|
Is there any way to post a single column from Excel into an existing table in OneNote as an additional column? Every time I try it ends up just nesting the column inside the other column.
|
# ? Feb 6, 2015 21:09 |
|
Is there a function to normalize a data set between 0 and 1, and if so what is it? Normally I just normalize by dividing everything in the set by the highest value therein, but I'm curious to see what it looks like if the lowest value is 0 as well.
|
# ? Feb 9, 2015 21:20 |
|
It's called a map. I don't think there is a function for it, but the math is simple enough.code:
|
# ? Feb 9, 2015 22:29 |
|
Oh duh, that seems kind of obvious now that I see it. Thanks!
|
# ? Feb 9, 2015 22:59 |
|
TheEffect posted:Is there any way to post a single column from Excel into an existing table in OneNote as an additional column? Every time I try it ends up just nesting the column inside the other column. What I ended up doing was exporting the table from OneNote to Excel, adding the column there, and importing it back into OneNote. pointsofdata posted:No, but that doesn't stop people from trying Really? I thought the new version was pretty much hack proof, aside from like brute force?
|
# ? Feb 11, 2015 15:57 |
|
TheEffect posted:What I ended up doing was exporting the table from OneNote to Excel, adding the column there, and importing it back into OneNote. Unless I'm badly mistaken you can only encrypt the whole workbook securely, not individual elements
|
# ? Feb 13, 2015 00:22 |
|
I've got the following pivot table going: Column A is people's level of English, Column B is the number of respondents at that level. The remaining columns are the number of people who read these different types of things. Is there a way to have columns C-H display as a percentage of column B?
|
# ? Feb 13, 2015 00:48 |
|
Pivot tables can be a bit wonky if you try to do too much inside them; I'd add a second table (a regular table) just to the right, have it do the math using table headers, then hide col C-H.
|
# ? Feb 13, 2015 01:01 |
|
greazeball posted:I've got the following pivot table going: the answer is yes but it depends on how your underlying data is structured. It looks like each column is it's own value, so you can create a calculated field for each column (it's up top on the pivot ribbon) that is [normal books read] / [number at level], etc.
|
# ? Feb 13, 2015 01:33 |
|
Thanks you guys! I'd like to try your method fosborb, but "number at level" is just a count of the row labels, so I don't seem to be able to use that. If I had that as a field in my data, then I could, right? I've still got a lot to learn but my wife will be around this weekend and she's much better at Excel so I'll get a tutorial from her. For now, TheLastManStanding's method is working though, thanks!
|
# ? Feb 13, 2015 11:22 |
|
no worries! Like I said it really depends on how your data is originally structured. If you're still interested in containing this within your pivot table alone (and you absolutely should be able to do that if you want) then just posting a single row from your data set would be enough. Otherwise, I hope you enjoy a very data driven Valentine's Day weekend with your wife!
|
# ? Feb 13, 2015 14:11 |
|
e: durr, nevermind. Realized how dumb I was being.
Papercut fucked around with this message at 21:41 on Feb 17, 2015 |
# ? Feb 17, 2015 21:03 |
|
|
# ? May 26, 2024 19:36 |
|
I have two Excel documents that I need to combine. One has multiple sheets and 3 columns in each. The second one has the same three columns, one sheet only. I need something that will look at the first column in document B and search for a match in document A, and when it finds a match it will copy the next two columns associated with that row to the other workbook. I imagine there's not a simple elegant way to do this is there? Edit- something that doesn't cost 50 bucks- https://www.ablebits.com/excel-lookup-tables/ Although goddamn does that work marvelously. I still need something for when the free trial expires though. If there's not a simple solution I might just bite the bullet and buy it because the thing is pretty drat great honestly. TheEffect fucked around with this message at 17:11 on Feb 19, 2015 |
# ? Feb 19, 2015 16:34 |