|
diremonk posted:Thanks this works great, although I haven't tested it yet on the network share portion. Sorry if took a couple days to get back to you about it, had to take a couple days off. Just change the following line in ScarletBrother's code from this code:
code:
|
# ? Jul 24, 2012 15:55 |
|
|
# ? May 11, 2024 07:01 |
|
Never mind, figured it out!
miserable lil onion fucked around with this message at 17:35 on Jul 24, 2012 |
# ? Jul 24, 2012 16:46 |
|
I'm trying to set up a sheet in a workbook that will take a daily average of values in a table. So say the columns in the table look something like this:code:
With multiple values for both measurement 1 and 2 for each date in a month. However, the number of measurements taken per day varies. What I want to do is plug in a table that looks something like the above populated with data from an entire month, and then another sheet will return the average of each measurement on the first of the month, second, third, and so on until the end of the month. Is there any way to do this automatically or will I have to manually separate out the dates? I'm posting from my phone at work so sorry if it's unclear. Hopefully you can see what I'm looking for; if not I can rephrase the question once I'm back at my PC at home. EDIT: I'm trying to use the averageif function with the range being the date column and the criteria as "*/11/*" and the average range being the column measurement I want to average, but I'm getting a #div/0 error, and I can't figure out why. I suspect it has to do with how I'm using wild cards but honestly I'm a pretty poor programmer, so I don't know what exactly I'm doing wrong. Lawnie fucked around with this message at 21:19 on Jul 24, 2012 |
# ? Jul 24, 2012 21:02 |
|
A pivot table will do exactly this.
|
# ? Jul 24, 2012 22:18 |
|
G-Dub posted:A pivot table will do exactly this. That looks like it will work perfectly, and I might even be able to apply it to a couple other things at work. Thanks a bunch!
|
# ? Jul 24, 2012 23:06 |
|
G-Dub posted:A pivot table will do exactly this. Microsoft needs to sell a version of Excel named PivotTables Express or something and have it just be exactly the same program, just to get some more visibility on those awesome things in the general business world. I've seen so much time and effort wasted when people just simply weren't aware of the option.
|
# ? Jul 25, 2012 00:00 |
|
Old James posted:Just change the following line in ScarletBrother's code from this That did it, thank you both so much for the help. You'll have made the day of my entire department once I let everyone know.
|
# ? Jul 25, 2012 04:36 |
|
DukAmok posted:Microsoft needs to sell a version of Excel named PivotTables Express or something and have it just be exactly the same program, just to get some more visibility on those awesome things in the general business world. I've seen so much time and effort wasted when people just simply weren't aware of the option. I am rewriting a bunch of code right now where I have seen so many Excel functions replicated in the most inefficient fashion - the biggest culprits being SUMIF and COUNTIF. It gets even easier now with SUMIFS and COUNTIFS in 2k7 onwards. So many macros have been completely removed to leave basic workbooks, which takes them out of being classed as apps, and this makes me happy.
|
# ? Jul 25, 2012 07:22 |
|
I'm trying to compare attributes of parts that are used across a maximum of 4 projects. Is there a way to compare text strings across multiple non-consecutive cells, and only count the non-blank cells? Something likecode:
is what I'm trying to do.
|
# ? Jul 30, 2012 13:14 |
|
Since it's limited to 4 sets of data I just do it the brute force way and do all of the comparisons, defaulting to TRUE if either cell in a specific comparison is blank. Assuming your table below is from Cells A1:J4, then paste this into I2 (Desc Match for the first row), but on one line instead of 3 as below that I had to do to prevent table breaks code:
|
# ? Jul 30, 2012 15:50 |
|
Aredna posted:Since it's limited to 4 sets of data I just do it the brute force way and do all of the comparisons, defaulting to TRUE if either cell in a specific comparison is blank. I had to use "==" because spaces count as unique characters in my descriptions, but this works perfectly. Thanks!
|
# ? Jul 30, 2012 18:02 |
|
I have a calculator set up that takes 3 inputs and outputs a number in another cell. Is there any way to create a button or a macro that the user could press that would automatically export the output to another sheet, along with the date and time it is exported? I'm trying to track some data for a project and I need to make it as quick and easy as possible to do.
|
# ? Jul 31, 2012 16:45 |
|
Lawnie posted:I have a calculator set up that takes 3 inputs and outputs a number in another cell. Is there any way to create a button or a macro that the user could press that would automatically export the output to another sheet, along with the date and time it is exported? I'm trying to track some data for a project and I need to make it as quick and easy as possible to do. Yes, and there are examples of similar scenarios on the last page to get you started.
|
# ? Jul 31, 2012 18:52 |
|
e: fixed
DoleMIGHTY fucked around with this message at 17:56 on Aug 3, 2012 |
# ? Aug 3, 2012 16:19 |
|
DoleMIGHTY posted:*sigh* This is actually a quick fix. When setting the value of a variable to an object (such as a range or worksheet) you need to use the term "Set " before the variable name. So change code:
code:
|
# ? Aug 3, 2012 17:52 |
|
Hello thread. I have a little question that I hope someone with greater Excel nous than me can solve. I've got two excel workbooks, one called datasource.xlsx and the other called datapresentation.xlsx. In datasource.xlsx I have a range of cells (B11:B42) which contain data. I want to be able to link these two books so that the data from datasource.xlsx will also appear in datapresentation.xlsx. This sounds simple; something like =[datasource.xlsx]sheet1!whateverthecellrefis would do. Unfortunately, there are a couple of things I need to be able to do. 1. I need to be able to pull the data from datasource.xlsx even when datasource.xlsx is closed. 2. The links can't be absolute, because I will end up moving the two files around. The only guarantee I have is that they will both be in the same directory. So after a bit of a google I got indirect.ext working, but the problem I have with that is that if I want to reference a cell in a closed workbook I have to have a full directory reference i.e. C:\mydumbwork\data\datasource.whatever. Therefore I have two questions 1. Is what I'm asking for possible 2. If so, is it worth going to the trouble of trying to make it work instead of just dumping all the data from datasource.xlsx in to another worksheet in the datapresentation.xlsx workbook and linking internally? It won't look as well structured but I'm damned if I'm going to go for a super-complicated solution when an easy one can do the same. Thanks!
|
# ? Aug 5, 2012 13:18 |
|
I have thousands of pictures, not porn believe it or not..., I need to hotlink all these pictures separately into individual cells. So what I'm doing now is right clicking on the cell, choosing hotlink, and browsing to the photo. I've probably done 100... and figured there has to be a better way. I found this and am going through it now... http://forums.esri.com/Thread.asp?c=3&f=39&t=150522 But I know nothing of 'scripts' etc. Thanks. Anyone use hotpotato.avx ??
|
# ? Aug 13, 2012 16:02 |
|
FrankeeFrankFrank posted:I have thousands of pictures, not porn believe it or not..., I need to hotlink all these pictures separately into individual cells. So what I'm doing now is right clicking on the cell, choosing hotlink, and browsing to the photo. I've probably done 100... and figured there has to be a better way. Do you already have a list of all the file names? I've done something like this before, I used some VBA to import all of the full file directory names ("C:\Folder\File1.jpg") into distinct rows, so I had a big list of non-linked strings in the cells. Then I looped through them again and used this little snippet to make each cell link to the destination contained within the cell: code:
|
# ? Aug 13, 2012 18:08 |
|
DukAmok posted:Do you already have a list of all the file names? I've done something like this before, I used some VBA to import all of the full file directory names ("C:\Folder\File1.jpg") into distinct rows, so I had a big list of non-linked strings in the cells. Then I looped through them again and used this little snippet to make each cell link to the destination contained within the cell: Might be easier to show you.... I have photos of doors, with varying numbers of photos per door. Anywhere from 1-8 photos. I know which photos go with which door and am working on renaming them something that makes sense. Photo# 8-101-1 = Bldg. 8, Door 101, 1st photo,... etc. I then have a speadsheet with other door information and then within the spread sheet I want to hotlink to the various photos. I'm not sure what you are describing would work the way I have the spread sheet set up. Or maybe it will work on a different tab and then I could just cut and paste the link in my spreadsheet. I'm kind of at a loss when it comes to 'code' etc. like you posted, so if anyone has an idea you are probably going to have to walk me through it. Thanks.
|
# ? Aug 13, 2012 19:14 |
|
What he posted is a snippet of VB code that would turn a cell value into a link. So you would have to enter "C:\files\img_2069.jpg" into cell A1 and that macro would create the hyperlink for you to that location. full code would look something like this... code:
|
# ? Aug 13, 2012 21:36 |
|
FrankeeFrankFrank posted:I'm not sure what you are describing would work the way I have the spread sheet set up. Or maybe it will work on a different tab and then I could just cut and paste the link in my spreadsheet. Yeah, I can't quite make out the data on the spreadsheet but as best as I can tell, there isn't a quick and easy solution with the way that's set up. If your images were all named in a sort of hierarchical fashion, or there was a reference to translate those image names into that hierarchy, we could construct a pretty complex script to parse through those and assign links to your cells, but honestly that's not entirely useful, it might end up taking as much time as clicking a bunch, depending on how many links you have. Are you tied to this overall spreadsheet format? There are a lot of easier ways to categorize this data that will make later translations and modifications much easier. pizzaman5000 posted:Hello thread. I have a little question that I hope someone with greater Excel nous than me can solve. I would save the hassle and just get the data you need into the presentation workbook, unless there's some serious size/storage concerns. I've messed around with closed workbook references and such, and it's never really as efficient as I thought it would be, and a lot more maintenance intensive. Usually much easier just to have a little data duplication between workbooks and not worry about it too much. DukAmok fucked around with this message at 22:28 on Aug 13, 2012 |
# ? Aug 13, 2012 22:25 |
|
DukAmok posted:Yeah, I can't quite make out the data on the spreadsheet but as best as I can tell, there isn't a quick and easy solution with the way that's set up. If your images were all named in a sort of hierarchical fashion, or there was a reference to translate those image names into that hierarchy, we could construct a pretty complex script to parse through those and assign links to your cells, but honestly that's not entirely useful, it might end up taking as much time as clicking a bunch, depending on how many links you have. I'm pretty tied to it for this project, but we may have similar things in the future. Thanks a ton. I thought I thumbnailed that spreadsheet image... FrankeeFrankFrank fucked around with this message at 13:52 on Aug 14, 2012 |
# ? Aug 14, 2012 13:48 |
|
FrankeeFrankFrank posted:I'm pretty tied to it for this project, but we may have similar things in the future. Thanks a ton. Yeah, without having a consistent pattern for your image files it's going to be tricky, and probably more work than it's worth. Couple thoughts: Edit2: Take a look at my edit below, as I think the Hyperlink function will be easier than using VBA for this. I think it's going to be far easier if you figure out a way to get the file name and path into the spreadsheet, then run the VBA code DukAmok posted to convert it all into links. If all the pictures are in the same folder, you can set that code up to automatically attach the path, so all you'd need to type in is the filename. If they all have the same extension (".jpg"), you can skip that as well. Visual Basic .NET code:
code:
This won't completely automate the process, but it should reduce the amount of mindless clicking you have to do. Also, take a look at this MSDN article to figure out how to turn that VBA code into a working macro. Edit: Just realized there's a Hyperlink function. I would do the following: code:
kapinga fucked around with this message at 15:50 on Aug 14, 2012 |
# ? Aug 14, 2012 15:37 |
I have an excel workbook with 100+ tabs that need to be split into separate workbooks. Is there a shortcut to do this or am I doomed to right click on each individual tab?
|
|
# ? Aug 14, 2012 21:12 |
|
Goodpancakes posted:I have an excel workbook with 100+ tabs that need to be split into separate workbooks. Is there a shortcut to do this or am I doomed to right click on each individual tab? From the previous page: code:
If you want to save them to individual folders, Just change the following line from this code:
code:
Second part courtesy of Old James.
|
# ? Aug 14, 2012 21:17 |
|
kapinga posted:Edit: Just realized there's a Hyperlink function. I would do the following: Ah yeah I had forgotten about that, definitely easier for how he's got the sheet set up. For my particular use, I ended up skipping to the VBA because I think HYPERLINK has a character limit on the URLs, 255 if I remember correctly. Adding the link in via VBA bypasses that limit.
|
# ? Aug 14, 2012 21:36 |
|
Is there a good tutorial on how to use Excel 2010 Pivot Tables? The older version ones seemed so much simpler and better
|
# ? Aug 15, 2012 22:22 |
|
I don't think there has been significant changes in pivot table functionality at the level where anybody who would need a tutorial would notice. What are you having trouble with?
|
# ? Aug 15, 2012 22:35 |
|
ZerodotJander posted:I don't think there has been significant changes in pivot table functionality at the level where anybody who would need a tutorial would notice. What are you having trouble with? Really? There was no drag and drop functionality, and the thing just feels like a total mess. Am I just bad at Pivot tables now?
|
# ? Aug 15, 2012 23:36 |
|
Is it possible to use a full filepath and the indirect function?
|
# ? Aug 16, 2012 09:10 |
|
I have a series of procedures that uses the below code to search a column for a string and adds a comment in the next column. Let's say I have five procedures like the one below and each one searches for a unique name and enters a unique comment. Example: it searches for "Ron" and adds the comment "This person is Ron", another one searches for "Bob" and adds the comment "We have found Bob". Another searches for "Billy", another for "Timmy" and so on... Is there a way to combine this into one procedure that searches for all five strings and provides five unique comments? Sub Mark_cells_in_column1() Dim FirstAddress As String Dim MyArr As Variant Dim Rng As Range Dim I As Long With Application .ScreenUpdating = False .EnableEvents = False End With MyArr = Array("ron") With Sheets("Sheet1").Range("A:A") .Offset(0, 1).ClearContents For I = LBound(MyArr) To UBound(MyArr) Set Rng = .Find(What:=MyArr(I), _ After:=.Cells(.Cells.Count), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not Rng Is Nothing Then FirstAddress = Rng.Address Do Rng.Offset(0, 1).Value = "X" Set Rng = .FindNext(Rng) Loop While Not Rng Is Nothing And Rng.Address <> FirstAddress End If Next I End With With Application .ScreenUpdating = True .EnableEvents = True End With End Sub
|
# ? Aug 21, 2012 12:48 |
|
Here's an interesting one. Whilst it's possible to create a dynamic graph/chart in Excel using dynamic named ranges so the X/Y values automatically update if you add/remove data, is it possible to actually create new series on the fly? For example, I have a table of data: with each row forming a category (project). Each row has 6 columns (categories). It is simple enough to plot each project's scores on a graph and have the graph update if a new row is added. Graphs are plotted as scores vs project and each category is highlighted in a stacked column - each column made up of the 6 categories. The drawback is that the categories are predefined and can't be changed dynamically like the axes can be. For example, I'd like to be able to make each row a new series and if I add a new row, the number of chart columns would be updated. In short - can I swap the series and the x-axes data around - it would effectively mean that the x-axis would be static but the series would be dynamic. I can't find a way of doing this, so I guess it's not possible so I turn to the ever-knowing power of the forums. Here is a diagram to explain what I have: Basically: Each row of data is a column in the graph. Each (spreadsheet) column is a colour in a (chart) column. Can I make the series in the legend the x-axis and the x-axis a set of series that change dynamically? I appreciate that the style of graph may have to change to accommodate. Sonic H fucked around with this message at 14:35 on Aug 21, 2012 |
# ? Aug 21, 2012 14:27 |
|
In Response to Mouse Cadetcode:
Old James fucked around with this message at 15:43 on Aug 21, 2012 |
# ? Aug 21, 2012 15:12 |
|
Sonic H posted:Basically: Each row of data is a column in the graph. Each (spreadsheet) column is a colour in a (chart) column. Can I make the series in the legend the x-axis and the x-axis a set of series that change dynamically? I appreciate that the style of graph may have to change to accommodate. You can do this with Visual Basic to have a macro trigger when the workbook is opened. With normal Excel you could define the blank ranges before hand the only drawback is you would have extra listings in your legend when those rows are null.
|
# ? Aug 21, 2012 15:15 |
|
I'm using Concatinate to pull together words to make a sentence, but one of the columns is a time. What is the function you use to pull that into the sentence? Is there a general string function?
|
# ? Aug 21, 2012 15:21 |
|
Veskit posted:I'm using Concatinate to pull together words to make a sentence, but one of the columns is a time. What is the function you use to pull that into the sentence? Is there a general string function? Use the TEXT() function.
|
# ? Aug 21, 2012 15:24 |
|
Old James posted:You can do this with Visual Basic to have a macro trigger when the workbook is opened. With normal Excel you could define the blank ranges before hand the only drawback is you would have extra listings in your legend when those rows are null. I had a feeling I needed to delve into the magic that is VBA. Cheers
|
# ? Aug 21, 2012 15:57 |
ScarletBrother posted:From the previous page: Thanks for this macro ScarletBrother and Old James. I had to trouble shoot an error message, and I was gone last week so that delayed my thank you post (I had to install some Visual Basic tools for the macro to complete properly for the curious).
|
|
# ? Aug 21, 2012 21:51 |
|
Utterly stumped here. I'm no good with Excel but even the guy who I asked, who used to work with this stuff, can't get it to work. Any help would be great. Table looks odd as I've resized columns to hide data that I don't need at this point. I'm trying to get it to look up the value in BH. I then want it to return the value in row 3 (the table headers) which it matches - for instance, in the first row (4) it should return 1B. In the second row, (5), it should return 1B. I hope this is clear. I can't get hlookup, lookup or vlookup to work. Help! Gooses and Geeses fucked around with this message at 23:24 on Aug 21, 2012 |
# ? Aug 21, 2012 23:22 |
|
|
# ? May 11, 2024 07:01 |
|
I think this will do what you need if you put it in BI4 and copy it down: =INDEX($AC$3:$BG$3,0,MATCH(BH4,AC4:BG4,0)) Note: This will have problems if you have hidden values that also produce a match. If that is possible to happen then you'll just need to hard code an ugly nested IF statement or change the way your data is laid out. Aredna fucked around with this message at 00:13 on Aug 22, 2012 |
# ? Aug 22, 2012 00:10 |