Delete List Of Custom Formats

Feb 28, 2013

When you right click on your mouse, and you select:

Format Cells...NumberCustom
You can see many formatting styles.
Some of them impossible to delete eg: "General" or "@"

But some others easy to delete one by one pressing the Delete button.

In my Workbook eg "#.##0,00 ;[Red]-#.##0,00 ;; @"

Is there any way of deletting all of then by vba.

I mean all the different custom formats that I am carrying from book to book, from job to job long time ago.

I would like to have just the ones excel gives us by default, not the rest.

View 1 Replies


ADVERTISEMENT

How Do I Delete Custom Number Formats

May 17, 2006

I need to create some custom number formats, but after a few failed tests I
have a lot of junk that I'd like to remove. I've checked the Microsoft
knowledge base which suggest there should be a delete button, but I don't
have one.

View 14 Replies View Related

Custom Number Formats

Mar 20, 2009

I need to create a custom number format for a recorded delivery number in an excel sheet. I want it to look like AA 1234 1234 1GB in the cell even if the user enters it without spaces e.g. AA123412341GB. I have had a bit of a play around with underscores to get spaces but nothing seems to make it change.

View 4 Replies View Related

Custom Time Formats

Sep 8, 2008

I've been trying to create a work schedule and I am having trouble formatting it just the way that I want it. Basically, I am looking to put in the time with an AM or PM after it.

The problem that I am having is that if the time is on the hour I want it to display as 4 PM and if it is not on the hour, then I want it to display as 4:30 PM. I don't want 4:00 PM with the 2 extra zeros. Is there any way to do this with conditional formatting or some type of formula? I just don't want to have to change the number format every time I have to enter a time that is not on the hour.

View 9 Replies View Related

Custom Cell Formats

Jan 27, 2007

I would like to display the following number of hours as day hour minute format
48.0 hrs as 2d 00h 00m.

View 4 Replies View Related

Custom Formats For Two Different Cells

Feb 20, 2007

i've inherited a spreadsheet that is causing me a problem with a custom format. I have two cells each with different custom formats. The first (cell A) is formatted as thus 0.0;0;. The second ( cell B) formatted as 0.000;0;

The number in each cell will vary. Cell A is always positive and cell B could be positive or negative. The problem occurs in Cell B when this should be a negative number as it picks this value up from another cell ,say cell C. so Cell C= -0.614 but cell B shows this as being 1. I'm sure the author of the sheet did this for a reason. Why does the cell display the number 1

View 3 Replies View Related

Linking To Custom Number Formats?

Nov 11, 2011

I want to display a cell with the value -1,000 as Negative 1,000. In custom number formats I have selected #;"negative" #,###;; which does indeed display the cell with -1000 as Negative 1,000, however, I want to be able to link to this cell and have the link pull in Negative $1,000.

View 9 Replies View Related

Join Digits From Multiple Cells While Retaining Custom Formats

Apr 2, 2009

I have four different cell.

Cell a1 contains 6 digits
Cell a2 contains 1 digit
Cell a3 contains 6 digits
Cell a4 contains 4 digits.

Cells a3 and a4 may contain a number less than number of digits specified above but are custom formatted 000000 and 0000 respectively to ensure they have the correct number of digits. My problem is when I try to combine the four cells they lose their formatting. I have tried =A1&A2&A3&A4 and = concatenate(A1,A2,A3,A4). But both formulas loose the formatting from the individual cells.

View 7 Replies View Related

Comparing List Of Names In Different Formats

Jan 14, 2013

I am constantly given the task of comparing to list of names that come from different sources. One source(SourceA) provides the names in a two Cols as first name and last name. Luckily Source A usually just has first and last name list but sometimes they have a third name or middle initial included and it could be in the first Col or the second no real pattern. The second source(SourceB) provides their list as a single Col and it has every given name for the person including suffixes and prefixes. A couple of examples of Source B names would be-"John Henry David" or "Dr. Billy Bob Adams Barone Beard IV".

I have spent countless hours and days probably weeks on some list as there are anywhere between 20-50,000 names per list I am sent. I am hoping there is a better solution to compare these names. As of right now the only thing I can do right now is manually trim the data from SourceB down to a format like "John David" and "Billy Beard" and then combining the Cols from Source A into the same format and then comparing them.

View 3 Replies View Related

Delete Custom Menu Bar / Toolbar

Oct 26, 2006

I was making a custom Menu Bar in excel. In order to troubleshoot I executed the code a no. of times, resulting in multiple but same menu bars in excel application. Now the problem is that even if I quit excel, the menu bars dont seem to go..How do I get rid of them.

View 2 Replies View Related

Sample Code To Add/delete Custom Menu Items

Feb 22, 2007

Option Explicit
Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCustomMenu As CommandBarControl

'(2)

Set cbMainMenuBar = Application.CommandBars("Worksheet Menu Bar")

'(4)

Set cbcCustomMenu = cbMainMenuBar.Controls.Add(Type:=msoControlPopup)

'(5) Give the control a caption................

I have created several spreadsheet with customized menus. In step 5 when I type
a period the options for selecting Caption does not appear. In step 6 the 'Control' word didn't appear in list. Sometimes the options in step 6 do appear BUT not the option for Controls. I have created spreadsheets with menus that work but the only way I could do it was to cut and paste from the website and then modify it.

View 3 Replies View Related

Finding The Biggest Value In Custom List?

Apr 26, 2014

I know how to make customs lists, but it's not getting me anywhere.

Let's say I have a lot of data containing values as follows and in the following list I sorted them from small to large:

5a
5a+
5b
5b+
5c
5c+
6a
6a+
6b
6b+
6c
6c+
7a
7a+
7b
7b+
7c
7c+
8a

and so on... In the list, the date isn't sorted from small to large, many values (if not all) occur multiple times and on occasion some values are added to the column. With that last part I mean that you can see it as a batch of 500, and if so far the batch only contains 450 values there will be (for the time being until items get added) 50 empty cells in the column as well.

You can't order numerically or alphabetically. I know I can make a custom list and then just sort according to that custom list so the biggest value would come out on top or bottom but that's not what I'm looking for either, as the data needs to be sort by another colomn (dates in chronological order).

So I'm just looking for a formule saying "return the biggest value if the order of things is as I typed above'.

View 14 Replies View Related

Custom Validation List With Conditions

Oct 17, 2013

I am trying to create a validation list, with its source data being filtered from another sheet of the same workbook. I also need to validate the drop list against some conditions in the source table.

Here is an example of the data am trying to create`(I want to create the droplist for the column Contract):

"Contract Definition" Sheet or Table:
-----------------------------------
Company Contract Service Report End date: Status
--------- --------- -------------- --------- ------
CompanyA ContractA1 Service 1 31/12/2014 Active
CompanyA ContractA2 Service 2 31/12/2014 Active
CompanyB ContractB1 Service 1 31/12/2014 Inactive

"Service Reports" Sheet or Table:
--------------------------------
Company Contract Service Report
--------- --------- ----------------
CompanyA ContractA1 Service 1
CompanyA ContractA1 Service 2
CompanyA ContractA2 Service 3

In the Service Reports Sheet, i want to create a list for each Contract, based on the specified Company and on the Status field, as defined in the Contract definition table.

View 8 Replies View Related

Add Custom Sort List To VBA Code

Mar 12, 2009

I have a Macro that I have recorded that autosorts several columns for me. This works fine as I have 5 columns that need to be sorted in a particular way.

The problem is that I have a custom list which I stored using the: Tools>Options>Custom List tab. Now this works fine on my pc as I have the custom list stored on my PC.however the problem is when someone else uses it on their PC it may no longer work as they won't have my custom list stored on their PCs.

Is there a way to store the custom list in a Macro and then use that list to sort to the criteria needed. For example "One, Two, Three, Four" will not store in alphabetically, so hope would I be able to sort so that they would appear in a chronological manner? Below is the code for the Macro I recorded. As you can see one of the lines says "OrderCustom:=6"; this I am assuming is reading from the list I created.

View 2 Replies View Related

Using Custom Sort List Within Macro

Aug 3, 2009

I have following code, which sorts data if user "double clicks" on cells A1, B1, C1, or D1. If the user "double clicks" on cell D1, I want to sort by column D but I want to apply a custom list. The data is in the "Custom Lists" table but I can't figure out how to apply to my code.

The sequence/sort order of the list is as follows:
aaa+, aaa, aaa-, aa+, aa, aa-, a+, a, a-, bbb+, bbb, bbb-, bb+, bb, bb-, b+, b, b-, ccc+, ccc, ccc-, cc+, cc, cc-, c+, c, c-, ddd+, ddd, ddd-, dd+, dd, dd-, d+, d, d-
code is as follows:

View 3 Replies View Related

How To Create Custom Sort List

Aug 25, 2013

I am trying to create a custom sort list by going to preferences/custom lists and typing 37 items into a new list in Excel. It will not take all of the items after I type them in. Is there a limit as to how many items can be in the list. It seems to only take 18-20 items out of the 37 and erases the rest.

View 9 Replies View Related

Custom List To Sort Data

May 28, 2014

I am having trouble finding info on this. I am trying to create a custom list to sort data.

I only want to sort two of the letters in my first Level or Column. For instance I want to sort Column A Alphabeticaly as A, B, C, D, but once I get to E, have the rest of the list in a random order that is sorted by my next level. Is there a wildcard character that can be used to have my next sorting level take over once I get to E?

View 4 Replies View Related

Rounding A Number Up To The Next Value In A Custom List

Nov 17, 2008

I have a scenario where I calculate the size of a piece of process equipment, and then select the next highest size from a list of standard equipment that we supply.

I've managed to write an ugly brute-force solution to a relatively straightforward previous situation, but I'm reluctant to redo the entire exercise when I'm sure there's a more straightforward way of doing this.

An example of the problem:

We sell equipment in sizes 1, 3, 8, 12 and 25. I calculate that the piece of equipment theoretically needs to be size 19.6. I want a formula that will automatically generate the answer to this as 25. Similarly, if my formula shows a theoretical size of 12.01, I want the formula to state the actual size as 25.

My previous method of solving this was a series of nested ifs, searching for the rounded value of my formula output, then that value +1, then +2 upto a maximum of +5. This wasn't a pretty sight, but it worked for that situation.

Are there any other ways of solving this that are more elegant than mine?

View 9 Replies View Related

Add List As A Subchoice To A Custom Menu

Jun 2, 2006

I have create a custom menu and want to add a choice and a subchoice that will guide the user to insert a number in an inputbox or under a list. Then the user's selected number it will be used as parameter for a vba code.

Eg.:
The choice is: How many rows will be marked
The subchoice : 10 (default value)

What i want is the following:
If the user choose the above subchoice then it has to be appeared an inputbox or something like that in which he has to insert a number, eg. 45. After that the choice must shows

The choice: How many rows will be marked
The subchoice: 45

This parameter must be active all the time that the user uses the workbook. At the end, if the user saves the workbook, the next time it will be opened, the subchoice must have the value 45.

Look at the attachment image to see exactly what i want
Here's the code. Just copy it under a module

Option Explicit

Dim cbMenu As CommandBarControl
Dim cbSubMenu As CommandBarControl
Public MenuSeries

View 9 Replies View Related

Custom List Order Of Names

Mar 21, 2008

I Have A List That Is Generated From Another Program Dump Info Into Excell.
This List Has Names In One Colun And Corresponding Values In The Next Column. My Goal: Get The Name And Values To Be Listed In A Specific Order To Be Copied To Another Workbook. Issue: All The Names Do Not Always Get Sent From Original Program. Example:

Person1 Always Needs To Be In Row2
Person 2 Always Needs To Be In Row3
Etc
However Person1 May Not Be On The List Today. In Which Case I Need Row2 Blank

View 2 Replies View Related

A Custom Sort List Longer Than 255 Characters?

Apr 30, 2009

I have a large list of parcels to be delivered which have names, the last 2 letters of the postcodes, addresses tel. no. etc. I have created a custom sort list of postcodes in the order I want to deliver them. I can only list a total of 85 postcodes since there are 2 characters in each postcode and a return at the end of each line, hence 85*3=255.

Is there any way I can make excel sort my list of parcels using a spreadsheet list of the 2 postcode letters of unlimited length? There is a theoretical maximum of 676 postcodes in my area (26*26) so I would need it to be at least this long. It would also be more convenient if it were an excel based list since the editing of such a list would be more convenient.

View 3 Replies View Related

Custom Data Validation With Dropdown List

Jun 30, 2008

I have a data validation in a cell that works like this : if a control cell has the value "F" it validates the input with some dates in a custom list, if not it does nothing.

The formula looks like this :

=IF(F2="F",datelist,"")
It works fine but I also want it to display the drop down list when cell F2 = F, now all i can do is type a value and it checks if it's ok.
Can I do this without using vba ? I will send this to other persons and I don't want the macro warning to pop up when they open it.

View 9 Replies View Related

Auto Custom Sort List On Certain Days

Jan 23, 2009

I have a fairly large watch collection as below which I wear on a daily rotating basis(!).

FORTIS
Seiko
OMEGA SPEEDMASTER
Seiko
SINN 144
Seiko
Accurist
Seiko
TEMPTION
Seiko
OMEGA TRIPLE DATE
Seiko
Junkers....................

View 9 Replies View Related

Custom Function With Auto List Members

Apr 27, 2007

I have created a UDF which determines what the name of the day is.

Public Function Dagnaam(daydate As Date, Optional Language As String = "Dutch")
weeknl = Array("Maandag", "Dinsdag", "Woensdag", "Donderdag", "Vrijdag", "Zaterdag", "Zondag")
weeken = Array("Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday")
dayno = Application.WorksheetFunction.Weekday(daydate, 2) - 1
Select Case Language
Case "Dutch"
Dagnaam = weeknl(dayno)
Case "English"
Dagnaam = weeken(dayno)
End Select
End Function

What i would like is when the user uses this function in the worksheet the field Language holds a dropdown list with predefined languages.

View 6 Replies View Related

Custom Sort Data Based On List With More Than 255 Characters

Apr 21, 2014

How to sort a column of data based on a custom list with more than 255 characters.

I have created a named range with 40 entries and then added data validation in the cells of column D using the above named range. However, I want to be able to sort column D in the same order as the named range but the custom data sort lists are restricted to 255 characters.

View 1 Replies View Related

Dropdown Menu With List And Custom Data Validation?

May 2, 2014

I have a Drop-Down Menu that looks like:

BOS
PIT
COL
NYR

using List with source =Lists!$B$2:$B$9, which is just the validation list

My Table:
Team Player
BOS Bergeron
BOS Krejci
NYR Nash
... ...

My goal is to limit the end user from choosing BOS more than two times from the drop down menu

View 4 Replies View Related

VBA To Custom Sort Data Based On Referenced List

Feb 17, 2014

Sort Example.xlsx

I have data in Sheet "Schedule" in range A8:C160 (including column headers), but note the length (rows) of the range is dynamic.

A8 = Customer
B8 = Load Time
C8 = Delivery Time

Columns B & C are formatted as h:mm:ss AM/PM, but Column B also includes text..."PRELOAD"

I would like a macro to sort the data based on column B, but the sort must follow a specific order, which is listed in Sheet "TimeSort", range A1:A50 (including header).

The custom order basically has "PRELOAD" sorting at the top, then sorting everything else chronologically starting at 6:00am.

The desired outcome would sort the range based on column B as follows...PRELOAD, 6:00:00 AM, 8:00:00 AM, 12:00:00 PM, 5:30:00 AM

Example is attached (desired sort is reflected).

View 14 Replies View Related

Excel 2010 :: Can't Delete Filtered / Visible List Rows From List Object

May 19, 2014

I have pulled a SharePoint list into my workbook. The list object (table) is still linked to the SharePoint list, as I'd like to synchronize it later on. I have filtered it with an autofilter. I'd like to delete all of the visible rows. I have tried a billion things to no avail. I have been searching Google for hours now. None of the examples work.

View 5 Replies View Related

Delete Value Of Dependent List Automatically When Mother-list Gets Manually Deleted?

Feb 8, 2013

I'm currently working with 3 dropdown lists depending on each other using data validation and =indirect(). I arranged them like in a top-down approach where you have a mother-list and child-lists (categories-->subCategories-->subCategoryCriteria). The user should easily chose first a category then a subCategory etc... This part works perfectly

My question is: when a user chose in the dropdown list e.g. a category, a subCategory and a subCategoryCriteria and he now maually deletes the value in the highest level, which is the Category (mother-list), all sub level values like subCategory and subCategoryCriteria will stay. It shouldn't be that way, because it's not logical. If you turn a tree upside-down (top-down approach) and cut one of the higher positioned trunks, all lower positioned trunks will fall off as well, right? So, my question is: can I use a conditional formatting or a formula to delete or fade out the value in the lower lists when the higher list value is being deleted?

I tried to use If-formula in combination with indirect in data validation, like =IF(A1="";("");INDIRECT(B1))...just and example.

The value doesnt need to be deleted, it could also be automatically face out like white on white or something. It's just confusing for the user if he deletes the highest choice and the lower choices still stay.

View 8 Replies View Related

Macro To Autofill With Custom List Based On Value Of Current Cell

May 25, 2012

I have an Excel file for work that has the following:

Column 1 is Agent
Column 2 is Interval (in 15 minute intervals from a report that I pulled)
Column 3 is Agent Calls (has a number anywhere from 1 - the highest currently is 19, which tells me how many calls that Agent had on that 15 minute interval.)

What I have currently is that if it says:

John Doe 15:00 5

It will automatically insert rows after the number based on a # -1, so it will add 4 empty rows after this row.

I have another step that will automatically copy Column 1 and Column 2 to the blank rows directly beneath them until it reaches a cell with data.

Now, what I need is a macro that will take Column C and where it says 5 automatically know to change that to '5a' then proceed to go down the list with 5b, 5c, 5d, & 5e. If it sees 4 it will know to change that to '4a' then proceed to go down the list with 4b, 4c, & 4d. I currently have numbers ranging from 2 - 19.

I have built custom list with this information so if I change all the numbers from 4 to 4a or 19 to 19a and double click on the black box it will autofill exactly like I want but it only goes down to the next cell with data, and I have 100+ lines per agent, and up to 21 agents a day, so this gets time consuming.

Here's how it looks when I'm ready to start the macro:

JohnDoe - Doe, John15:00 x
JohnDoe - Doe, John15:15 4a
JohnDoe - Doe, John15:15
JohnDoe - Doe, John15:15
JohnDoe - Doe, John15:15
JohnDoe - Doe, John15:45 4a

[Code] ......

And I need it to look like this:

JohnDoe - Doe, John15:00 x
JohnDoe - Doe, John15:15 4a
JohnDoe - Doe, John15:15 4b
JohnDoe - Doe, John15:15 4c
JohnDoe - Doe, John15:15 4d

[Code] ........

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved