|
Sub Par posted:You can accomplish this fairly easily with a macro. Try this: I don't know if it matters, but on the first sheet when I want to place all the hyperlinks, each column has a different number of names in it. So in the second loop I gave the row range of the column that has the largest amount of names in it.
|
# ? Jun 21, 2011 15:49 |
|
|
# ? May 13, 2024 08:20 |
|
Mr. Apollo posted:Thanks. I followed your instructions but I seem to have done something wrong as when I got to run it I get an "Invalid procedure call or argument" error. code:
|
# ? Jun 21, 2011 17:11 |
|
OK, so I replaced the code but now I get the following error: Is there a way to find out what exactly the error is?
|
# ? Jun 22, 2011 19:01 |
|
You can find out more by adding "on error goto errReport" on the line after "sub addhyperlinks()" and then right before "end sub" add this:code:
|
# ? Jun 22, 2011 21:13 |
|
Sub Par posted:You can find out more by adding "on error goto errReport" on the line after "sub addhyperlinks()" and then right before "end sub" add this: Yes, the sheet is password protected. I'll remove it and try again.
|
# ? Jun 23, 2011 05:04 |
|
OK! The revised code worked great. Now, I know that I can create a seperate macro for each sheet that I have data on, but how would modify the so that I could have a single macro to scan each sheet in the work book? Also, I played around with the code a bit and I noticed that if I give a row range with more rows than is in the "COMPANY LIST" sheet (where I want the links to be displayed) they I'll get that same "400" error. This list is going to be updated on an ongoing basis so as new names are added to the COMPANY LIST sheet and the correspoding data is put on the related sheet the macro will need to be run to update all the links.
|
# ? Jun 26, 2011 06:04 |
|
I'm trying to create a pivot table where there are multiple children of one item in the hierarchy. It's hard to describe, but here we go: I want to be able to show a table with a list of names and total cost. Each total cost is the sum of three values that are also in the table. My table is set up like this: code:
I'd like to be able to (in a pivot table, ideally) expand a "total cost" cell to show the parts that made that total. So it looks like this: Anyone have any ideas about how to do this?
|
# ? Jul 1, 2011 05:40 |
|
How hard is it to rearrange your source data? If you can set it up as 4 columns (name,id,item,cost) then it will be easy to get the pivot table very close to what you want. Note, you don't need a row for total in this setup. The pivot table will sum up the individual parts into a total for you. Once the data is in the above format, all you need to do is set the name as your column field and cost as your data field. Then double click on a name and select item and it will be added as an expanded 2nd column with the item level details. To show and hide the details for each name you just double click them. You only need to select the item field the first time and I'm sure there is another way to add it with all of the details collapse by default, but I don't know it without Excel in front of me.
|
# ? Jul 1, 2011 06:16 |
|
Aredna posted:How hard is it to rearrange your source data? If you can set it up as 4 columns (name,id,item,cost) then it will be easy to get the pivot table very close to what you want. Note, you don't need a row for total in this setup. The pivot table will sum up the individual parts into a total for you. I can probably write some VBA to rearrange the data to include separate rows for each item. If I manage that, how would I ensure the data looks like the mock-up above?
|
# ? Jul 1, 2011 18:19 |
|
DankTamagachi posted:I can probably write some VBA to rearrange the data to include separate rows for each item. If I manage that, how would I ensure the data looks like the mock-up above? Or, you could just make a few extra columns with formulas and base a pivot off that. For example, make E2 be "=if(A2="", E1, A2)" and copy it down to the bottom of the data. Then run a pivot off of columns C:E. You can hide the blank rows inside the pivot.
|
# ? Jul 1, 2011 19:10 |
|
esquilax posted:Or, you could just make a few extra columns with formulas and base a pivot off that. I ended up doing something very similar to this and it worked great, thanks! Next question: I have a pivot table with columns of scores, each row represents a person. How can I use conditional formatting to highlight cells where the value is greater than the value of the previous column? I want to make them one color if the value is trending up and another if the value is trending down. Thanks!
|
# ? Jul 6, 2011 00:46 |
|
I have an Excel 2003 worksheet to go through, containing about 54,000 rows. The cells contain email addresses. I need to retrieve only cells which contain a certain string within the domain name. For example, a selection of cells may look like this: code:
I really can't figure out where to begin with this.
|
# ? Jul 6, 2011 20:34 |
|
If your data starts in cell A1, put this in B1. It will return true for the addresses you want to keep: =not(iserror(find("abcd.net",A1))) Note: This will also find those that have "abcd.net" in the mailbox name as well, but I'm assuming it won't be in your data. With necessary error checking it's a bit more complicated, but this only finds the ones with "abcd.net" after the @ sign: =if(not(iserror(find("@",A1))),not(iserror(find("abcd.net",A1,find("@",A1))))) Aredna fucked around with this message at 20:41 on Jul 6, 2011 |
# ? Jul 6, 2011 20:39 |
|
Aredna posted:If your data starts in cell A1, put this in B1. It will return true for the addresses you want to keep: =not(iserror(find("abcd.net",A1))) Thank you so much for the fast help - this worked perfectly!
|
# ? Jul 6, 2011 20:51 |
|
How can I make the columns of my bar graph wider? I already have the gap width set to 0%, but there still is tons of space between each value. Here is what it currently looks like:
|
# ? Jul 10, 2011 18:04 |
|
Try checking the source data of your graph and see if you're referencing a bunch of blank cells. Having a bunch of blank categories after "Goal" and "Net" might make your graph look like that, because it would read the blank cells as columns of height zero.
|
# ? Jul 10, 2011 19:06 |
|
Lt Moose posted:How can I make the columns of my bar graph wider? I already have the gap width set to 0%, but there still is tons of space between each value. I've run into this before, the problem is the date fields. Create a second column with a text format (using =TEXT(<cell>,"m/d/yy") and use that column as your axis. (Excel is very helpful and goes "Okay, so you have a value then, and another value seven days later, so that must mean six days of no data" and draws it up like that.)
|
# ? Jul 10, 2011 20:55 |
|
You can also just change your x-axis to a category axis instead of a date axis.
|
# ? Jul 10, 2011 21:17 |
|
After changing it to a text axis and Thel's suggestion it works! It looks much better now, thanks.
|
# ? Jul 10, 2011 22:12 |
|
I'm having a problem that I've spent ages trying to solve and I don't know if it's even possible. I have a list of genes and their expression from an array experiment, but several of the genes are represented more than once. Is it possible to average the values of the non-unique entries? Here is what I have now: And here is what I want: So for genes with a unique entry, like A2M, I just want the actual numbers, but for A1BG I need the average of two rows and for NAT1 I need the average of three rows. If it were only 20 rows, I'd do it manually, but there are >36,000 probes on the array and only about half of them are unique.
|
# ? Jul 20, 2011 16:51 |
|
You can use the Consolidate tool. Click in the first empty cell where you'd like your new table printed out, then go to Data->Consolidate. Change the Function from Sum to Average, add your existing table as a data source, use labels in Top Row and Leftmost Column, and you should be good.
|
# ? Jul 20, 2011 17:16 |
|
Holy poo poo, how have I been using excel for this long and not known about that? Thank you so much!
|
# ? Jul 20, 2011 17:22 |
|
Jesus that Consolidate process I'll be telling everyone about Guess who never has to hack peoples ugly spreadsheets around with Perl anymore. I've got no experience with charts in Excel, and I'm trying to do something that I think is out of the box. Here's a sample of my data: What I want to do is create a line graph that has on the X axis the numbers 0 through 23 (representing hours of the day, in the HoursOfTheDay column) which may or may not exist in the data (Andrews starts at 6 instead of 0). Y axis should be based on the AVGKPS column. The trick is that I'd like to either create a chart for each value in the Plant column (obviously based on just their data), or if that's not reasonably possible, create a line on each graph that connections each of that Plant's points in a different color, with a key that shows Plant and color or some other way to determine which is which. Is any of this possible out of the box, or is this all VBA funk?
|
# ? Jul 20, 2011 17:29 |
|
It's easy to make one chart with all of them on it, but I believe creating separate charts requires making each chart individually. Use a pivot table on all of your data to rearrange your data like this: code:
To create separate ones for each plant, just highlight the hour column and the first plant and create your chart for Allentown. It will be easiest if you create it as an object in your current sheet instead of a new sheet. Copy and paste that chart, then select the duplicate. The current data ranges should be highlighted, so all you need to do is click and drag the data range to Andrews. With copying, pasting and moving data ranges you're looking at like 10-15 seconds per plant type for individual charts.
|
# ? Jul 20, 2011 18:04 |
|
Thanks for the help. The data points are so all over the place, that it turns out it only really makes sense if I run this overall instead of breaking it out hourly, which makes charts far less necessary. However, I've learned something
|
# ? Jul 20, 2011 18:22 |
|
I'm confused with how I should program this vlookup formula. Here's the gist: I have 4 arrays for each age/ gender group. 5 age groups, for a total of 10 arrays. They are all labeled, GXXZ, where XX is the age group variable (30, 40, 50, 60, 61), and Z is the variable for each array (A, R, P, S), and G is gender notation (M/F) So I can have variables such as M30R, or F60P. This comes out to 40 total arrays, with up to 20 rows (all have two columns). I use the formula =vlookup(target value,array,2,True) to go from the value to the point system. Target value are my A, R, P, S variables. Problem is I have 50 people, who get older, and get taken off list/ added to the list. So I wanted to add a second function to change the array value (M30R) so it updates when I change it from 30 years old to 31 years old. I created 8 new arrays that consist of the other 40 arrays. 4 based off female gender, 4 off male gender each subparts of A, R, P, S. The arrays look like this 30 M30A 40 M40A 50 M50A 60 M60A 61 M61A That would be array "MA" I then created a new lookup that is written like this =vlookup(age,MA,2,True). This works in the way that it spits out the proper MXXA array based off age. I try to nest it such as =vlookup(A_target_value,vlookup(age,MA,2,True),2,True). So that it calculates the appropriate array based off age and then spits out appropriate point value based off target value. This provides error n#a. Which doesn't make sense because the individual arrays (to calculate MXXA, and point value work. I've also tried to direct the =vlookup(A_target_value,array,2,True) formula to the result of a cell that only consists of =vlookup(age,MA,2,True), simply replacing "array" with b2. But it gives same error. Yet the direct link to the MXXA array works. I've also considered using VBA to create if, then statements that replace the XX part of the vlookup formula based off age, and the Z part based off gender but I'd have to figure this out (as I haven't done VBA is a long time). I probably should have brought this home from work, but I forgot to save it to my email. I hope this doesn't sound all
|
# ? Jul 21, 2011 00:21 |
|
HClChicken posted:I'm confused with how I should program this vlookup formula. =vlookup(A_target_value,indirect(vlookup(age,MA,2,True)),2,True) Is that what you meant?
|
# ? Jul 21, 2011 00:39 |
|
esquilax posted:I'm having a hard time parsing your post, but it looks like you're trying to use a value that you either pointed to or did a vlookup to as a named range. If that's the case, you need to use the indirect() function to change it from a text string to a "named range" string. As in, maybe I just had this explained to me by someone else "Because the nested vlookup is only going to return a single value, not an array. There are other functions if you need to dynamically calculate arrays."
|
# ? Jul 21, 2011 00:49 |
|
I've got a lot of data (genetic code) in Excel, where each letter is in a seperate cell. I need to copy the entire dataset into Word, but when I do so a tab is inserted between each letter. E.g. In Excel the data are like this: A|C|T|G|G|A|T|C|C|A When I copy to Word they're like this: A C T G G A T C C A Whereas I need them like this: ACTGGATCCA Any ideas?
|
# ? Jul 21, 2011 13:04 |
|
In word, Ctrl+F, click on replace. In the find box enter "^t" (without the quotation marks), and leave the replace box blank.
|
# ? Jul 21, 2011 13:27 |
|
Yeah that's what I've been doing. Only problem is that it takes aaaaaages as genetic codes are quite long. Oh well! Thanks anyway
|
# ? Jul 21, 2011 14:26 |
|
esquilax posted:I'm having a hard time parsing your post, but it looks like you're trying to use a value that you either pointed to or did a vlookup to as a named range. If that's the case, you need to use the indirect() function to change it from a text string to a "named range" string. As in, It actually worked. Thank you. I have another problem now though. Those four values needed to be added up. But if one of the target values doesn't have a value (either exempt or blank field) I need the calculated value (vlookup) to be a specific amount. I've tried making a field in my array such that: 0 0 1 2 25 40 exempt 60 But it gives me an error, no matter if pu the exempt value at top or bottom. I've tried this: =IF(H3=exempt,"60",VLOOKUP(H3,INDIRECT(VLOOKUP(B3,MR,2,TRUE)),2,TRUE)) and =IF(H3=exempt,"60",INDIRECT(VLOOKUP(H3,INDIRECT(VLOOKUP(B3,MR,2,TRUE)),2,TRUE))) Both give the error #NAME? B3 is pointing to age, MR is the approriate array, H3 is the time. My other option would be to create the value that exempt=x value, but I'd need 4 values and 4 different exempt. EDIT: This is my solution: I created 4 new rows for the data that are in a different location (off view) in each they have =if(target_value=0,needed point value,cell_with_vlookup_data) HClChicken fucked around with this message at 01:36 on Jul 22, 2011 |
# ? Jul 21, 2011 17:44 |
|
You need to put "exempt" in quotes. Also, use "false" in your vlookups - it forces exact match instead of just giving you the first value past the sort point. Edit: Your original post is very hard to understand, it's completely unclear to me exactly how you have your data stored and what your lookups are for. This might not actually be true, because I don't know how your sheet is set up, but what you're doing seems unnecessarily complex. ZerodotJander fucked around with this message at 03:23 on Jul 22, 2011 |
# ? Jul 22, 2011 03:11 |
|
Lady Gaza posted:Yeah that's what I've been doing. Only problem is that it takes aaaaaages as genetic codes are quite long. Oh well! Thanks anyway Only way you're going to get around it is with VBA (which would be pretty straightforward String.Replace operation) or maybe a macro solution like AutoIT. You could also do more with the file than just copy+paste though, I believe you could save out each file as TSV/CSV and then run an offline regex editor over it. E.g. textpad, in some kind of batch operation. Depends on how necessary the user interactive copy/paste aspect is.
|
# ? Jul 25, 2011 21:27 |
|
Ok got a small problem with a formula and I think my brain has died. Any help will be appreciative. I have three sections that grade the person on a percentage over how much they have completed. Each section is labelled Pass, Merit and Distinction. What I am trying to accomplish is to have a cell on my spreadsheet that gives an overall statement depending on those percentages. So for example: Pass: 100%, Merit: 100% and Distinction: 100% = In this scenario I want the cell to display Distinction. In another example: Pass: 100%, Merit: 50% and Distinction: 100% = I want the cell to display Pass as the person has failed the Merit section so cannot be graded any higher. Now currently this is what I have ended up displaying: code:
CR5 = Percentage for Pass CW5 = Percentage for Merit DB5 = Percentage for Distinction Which does not work. I'm probably missing something really simple here so feel free to call me stupid.
|
# ? Jul 26, 2011 19:31 |
|
You're missing an "else" answer for that last if. quote:=IF(CR5<=100%,"Fail",IF(CW5<=100,"Pass",IF(DB5<=100,"Merit",IF(DB5=100,"Distinction",PUTSOMETHINGHERE))))
|
# ? Jul 26, 2011 20:26 |
|
Sub Par posted:You're missing an "else" answer for that last if. I think I'm missing something else. code:
|
# ? Jul 26, 2011 20:50 |
|
I just tried this myself, and I think I've got it. If the following is the output you require: The formula you want is this: =IF(CR5<100,"FAIL",IF(CW5<100,"PASS",IF(DB5<100,"MERIT","DISTINCTION")))
|
# ? Jul 26, 2011 21:06 |
|
Scientastic posted:The formula you want is this: Brilliant, I knew it was something along those lines. Thanks for your help.
|
# ? Jul 26, 2011 21:24 |
|
|
# ? May 13, 2024 08:20 |
|
Hi guys, Im currently reading and parsing an HTTPrequest and inserting it into a worksheet via the following the code. code:
Here is the output I get (which is fine) but I would like it to not go through a loop populating. Here is what im trying and failing at. It just spits it all into A3. Am I storing it wrong? How the hell do I get it to just spit it out over the range?! code:
|
# ? Aug 2, 2011 07:30 |