|
That's by design. INDIRECT() throws a reference error if the file isn't open. See this.
|
#
?
Jun 1, 2016 21:04
|
|
|
|
| # ? Apr 26, 2024 14:17 |
|
|
Richard Noggin posted:That's by design. INDIRECT() throws a reference error if the file isn't open. See this. Oh man, thank you, I didn't even notice that.
|
|
#
?
Jun 1, 2016 21:18
|
|
|
In Excel, I sometimes see formulas written by others beginning with "=+...." does the "+" immediately after the "=" actually do anything?
|
|
#
?
Jun 2, 2016 05:51
|
|
|
Ragingsheep posted:In Excel, I sometimes see formulas written by others beginning with "=+...." does the "+" immediately after the "=" actually do anything? Not really, no. What happens is that people start off their formulas with "+", and then excel evaluates the formula and adds a "=" behind it. I am one of those people that start formulas with + just because I am an accountant and my hand is always on the 10key so I start formulas with a + because = is too far away
|
|
#
?
Jun 2, 2016 22:05
|
|
|
cyxx posted:Not really, no. What happens is that people start off their formulas with "+", and then excel evaluates the formula and adds a "=" behind it. What does an accountant do all day?
|
|
#
?
Jun 3, 2016 07:45
|
|
|
Ragingsheep posted:In Excel, I sometimes see formulas written by others beginning with "=+...." does the "+" immediately after the "=" actually do anything? I believe it's a holdover from those who cut their teeth on Lotus Notes, which started formulas with +. Excel happily handles this.
|
|
#
?
Jun 3, 2016 13:46
|
|
|
tuyop posted:What does an accountant do all day? Not much, but occasionally my left hand is busy holding my head up so I don't fall asleep, and I need to punch in a bunch of formulas with my right hand.
|
|
#
?
Jun 3, 2016 15:47
|
|
|
Richard Noggin posted:I believe it's a holdover from those who cut their teeth on Lotus Notes, which started formulas with +. Excel happily handles this. I've never used Lotus Notes, but always start formulas with +. It's easy to find.
|
|
#
?
Jun 3, 2016 18:39
|
|
|
I know the equation I need, but I have no idea how to write it. Any help would be greatly appreciated! "IF the sum of cell W is greater than cell X or cell Y, fill the cell with the lower value between cell X & cell Y" ... I'm trying to make a fillable dnd character sheet document and poo poo like this is important. Pastry Mistakes fucked around with this message at 09:50 on Jun 6, 2016 |
|
#
?
Jun 6, 2016 09:45
|
|
|
Apeshit Sixfingers posted:I know the equation I need, but I have no idea how to write it. Any help would be greatly appreciated! What are you expecting in Sum of W? A column? A single cell? What is your "not greater than" condition? Otherwise what you're looking for is probably this - replace the letters with your cell expressions: =IF(SUM(W) > MAX(X,Y), MIN(X,Y), 'else')
|
|
#
?
Jun 6, 2016 11:57
|
|
|
Cell W was just another cell that had some math going on from other cells. I just want the total of that cell to interact with cells X & Y Thanks for your help though, I'm going to plug that in and see if it works! Pastry Mistakes fucked around with this message at 21:00 on Jun 6, 2016 |
|
#
?
Jun 6, 2016 19:28
|
|
|
excel kept thinking it was an incomplete formula with the variables I was giving it so I'm pretty sure I hosed up in my original post; I'll try again. There are four cells I'm dealing with right now: E7, H12, C53, C54 E7 is the cell I want this formula to occupy, which is in itself part of another SUM formula H12 is part of a larger set of cells that are read to spit out a specific sum given whatever numbers are in them (can be a positive or negative number), the result is tallied into the row that E7 inhabits. ~This stuff is pretty simple, and I can get that to work no problem. Here's where C53/54 come in though. H12 has to be capped based on the lowest value between C53 & C54. The numbers in these C cells will never be negative (just doesn't happen in this game), but H12 might be at times. If H12 is negative then it needs to show a negative number in the cell, but if H12 is greater that either C53/54 it needs to be capped. If H12 is lower than either of these numbers than it just needs to show H12 as-is. I hope it makes a bit more sense, if not I can try to clarify further.
|
|
#
?
Jun 6, 2016 21:01
|
|
|
Apeshit Sixfingers posted:excel kept thinking it was an incomplete formula with the variables I was giving it so I'm pretty sure I hosed up in my original post; I'll try again. I have some free time at work right now if you want to share the sheet on Google docs or something. I think I can get what you want, but it would help to be able to tinker with it.
|
|
#
?
Jun 6, 2016 21:06
|
|
|
PM sent!
|
|
#
?
Jun 6, 2016 21:15
|
|
|
Apeshit Sixfingers posted:PM sent! Curious to see if my formula worked for what you wanted.
|
|
#
?
Jun 6, 2016 21:31
|
|
|
It works perfectly! It's such a simple solution, I think I put way too much thought into it haha Thank you very much!
|
|
#
?
Jun 6, 2016 21:55
|
|
|
Apeshit Sixfingers posted:It works perfectly! It's such a simple solution, I think I put way too much thought into it haha  I felt like you might face palm with how simple it was. Glad I saw the sheet, you were definitely over thinking it!
|
|
#
?
Jun 6, 2016 21:58
|
|
|
I don't know if I'm up against a limit of Excel's capabilities for graphing, but I'm experiencing a rather annoying issue. So here's my basic graph/data:  I have 3 superorder categories (Block 1, Block 2, and Test) that each contain some shared variables. The problem is, I cannot adjust these bars such that the ones within a supercategory touch (ie. Not-repeated Prime, Not Repeated Target, and Repeated Target touch within Block 1), but a space remains between the categories (Block 1 bars do not touch Block 2 bars). For some reason if I increase the gap or decrease the series overlap, separates all of the bars, even the subcategories. As a hacky solution, I tried creating some dummy/blank rows to buffer the beginning/end of each supercategory:  However this solution too is a little sub-optimal, as the dummy bars take on the width of a normal bar, making spacing between the supercategories way too extreme. And if I just delete one of the 'buffer' bars, then I decrease the spacing, but make it asymmetrical. Is there a way to individually adjust the width of a bar to reduce the space between, say, the y axis and the blue bar? Or the grey bar in block 1 and the orange bar in Block 2? Or the buffer zone on either side of the 'New' bar in test? PoizenJam fucked around with this message at 20:45 on Jun 8, 2016 |
|
#
?
Jun 8, 2016 20:42
|
|
|
Limit of Excel. You can make it a bit better by angling the labels horizontally, but dummy rows is exactly how I've solved this in the past.
|
|
#
?
Jun 8, 2016 21:09
|
|
|
Well that sucks then- it's going to look like rear end on my poster presentation and even worse in a manuscript, so I guess I better get to fixing it after the fact in photoshop or learning how to graph in R or Plot, eh? Seriously, you'd think they'd have multiple sliders for spacing on multi tier bar graphs.
|
|
#
?
Jun 8, 2016 21:13
|
|
|
Is there a way to filter based on a dropdown? A1 is a drop down with "App1, App2, App3" as options. B:Y are the hours of the day for a shift A4:A6 are Call Centers B4:Y6 are the number of people on hand during that hour by call center Z lists out the applications that affect that Call Center B2:Y2 are the totals The desired outcome: Select an App in A1, filter only centers that are affected by that application Example: Selecting App1 only shows Center1 and Center2 Selecting App2 only shows Center2 Selecting App3 only shows Center3
|
|
#
?
Jun 24, 2016 22:38
|
|
|
(mock-up) Is there a way to automatically tally how many mondays each person has etc...?
|
|
#
?
Jul 4, 2016 08:59
|
|
|
double nine posted:(mock-up) Yup.  The formula in D2 is "=COUNTIFS($A:$A, D$1, $C:$C, $C2)" and you can drag that across columns and down rows to fill in the rest. Basically, you're using COUNTIFS to count only instances where the day of the week in Column A is the same as the day of the week in row 1, and where the name in Column C matches the name in that row. If you wanted to create a separate grid with the names of employees and days of the week, you can do that by modifying the last criterion ("$C2") so that reference points to the column where you've got the names listed.
|
|
#
?
Jul 4, 2016 09:26
|
|
|
Here is the set up: I have six computers networked and they all share files via SharePoint. We have one central Excel workbook that is basically a daily log of activities. I made a version of this book boiled down to the bare essentials, copied as links each area that was most pertinent (for formatting reasons) and had that set up to refresh links every 60 seconds. This workbook never closes. Then copied that as a link into a PowerPoint presentation. That presentation is displayed in our work area on a TV so that everyone can have a quick glance at what has happened and what work still needs to be done. Workbook > Workbook pseudo copy > PowerPoint For some reason, the laptop that hosts the pseudo copy runs out of HD space every couple of weeks. I can hunt down the offending file but there is nothing inside. Not even our top level IT guy sees anything, yet the file is 200+ GB. How do I prevent this product from creating phantom data? Edit: Once this POS laptop boots again, I will give the file path. Inzombiac fucked around with this message at 21:43 on Jul 4, 2016 |
|
#
?
Jul 4, 2016 21:32
|
|
|
You have a memory leak somewhere. My guess is in PowerPoint, every time you're refreshing your data connection. ?? https://support.microsoft.com/en-us/kb/2505667 There are likely several better ways to handle this, depending on your needs. Since you have a SharePoint site, one is to use Excel Services. This allows you to display a named range from an Excel workbook that is stored on ShraePoint as a web part on a SharePoint page. It runs server side, so you shouldn't have any issues with memory leaks. You could also probably rig up some auto refresh + calculations based on current time to get it to rotate the display of data between several views (I assume you're using PowerPoint because you're regularly flipping between a handful of slides). Another solution is to move away from using Excel as a productivity log and switch over to SharePoint lists. Then you have a truly IT-supported SQL Server solution, and you use SharePoint web parts to display those lists grouped and filtered as you will. You can add whatever columns (metadata) to the lists you want, and you can setup views to quickly see current work, future work, work to date this month, etc. Bonus, very easy to connect Excel and PowerPoint to SharePoint lists if you still need to run deeper analysis or send presentations up the chain. Both of these solutions allow you to say you've decommissioned a Business Owned Application, which should make your top level IT guy very happy.
|
|
#
?
Jul 4, 2016 22:31
|
|
|
That's a great idea, thank you. For background I work in government and have a pretty limited availability of programs and services available. Hell, I don't even have SharePoint Designer authorized. It's pretty bad. This compounded with me being made the SharePoint administrator without any previous experience really tell how professional my workplace is. The product that I made was the best I could do at the time. Edit: The support link you provided sure sounds like the issue I'm having and it's nice to have confirmation of my suspicions. Unfortunately we work in a very reactionary capacity so there is little need for a scheduled task list but I'm going to spend the rest of my time trying to make it work for our strange and specific needs. Inzombiac fucked around with this message at 00:02 on Jul 5, 2016 |
|
#
?
Jul 4, 2016 23:54
|
|
|
Wait, are you the admin of your SharePoint page, or the admin of the entire SharePoint farm? If the farm then lol do whatever the gently caress you want. If you aren't already aware, Excel Services should already be installed if you're using SharePoint 2010 or 2013. Can you open a workbook in a web interface through SharePoint? Then you should also have the Excel Web Access Web Part that can display a portion of a workbook integrated into a SharePoint page. It sounds like you put together a really cool solution, actually! Are you cycling pages with PowerPoint, or is it just a single slide that updates? I can say with experience that your cobbled-together MS tech to display Operational stats on a big screen sounds at least on par with the biggest names in insurance, aeronautics, finance, telecommunications, and web-based transactions. Depending on how you're using PowerPoint and how well you have designed your data visualization (not a high bar honestly) chances are your solution is actually already better than most of your private counterparts. I have also seen a proposal to set up pretty much the same thing you described, for only ~100 employees, with a 7 figure price tag. It wasn't actually accepted (it could be done better with the right tools and a single FTE for 1 year) but that they honestly thought that was an appropriate starting point really says something about the general state of operational reporting. fosborb fucked around with this message at 02:41 on Jul 5, 2016 |
|
#
?
Jul 5, 2016 02:37
|
|
|
That's some high praise for this guy who dropped out of college and is self-taught. Thank you. Yeah, I am the top SharePoint guy aside from one other person at HQ that doesn't have an office or phone number. I messed around and got a few web parts going to show a couple test books. Unfortunately, our official materials must be password protected and many have data validation. This seems to cause conflicts with SharePoint and forces the items to open in Excel and not in browser. The original product was a PP with two identical slides at 30 second display, no transitions and set up to update links when returning to slide one. This way a user had to wait a maximum of 60 seconds to see their changes. As everyone here knows 60 seconds is also the minimum time for Excel to update links automatically. I am pulling four discreet sections from four sheets across two books with up to 50 simultaneous users and 600 total users. They are all modified and Tetris-fitted to display the most useful information. It used to be three slides but a lot of users complained about having to wait a literal minute.
|
|
#
?
Jul 5, 2016 03:39
|
|
|
Is there a way to use VBA to either combine two identically named workbooks (or worksheets in two master workbooks; the "workbooks" in question are just one sheet each) into one workbook or to just automatically save every two worksheets in a single workbook as their own files? I''m compiling data from two workbooks, one of which has overall budget information and one of which lists transactions. I'm trying to create a workbook that lists the budget information and the individual transactions for each budget as its own file. I've got all the underlying slicing and dicing into different worksheets and/or different files, but the system I'm trying to replace combines the two into one master file for each budget and for some reason sending the data out as two workbooks instead of as two worksheets isn't considered feasible. Is the best bet pulling all of my data into one workbook initially then using a macro to match up the two sets of sheets by tab name (tabs are renamed as part of the macros I
|
|
#
?
Jul 18, 2016 02:35
|
|
|
FadingChord posted:Is there a way to use VBA to either combine two identically named workbooks (or worksheets in two master workbooks; the "workbooks" in question are just one sheet each) into one workbook or to just automatically save every two worksheets in a single workbook as their own files?
|
|
#
?
Jul 18, 2016 23:19
|
|
|
Problem: I want to make 1 sheet containing all the data from three sheets so I can make a pivot table from it but I cant make it a macro enabled workbook. Example: I have a big list of stuff to buy on 3 sheets: Sheet 1) Stuff we might wanna buy Sheet 2) Stuff we approved and are in process for Sheet 3) Stuff we bought Sheet 4) Full of pivot tables with various info. Thus I want to put all the data from Sheet 1-3 onto a new sheet so I can make a pivot table from it. -The "Pivot Table from Multiple Consolidation Ranges" doesn't work for the pivot table from 3 sheets because it always makes the categories into the values in the far left column which isnt relevant to what I am doing. -Consolidate doesnt seem to work because it doesn't simply copy the data, it requires performing some numerical operation on it like sum or count. -I cant just make references (I don't think) because the number of rows in each sheet is constantly changing. -VBA would do this easily but the document is on a shared OneDrive with a bunch of people having permissions to use it so no Macros. We use the pivot tables to track our budget and various other metrics. Thoughts? CarForumPoster fucked around with this message at 01:07 on Jul 23, 2016 |
|
#
?
Jul 23, 2016 01:05
|
|
|
CarForumPoster posted:Problem: I want to make 1 sheet containing all the data from three sheets so I can make a pivot table from it but I cant make it a macro enabled workbook. =IF(ROW()<=ROWS(Table1),INDEX(Table1,ROW(),COLUMN()),INDEX(Table2,ROW()-ROWS(Table1),COLUMN())) Obviously you would need to ensure that you have the formula copied to the cells in the maximum number of columns between the other sheets to a number of rows at least as much as the sum of the rows in the other sheets. Edit: There's also a Microsoft add-on called "power query" that might allow this to be done more cleanly in Excel 2010 or higher, but you would probably need everyone using the file to have it installed. Also, I should probably note that once you're trying to do stuff like this you would really be much better off using Access. mystes fucked around with this message at 02:31 on Jul 23, 2016 |
|
#
?
Jul 23, 2016 02:21
|
|
|
Does the new sheet have to be in the same workbook? If not, you could create a second workbook and use it to query the first.
|
|
#
?
Jul 25, 2016 21:17
|
|
|
Appreciate the replies. I started down the path of a series of if/then statements... ...you all will be annoyed with me but I ended up putting the data in one sheet, adding a new column in the original that says Purchase TBD | Awaiting Purchase | Will not Purchase | Purchased and told all the users to just use filters. Then just used a pivot table to count so that the summary page would display how many were in each.
|
|
#
?
Jul 25, 2016 23:38
|
|
|
e: nevermind, turns out I had some strange protection settings causing the issue
denzelcurrypower fucked around with this message at 19:53 on Aug 8, 2016 |
|
#
?
Aug 8, 2016 19:49
|
|
|
There's absolutely no way in VBA/the Excel object model to get at formatted text in cells whatsoever, right? It's really lame because if plain text is sufficient you can very easily get an array of strings with the values of an area of cells, but if you want to manipulate formatted text, apparently the only way is to copy the cell to the clipboard, access it as HTML, manipulate the HTML, copy it again as HTML format, and paste it back into the cell. Maybe I should just give up and use the open xml sdk, but I suspect that would open another can of worms.
|
|
#
?
Aug 13, 2016 17:11
|
|
|
cell.characters(start, length).font
|
|
#
?
Aug 13, 2016 18:19
|
|
|
fosborb posted:cell.characters(start, length).font Unfortunately, it looks like there's still no way to actually read/write an entire cell's worth of formatted text short of individually storing/setting the font settings for each character, but that would probably be pretty slow. Based on experimenting with font.bold it looks like it will return null for a character range that contains both bold and normal text, so theoretically you could probably do a binary search to find the places where styles change and get tolerable performance, but if I was going to put this much effort into it it would definitely be better to use another approach (manipulating the file directly rather than interfacing with Excel). I think for what I'm doing I'll just use the clipboard for now, which is kludgy but requires virtually no VBA code, and since I'm dealing with a fairly small amount of cells it's not too slow.
|
|
#
?
Aug 13, 2016 18:58
|
|
|
It actually ended up being worse than I thought. I quickly whipped up a thing using the clipboard to modify copied HTML in c# using Excel DNA and it was working great but then when I tried it again later it stopped working. It seemed like setting the clipboard from the Excel DNA plugin just randomly wouldn't work some of the time, so after wasting a bunch of time trying to debug it I had to switch the clipboard code to VBA (luckily there happens to be VB6 sample code for getting/setting html from the clipboard) and now it's working again, but it's even uglier than expected. It probably isn't going to be the most reliable thing in the world. I guess I might actually also be able to do what I want without directly modifying formatted text by 1) taking the value of a cell as a string, 2) allowing a user to modify this string, 3) diffing the modified value with the original, and 4) modifying the original, formatted cell directly based on the modifications to the string value in "3)", but that seems like that would be pretty awful too. Basically to actually do what I want to do the right way I would need to make my own excel viewer using the open xml sdk or somthing. mystes fucked around with this message at 17:42 on Aug 14, 2016 |
|
#
?
Aug 14, 2016 17:36
|
|
|
|
| # ? Apr 26, 2024 14:17 |
|
|
What's the best way to go from this:code:code:Transpose does something like this: code:
|
|
#
?
Aug 18, 2016 17:53
|
|
