|
Freeze pane.
|
#
¿
Apr 14, 2010 19:21
|
|
|
|
| # ¿ Apr 28, 2024 16:20 |
|
|
Find and replace 0 with nothing, make sure you check off the "match entire cell contents" box.
|
|
#
¿
May 12, 2010 18:08
|
|
|
All that does is return the value if it is present in Column B. Is Column B sorted in any way?
|
|
#
¿
Jun 10, 2010 18:18
|
|
|
If it's not sorted, I suppose you could use COUNTIF to determine how many total instances of the value there are, then use a loop to run match functions with ranges of decreasing size to find all of your cells.
|
|
#
¿
Jun 11, 2010 13:47
|
|
|
You probably want to use VLOOKUP. The syntax is =VLOOKUP(lookup_value, target_range, target_column, FALSE) Your lookup table has to be arranged so that your index (Zip) is in the leftmost column. So for example, if you have a table like this code:=VLOOKUP(G2, $A$4:$C$9, 2, FALSE) to look up the value in cell G2 in range A4:C9 (dollar signs added so you can fill down without changing the range), find the first occurence of that value (FALSE tells it to look for an exact match) and return the value in the 2nd column (City).
|
|
#
¿
Aug 18, 2010 02:46
|
|
|
Use SUMIFS.
|
|
#
¿
Aug 29, 2010 00:26
|
|
|
The vlookup range can be on another sheet, sure. =VLOOKUP(SHEETNAME(),'Alpha Tab'A:B,2,FALSE) Or something along those lines should work.
|
|
#
¿
Oct 9, 2010 07:49
|
|
|
Looks like you should be able to get something going using the INDIRECT function and concatenating pieces of your reference.
|
|
#
¿
Jan 4, 2011 22:11
|
|
|
As far as I know, there is no built-in way to do what you want. You can duplicate the effects of it though, using a dummy series. Add an extra column and 2 Label rows to your chart, with a 1 and a 2. pre:1 2 3 Yes 1 No 2 A 1 1 2 B 1 2 1 C 1 1 1 This creates 2 data labels to the left of the Y-Axis with no visible data points associated with them, looking exactly like Axis labels. You can then just edit the text of those labels to be whatever you want. It's a bit convoluted, but it works! 
ZerodotJander fucked around with this message at 16:48 on Jan 7, 2011 |
|
#
¿
Jan 7, 2011 16:38
|
|
|
Make sure you have relative references turned on.
|
|
#
¿
Jan 11, 2011 23:59
|
|
|
How are you selecting your range to sort during your macro record? Make sure whatever way you are doing that selects the full table and not just the range specified at the moment you record the macro. You could also try just adding a sort column and numbering all your guests in order, and sorting based on that instead of a multi-field sort.
|
|
#
¿
Jan 12, 2011 06:50
|
|
|
Without any specifics it is hard to tell. Some things you can do- Upgrading to an x64 architecture and Office 2007 or 2010 could potentially provide some benefit. Does every cell in the sheet need to recalculate every time he changes something? Turn automatic update off and then just force recalculate manually as needed. If he has a lot of pivot tables looking at the same data, you can make pivot tables function based off of each other and sharing the same data/calculations, which helps save memory/processing time. If he doesn't have a lot of pivot tables, maybe he should? But yeah it is pretty unlikely a 80MB spreadsheet will ever run anything close to fast, unless 75MB of it is embedded images.
|
|
#
¿
Feb 9, 2011 23:54
|
|
|
I would do a VLOOKUP from Column A into Column B and keep all the ones that don't error out. So in cell C1, =VLOOKUP(A1, B:B, 1, False) and fill down. This will look in Column B for the value in A1. If it is found, it will just reprint the value again. If it is not found, it will produce an error message.
|
|
#
¿
Mar 29, 2011 17:01
|
|
|
VLOOKUP looks for a value in the first column of a table, then returns the corresponding value X columns over. That formula looks for the value (A1) in the first column of a table (column B) and returns the value in the first (1) column of that table. If the value isn't found at all, it just errors out. The False just specifies that it looks for an exact match, rather than just returning the closest value in alphabetical order.
|
|
#
¿
Mar 29, 2011 17:52
|
|
|
jet_dee posted:Do people have any recommended books or online courses which I could look at to brush up on my Excel skills whilst I'm unemployed and looking for work (a lot of jobs for "numerate econ grads" specify being able to use Excel to analyse data and so on)? You can get really far by just going through the Ribbon and learning what all the buttons in it do.
|
|
#
¿
Apr 8, 2011 17:02
|
|
|
Is there any particular reason you're using a pivot chart? In general for almost all applications I find it much more convenient to copy data out of the pivot table into a normal table, then chart that with a regular chart. If you want the chart to be dynamic based on the pivot table, just create the normal table with references instead of copying the data out directly.
|
|
#
¿
May 9, 2011 19:01
|
|
|
Yes, but it requires a little bit of VBA. It also works much better for the Report Filter level than lower level fields. I'm going to assume you are working with Excel 2007 or 2010 here. First, you need to create a Support tab that contains your Pivot Table. Then, create a secondary table off to the side that is just references to the Pivot Table, duplicating it as a regular table but that will dynamically change if the Pivot Table changes. Off to the side, also list all of the fields in your Report Filter field in a column- e.g., if you are filtering by month, make a list of all applicable months. On your actual dashboard tab, create your desired chart, pulling the data from the secondary table. Then, on your dashboard tab, add an ActiveX ComboBox - you have to go into your Excel Options and check off "Show Developer Tab in the Ribbon" under Popular, then you will have access to the Developer Tab and can Insert ActiveX ComboBox. Go into Design Mode (next to the Insert button in the Developer Tab) and click on your new ComboBox, and click Properties. Note the name of your ComboBox - most likely it is ComboBox1, but it might be different if you've been experimenting. I find it helpful to switch to the Categorized view, instead of the Alphabetic view. Under Data->ListRows, enter how big you want your dropdown to be - if it is less than the number of values to be in the dropdown, you'll get a scrollbar. For example, 12, if you want all months to be visible; 6 if you just want half. You can change the Name of the ComboBox under Misc->(Name) if you want it to be something easier to remember. Under Misc->ListFillRange, enter the reference for your list of values on your Support Tab (e.g., Support!$D$5:$D$27). This populates your ComboBox selector with the possible values for your Report Filter. Now that you have your dropdown selector, you just have to tie it to your pivot table (and therefore chart). Close the properties box (everything saves automatically) and click on View Code (make sure your ComboBox is selected, you want the code to be associated with the ComboBox). Enter in the following VBA (with Sheet and object names adjusted to your specific needs): code:It might seem complicated if you haven't worked with ActiveX Objects or VBA in Excel before, but it's actually pretty straightforward. Common sources of error: Make sure that your Support tab's field list for the Dropdown is in the correct order, and everything is capitalized and spelled correctly - it has to match the values the PivotTable will find valid exactly. Make sure you are referencing the correct ComboBox and PivotTable. Make sure you set your Support tab's zoom to 100%. For some bizarre reason, the ComboBox will produce an error if the Dashboard and Support tab are not set to either 100% or exactly the same zoom. So just make sure that the Support tab is at 100% and then it doesn't matter what zoom the Dashboard is at. Hope that provides what you need!
|
|
#
¿
May 9, 2011 20:34
|
|
|
No problem, I have been doing a lot of this stuff this year, so it was just writing up something I've been doing lately anyway.
|
|
#
¿
May 9, 2011 20:42
|
|
|
You can also just select that column, click Data->Text to Columns and specify it as delimited by the character 'N', then find and replace the W with nothing and you're good.
|
|
#
¿
May 13, 2011 01:16
|
|
|
I believe there is a way to import data from Access into Excel automatically and update stuff, but unfortunately I don't have any experience with it. Good luck!
|
|
#
¿
May 13, 2011 16:23
|
|
|
I don't understand, what is your chart plotting? Sum of Peach is 33% in each of the months in your table and that is what the labels are, but the blue bars are not the same height. What is that percentage of?
|
|
#
¿
May 14, 2011 20:51
|
|
|
For each new row you can just enter in a formula to subtract your payment amount from the amount above it.
|
|
#
¿
Jun 10, 2011 14:39
|
|
|
You can also just change your x-axis to a category axis instead of a date axis.
|
|
#
¿
Jul 10, 2011 21:17
|
|
|
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
|
|
|
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
|
|
|
coyo7e posted:This may be a dumb question but I can't figure out how to do this clearly in Excel 2010 for some reason.. Combo boxes. I'm drawing a blank on how to make a combo box spit out anything besides a raw integer "1, 2, 3, 4" etc. You need to use a ActiveX combo box rather than just a Form box. The Form Box will return the key for the list item selected - e.g., if someone selects the 4th item, it returns 4. You can use that with a VLookup if you want, or you can use an ActiveX box which will hold actual values. I made a huge post with instructions on using ActiveX combo boxes to create a dynamic chart a couple of pages back, everything you need to know should be in there.
|
|
#
¿
Aug 16, 2011 19:58
|
|
like I am.
|
You can actually very easily do what you are looking to do with Google Spreadsheets and the importXML function. =importxml("http://www.google.com/search?q="&B2&"&pws=0&num=1","//h3[@class='r']/a/@href") Enter the keyword you're searching for into cell B2. This constructs a Google search results page URL for that keyword, with personalized search turned off (pws=0) and only the top result (num-1). The importxml function then runs a XPATH query on the page (since all webpages are xml documents) and returns the HREF from the Anchor tag that is enclosed in an H3 tag with class=r - which happens to be the URL for a result. One spreadsheet can only support 50 importxml queries, but it's still really useful.
|
|
#
¿
Aug 20, 2011 03:55
|
|
|
Assign numeric values to the servers and then edit your axis labels.
|
|
#
¿
Aug 26, 2011 18:11
|
|
|
How big is your table? If it's not huge you can just set a conditional format to highlight duplicates.
|
|
#
¿
Sep 26, 2011 23:12
|
|
|
I'm not really sure what you are asking.
|
|
#
¿
Oct 19, 2011 17:57
|
|
|
Do a vlookup for each item from column A into column B, the ones that error out are the ones that are missing from column B.
|
|
#
¿
Oct 19, 2011 20:52
|
|
|
Edit: never mind.
|
|
#
¿
Nov 4, 2011 21:50
|
|
|
All you need to do is divide it out and multiply by 100.
|
|
#
¿
Nov 5, 2011 05:52
|
|
|
Do you maybe have a merged cell and some hidden columns?
|
|
#
¿
Nov 29, 2011 16:42
|
|
|
=MAX(C1:C3)+MAX(COUNTIF(C1:C3,6)-1,0)
|
|
#
¿
Jan 13, 2012 19:03
|
|
|
This looks like kind of job Google Refine was built for - http://code.google.com/p/google-refine/ If you can't find a good way to automate it in Excel, this tool should make manually cleaning the data much easier compared to doing it directly in Excel.
|
|
#
¿
Jun 21, 2012 22:21
|
|
|
Just add extra rows to the end, 1 for each identifier, with no name. Then sort by the numbers to insert the extra rows where you want them. Filter your second column to blanks to delete the values in column 1, and you're done. Edit for clarity- Do this: code:ZerodotJander fucked around with this message at 21:43 on Jun 27, 2012 |
|
#
¿
Jun 27, 2012 21:40
|
|
|
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
|
|
|
Do you need this sheet to look a certain way or something? Easiest solution that comes to mind is Text to Columns with " " as a delimiter. This will split each cell into either 2 cells or 4 cells. Sort by your new column 3, which will be empty for <1 hour values, grouping the >1 hour and <1 hour entries together. Then just do math on the hour and minute values and you're good.
|
|
#
¿
Sep 18, 2012 03:59
|
|
|
|
| # ¿ Apr 28, 2024 16:20 |
|
|
Tricerapowerbottom posted:Simple question: I have a series of long urls that I want to be truncated into small cells. How can I either make them all display something like "url" in a batch, or force the cells to contain the text in their own boundaries?
|
|
#
¿
Sep 25, 2012 03:59
|
|