Excel 2011 :: Sorting Blank Cells To The Top Of List
Feb 5, 2013
I am trying to sort a list of business development leads. When a lead is dead I enter the date in the column entitled "Closed". Therefore, the Closed cell for active leads is blank. Whilst I still wish to retain the information of dead leads, when sorting I would like them listed at the bottom of the list. However, whenever I sort on the Closed column, whether using newest to oldest or oldest to newest, the blank cells are always at the bottom, instead of the top.
I'm using Microsoft Excel for Mac 2011. I'm creating a drop down box from List within Data Validation. It's only letting my create a list of 17 names. My longest list is 63 names. What do I need to do to create a list that will support that?
I am using Excel 2011 on a mac. I have a selection of data that lists a repeating code (say group 1, group 2, group 3 etc)and a value (-1,-2.3,3,6 etc) and need to find a quick way group together all the codes and next to that a value that counts how many values who share the same code hold a value greater than -1? I can do this myself in a pivot chat with count totals but my issue is how I can show the number of codes greater than -1.
Attached is an example of the data I want to sort.
I call prospects to generate business. I want to respect their time by calling before 8:00 PM so I need to know their time zone. I have a column in Excel that lists their area code, but I don't know their state.
I have tried everything imaginable to duplicate the results of the previous post to no avail. I'm using Excel 2011 on a Mac.
I'm using =VLOOKUP(B2,$I$2:$J$364,10) for my formula, where
B2 is the area code $I$2:$J$364 is the range where the area code/time zone data lives (I = area codes, J = Time Zone) 10 is column J, i.e. the Time Zone column that "feeds" the answer.
I've attached a screen shot to show what I'm doing.Screenshot at Feb 05 15-33-01.jpg
I have a list that I need to move to another column without spaces. I have it moving without spaces but it is only one item over and over again. I am using Excel 2010 and that may be my issue. I have attached the exact worksheet and formula.
I have this code to sort numbers from smallest to largest but i need it to ignore blank cells. Sub sort1neg() ' ' sort1pos Macro ' ' Range("a4:aq174").Select ActiveWorkbook.Worksheets("sort").Sort.SortFields.Clear
I have data from (row 1, column 1) to (row 53, column 5) on 283 consecutive worksheets in a singular excel file that I would like to be presented on a singular worksheet starting from the data on worksheet 1 and descending to the data on worksheet 283.
I am looking for a copy and paste loop solution that will copy the data from each page and sequentially paste the results on a singular output page in descending order (worksheet 1 data, worksheet 2 data... etc) so that I can sort the data.
The range of cells in column B containing the items has a name "ColStreams"
I need to go through the list, filling in each blank cells with the value contained in the first non-blank cell above it - so, in this case, rows 6 and 7 would contain "Item 2", row 9 would contain "Item 3", rows 11-13 would contain "Item 4" and so on.
Using Excel 2007, I have a very simple spreadsheet made up of only two worksheets that I am using to track the songs played by a band during a current tour.
One worksheet is called "Summary" and the other is called "Setlists". In the Setlists worksheet I list every show played (10 shows so far), and every song played from each show. In the Summary worksheet I have very song listed that has been played during the tour in one column (A), then the number of times that song has been played in another column (B). Column B, the number of times each song has been played, is populated by a COUNTIF function that looks at the Setlists worksheet and counts each instance of each song.
What I want to do, very simply, is to sort on column B in order to display the list of songs from the most played to least played. That is where I run into a problem. When I sort my list of numbers, I end up with a seemingly random list that certainly isn't from most to least and I can't figure out why.
Is it possible that the COUNTIF function which populates that column of numbers is somehow throwing off the sort?
I have a pivot table in sheet1 and references in sheet2 like
Code: ='Sheet1'!A1 and so on to copy the whole thing to make it the source data for a bubble chart.
Now, I want to convert the table in sheet2 into a list via Ctrl+L to be able to sort by names with a dropdown menu. Unfortunately, I have to copy all rows from 1 to 1000 to account for possible increases in the pivot table size. This results in blanks in the list and when I want to sort it, I have 990 blanks before the first data rows show up. Not very neat
I have a list in column L2:L352. I would like to be able to start at AD2 and drag horizontally so that AD2 = L2, AE2 = <BLANK>, AF2 = <BLANK>, AG2 = L3, AH2 = <BLANK>, AI2 = <BLANK>, AJ2 = L4, etc.... until L352 has been autofilled.
For Mac Excel 2011, though I think my question would apply to any modern version of Excel.
This is a little odd. I have a sheet where, for various reasons (see below ### if interested), I need to copy a hyperlink, replace a substring, and then return the new hyperlink (with old friendly text) to the original cell. (I actually have to do this with an arbitrarily-long column of links.) This was my plan of attack:
* Pull the URL from the hyperlink cell. * Use FIND and RIGHT to strip out what I need and replace it * Use HYPERLINK to build a new cell with the original friendly text and the new hyperlink. * (Manually) copy this new link (really, a column of links) back to the original cell (really, column of cells).
Here's where it breaks down:
a) If I simply cut-and-paste, the formula used to pull the hyperlink becomes circular (as do several others), and Excel complains. b) If I cut-and-paste-special (values only), I get just the friendly text, no link info. Likewise if I try to reference the "fixed" hyperlink.
The erroneous hyperlinks are in column K. My calculated correct hyperlinks are in column S. (There are similar errors and fixes in L and T, respectively.)
I want to open all csv files in a folder and just copy and paste some columns into another file. I am having a bit of trouble with the following code which can be found at ozgrid and I have modified slightly so it looks in the current directory instead of a fixed path...
Code: Sub RunCodeOnAllFiles() Dim lCount As Long Dim MyDir as string
For starters, if I don't use on error resume next I get a runtime error 445: object doesn't support this action. So when I use the on error resume next, then by the time it gets to the foundfiles line, there are no variables set, so on the next line (workbooks.open) nothing happens.
Is there an easier (more up to date?) method to open all csv files (or excel files) in a folder?
So i am working on an attendance tracker here. I already have aligned on a separate tab all of the works aligned next to their manager
EX Column A: Column B: Worker Name Manager
What i would like to do is create a drop down menu with all of the Managers name listed to where someone can select that manager and when they do all of the workers names show up in Column A and then that Manager Listed next to them.
Is there a way to do this... I am using office 2011 btw. Once when i get this last part completed with this drop down menu i should be complete!
I'm using Excel 2011 on a MacBook Pro. Here's my situation:
I'm running a monthly budget report that's got three spreadsheets within the workbook. Spreadsheet #1 is summary sheet with a few pivot tables. Spreadsheet #2 is the data sheet, where all of the information is entered, and is what feeds the pivot tables on Spreadsheet #1. Spreadsheet #3 is just a list page that populates the drop-down list on the second sheet.
As this is a monthly budget report, I'd like to find some way to archive the first spreadsheet (the summary sheet) every month. Ideally, I'd like to be able to create a macro-driven button that, when clicked, does the following actions:
1) Creates a new spreadsheet, with each click, named for the current month and year. 2) Archives the exact contents of Spreadsheet #1 by making an exact copy of its current contents, and places this copy into the newly created spreadsheet. 3) Clears the data from Spreadsheet #1 AND Spreadsheet #2.
To summarize, I'm trying to use a button that, when clicked, runs a macro that archives the current month's data into a new spreadsheet, and then clears the data from Sheets #1 and #2 so that I can start each month with a clean slate. If there's a way to only clear the data from certain cell ranges (as opposed to a complete wipe of the sheet), that would be even better.
How to insert a Calendar Dropdown for Mac Excel 2011? Also, do I have to readjust this (for lack-of-a-better-word) 'add-in' everytime the calendar year changes? This will be used for a payroll template.
My software is Excel for Mac 2011 version 14.4.3 (140616).
I am using the present value formula =PV(rate, nper, pmt, [fv], [type]), with the pmt expressed as a negative number, to determine the present value of a lump sum that would generate specific, equal, regular annual payments, exhausting itself in the process. I fill it in this way: rate is an annual rate, nper is the number of years the payments run, pmt is the annual amount paid each year, fv =0, type =1
I would like to be able to have a similar formula in Excel that would do the same thing except that it would take into account a time period where no payment is made, ie a "delay".
For example using the PV formula I know that if I have $49,019.96 invested at 1%, it will generate $10,000 per year for 5 years, leaving a net investment of 0. I would like to know the formula that would tell me how much I would need to invest at 1% to yield $10,000 per year for 5 years, again exhausting itself completely in the process, with the first $10,000 payment starting 3 years from now. I know it would be less because I would have the full amount earning interest for 3 years at 1% before any payments need to be made.
I am working on a spreadsheet for my Building company. I'm building a tool to price for Fascia, Soffits and Cladding.
I'm using Macbook Pro Microsoft Office for Mac 2011
Please see attached file
The first sheet holds all the lists of products, Category Headings list is in column A and then all the relevant products and prices are then from B to BI. I have defined each category with a name by selecting the cells and entering a name in the name box.
The second sheet is a Calculator in which I would like a to have a drop down list in the Category Column (Which I have worked out how to do myself - good old google) and then a drop down list in the second column which lets the user select from a list of results based on the selection from the previous column.
Example: Category(B3) - 18mm Fascia/Replacement Board (Square White) - Drop down menu taken from sheet 1 A3:A33 Description (C3) - Drop down list containing all the options from D2:D15 Named "FasciaReplacementBoard18mmWhite"
So basically, whatever the user selects in Column B (from the category list) a drop down list would be available in Column C
The Value column would then show a value based on the options selected.
I am using Excel 2011 and we have a link data source to the outside SQL Server.
For the 1st step, I linked to the Database and retrieve the data.
2nd step. Sort the data by using the column "Description" Descending. Add another column to the table and put the 1, 2, 3, etc in that column.
3rd step. I clicked on "Refresh" and the new column values are not consistent. I mean they are moving. So, I added another row in the database to test and it becomes very obvious. It can be clearly seen at the following image. The value of "A" should always be "10", but it has been changed to "1" after the refresh.
how I could retain the position of another column after the data refresh?
I have Excel MAC 2011 and my spreadsheet was created with multiple cell colors randomly throughout the spreadsheet.
I need to change all the royal blue colored cells to "no color" without changing the data contained in the cell. For example; Cells A5:A38 are blue, A39 is green, A40:A50 blue, A51:60 green, ... Cells B5:B15 are grey, B16:B20 are blue, B21 is yellow...
I can't find the "Advanced Options" for Find and Replace that might have allowed me to specify a color change.
I have switched from an ageing Mac Powerbook, Excel 2004 for Mac, and an out-of-date version of Firefox to an IMac with Excel 2011 for Mac and Safari as a browser. The only reason why I mention this is because I don't know if it was easier to do this on an older platform (one of those bizzare situations where going forwards is actually a step backwards).
In short I like doing things with Sports data in Excel. Usually I would hit my favourite website for pulling sports data (which incidentally I don't know if this has changed as I've been too busy to do much of this kind of thing for about 4 months - or if it is just a different format on a newer machine/different browser);
The link should be showing NFL results for this year so far (I like the site as it has a handy amount of other sports etc.).
I would usually then go edit/paste special - unicode text and then hit the paste options, use the text import wizard, go delimited, tabs - and usually everything would come in on a row and then I could do what I need to do with it (e.g. use Excel's text functions to get the data - taking spaces out for example - to where I want it to do other things).
The problem I seem to be having now is that when I do the above - things no longer seem to come in on the same row (which makes life difficult for doing things easily with the data). Once things end up on different rows it becomes a pain to do basic things.
In short is there a way round this (or do I need to find another source for my data with tables laid out in a more friendly way to "borrow" data)?
I am building a code based on a command button in a row, which will check is a worksheet exists (message), if not create a worksheet from a template (from another worksheet specific to a on a cell value in the same row), rename the worksheet based on a cell value in the same row.
I am having some success for each task with exception to relative cell values .....
As the code will be specific to the row (one button per row) Questions:
how to i determine the location of the button that is clicked? (I assume once this is established i can use to pull values in the same sheet on certain columns....?)