Matching Data in Excel to Closest Grouping
Let's say I have data in column A and categories in columns B, C, D.
want to take the data I have in column A and assign it one of th
values in the three following columns.
So, for instance, A1 = 10,000 B1 = 20,000 C1 = 12,000 and D1 = 6,000
I'd like E1 to have a formula that took the value in A1 (10,000) an
determined to which of B1, C1 and D1 it was closest and give me tha
value (in this case it should give me 12,000).
Is this doable?
Message posted from http://www.ExcelForum.com
I believe Frank Kabel or some of the others will have
a much more elequent soluti...Remove the word 'total' from subtotals
I am doing a subtotal using hte sum function
After excel subtotals it inserts the word 'total' in the same line as
the field being subtotal
How can I remove total? I need to take the subtotaled info and upload
it into another program and it will not work with the word totoal
Have you tried F2 and edit the cell contents to remove what you don't want?
Gord Dibben Excel MVP
On Sat, 12 Feb 2005 19:40:02 -0500, yahoo <email@example.com> wrote:
>I am doing a subtotal using hte sum function
>After excel subtotals it inserts the word '...Maro warning banner, despite no macros present?
I have a couple of PP presentations that I made to use for work. I will call
them PP1 and PP2. With PP1, the macro warning banner appears in the .ppt and
..pps versions of this file, despite the fact that there are no macros. I have
gone to the .ppt version of PP1 and removed the macros under tools and VBA
editor, and even lowered the security settings, yet I still get the warning
I have another PP presentation that works great, PP2, however it takes about
15 seconds to open in the .pps version. I thought that perhaps the screen
shots I put in PP2 made for a large fil...Displaying a new screen when choosing from a dropdowm box
I wanted to know if i can change the screen (having new fields and
attribute) when i choose an item form a drop down box
is it possible?
...Macro to delete current row AND delete objects also located in that row.
Is there a way to, when a macro deletes the current row, to also have
it find and delete any drawn objects [circles, rectangles, etc]. when
my macro deletes a row, a remnant of the drawn object, which appears
as a line, remains.
I am using XL 2002.
The drawing object is not part of the row so won't be deleted with the
Set its properties to "move but not size with cells" and it won't shrink up
to a line when you delete rows.
Gord Dibben MS Excel MVP
On Mon, 26 Jan 2009 13:41:49 -0800 (PST), Tonso <firstname.lastname@example.org>
>Is there a...Prevent removal of fields from pivot table or chart
Is it possible to prevent the user from moving or removing fields from a
pivot table or chart. I have one user that likes to move thing around and he
always seems to make things disappear.
I would like to allow the filtering but not allow the moving of the dropdown
...Removing active locks
Is there any way to remove an active lock from a record in a table in
Its not advisable to remove manually. If you want to overide dex lock use
store proc or pass through sql. Check master database for Dex Lock table and
Dex Session table. Also you can go thru dex help about active lock.
"Greg M" wrote:
> Is there any way to remove an active lock from a record in a table in
...Contacts in GAL using Cached Exchange Mode
I am using Outlook 2003 in cached exchange mode connecting to Exchange
2000. I have an issue when doing lookups for addresses in the GAL.
Before I changed to cached exchange mode, I used to type the first part
of someone's email address (ie cameron.mc) and press Crtl-K which would
look up the contact from the GAL. It would also work by entering the
contact name (ie cameron mcshane) and clicking Ctrl-K. However, once I
chanegd to cached mode this no longer works. I now must either type the
full address, or the contact as it appears in the GAL ('McShane,
Cameron' or ...Mailmig.exe command line
Mailmig from Exchange 5.5 to 2003
In mailmig version of ex2000 when using command line
and text file for users who should be migrated - and
this file is empty - no users will be migrated.
In mailmig.exe version in exchange 2003 the same scenario
will migrate ALL the users from source ex 5.5 server !!!!
I tried to find som documentation on mailmig (command line)
part for ex2003 - but could not find any.
Some contol file parameters has obviously been removed from
ex2003 version (error messages, NTaccounts etc.)
Thank you for requesting support for Exchange Server 200...How can I lock worksheets while macros can still enter into them?
I want to lock worksheets for outside users. But when I do it and a macro
needs to go into the worksheet, the system gives an error. So is there a way
to lock worksheets while still allowing macros to enter into the sheets and
enginguven, you can protect the sheet with user interface only, like this
ActiveSheet.Protect UserInterfaceOnly:=True, password:="123"
Sheets("Sheet1").Protect UserInterfaceOnly:=True, Password:="123"
or use something like this
Const PW As String = "123" 'Change Password Here
ActiveSheet.Unprotect P...Link Outlook 2003 to Project pro 2003 without the server
Hi all, Is there away of linking my projects to my outlook calendar and task
list with out having to get the server. I am a only user and really dont
need a server just for this. All I want to do is take the projects I am
working on and have them in my task list and on my outlook calendar so I
dont have to keep inputting stuff twice. I want more then just exporting my
task list to project. I want when I complete a task on the project it is
also completed in outlook or the other way around. I dont see why this is
not posible. I should be able to create a link between the two for a task. ...Txt box value from query field
FormA uses TableA. TableA has FieldA which is a very non-descript code.
TableB is related to TableA on that field (FieldA) and has all the
descriptive info behind the non-descript code.
I created a query to retrieve from TableB the descriptive info based on the
value of FieldA on the form.
When I run the query, it behaves correctly. I get the 1 record from TableB
that I am expecting.
When I try to populate a text field on FormA from one of the fields from the
query, I get #Name.
Seems simple enough. But, alas, not working.
On Tue, 19 Feb 2008 09:59:01 -0800, So...changing columns in the select names dialog box
Outlook 2003 using Microsoft Exchange Server.
1) Is it possible to change which columns display and in what order in the
"select columns" dialog box?
2) I have found that I can remove a column by dragging and sliding a column
to the left against the next column on the left. Unfortunately I have
removed some columns that I would like to see. How do I get the columns to
2) That's a tricky one! It took a couple of minutes for me to figure =
out. Position your mouse pointer exactly over the divider on the left =
side of the colu...Problem with long filename in Win 98 while reading the filename from command line using function ParseCommandLine()
I am developing an MDI application using MFC. I am facing a problem
related to long file name in Windows 98. When I drags a file with
longer name of 8 characters (say abcdefghijk.txt), on the icon of my
application on the desktop. My application opens the file with modified
filename, eg. abcdef~2,txt.
This problem does not exist on Win2K.
Is their any solution to this problem ?
This is not really a problem. MS-DOS does not know if the target can handle l...macro needs info on currently highlighted message in search folder
I created a search folder, "Messages Sent Or Received Prior To 2008)
by using the Advanced Search and creating a query. The list of
messages is shown, including just the lowest level folder name.
I can navigate through the messages, and I'd like write a macro to
show the full folder path of the currently selected (not opened)
message, because I have many dozens of folders and they're not all
named uniquely at the lowest level.
Can this be done?
Show it where? Outlook version?
Sue Mosher, Outlook MVP
Author of Microsoft Outlook 2007 Program...inserting a goal line in a chart
Is there a way to work a line for a performance goal into a chart in excel,
or do you just have to draw it as a separate object?
Jon Peltier has instructions for adding a horizontal line to a chart:
> Is there a way to work a line for a performance goal into a chart in excel,
> or do you just have to draw it as a separate object?
Excel FAQ, Tips & Book List
...Remove active directory
I want to remove Active Directory from my exchange 2003 server. Is it
supported? will I lose functionality in Exchange?
what exactly do you mean? you cannot run Exchange 2003 without active
directory...what is it you wish to remove?
"Cristina Boero S." <CristinaBoeroS@discussions.microsoft.com> wrote in
> Hello everyone!!
> I want to remove Active Directory from my exchange 2003 server. Is it
> supported? will I lose functionality in Exchange?
> T...Save As using contents of Cell in Name
Can someone help with code that changes the "save As" file name to
combination of the original File Name + the contents of a cell, an
saves file in same folder as the original.
E.G. Original File Name= Timesheet
Cells B1= Location
Cell C1 = Date in format 3-5-2004
Cell A1 = concatenate(B1,C1)
Fle Name will therefore be "Timesheet Location 3-5-2004"
The other problem is with the date. When concatenating, how do I forma
the date to avoid it looking like "Location 38051"
Any help much appreciated
Message posted from http://www.ExcelForum.com
for c...Using IsError with Match
I have a list box from which a user may select a client. My code then looks
through a list of employees to find the ones belonging to that client. For
various reasons, it is possible to select a client that might not be
represented in the list of employees (it's necessary, I assure you). In case
the user selects a client that isn't in the employee list, I wanted to catch
this and avoid an error message. The code that is supposed to do this is
If IsError(Application.WorksheetFunction.Match(ClientNum, ClientEEs, 0)) =
' Do stuff
Howev...count cells using multiple criteria
Can anyone show me how to count the number of rows containing two or more
criteria? For example, if one column has the text "DO" and another column has
the text "RD11" how do I count only those rows that contain both DO and RD11?
In article <0F41177E-45A0-4B0B-AE33-6BCCF2F3B8D1@microsoft.com>,
Alex68 <Alex68@discussions.microsoft.com> wrote:
> Can anyone show me how to count the number of rows containing two or more
> criteria? For example, if one column has the te...Can different editions of Publisher be used together?
If my son uses Publisher 2003 on a school computer, downloads his work to a
flash drive, brings it home to edit on Publisher 2007, will he then be able
to take the edited work back to the school computer to be used, or will it
not be compatible?
> If my son uses Publisher 2003 on a school computer, downloads his work to a
> flash drive, brings it home to edit on Publisher 2007, will he then be able
> to take the edited work back to the school computer to be used, or will it
> not be compatible?
Publisher 2003 and 2007 share a common file format, so the sc...Anyone use Promys CRM software integrated with GP Dynamics?
Our company is considering using Promys as a CRM to create quotes and
sales orders for the sales team. I am concerned about the integration
with GP and what the pitfalls may be. Is anyone here currently using
Promys with GP Dynamics?
...Someone's using my domain name to send e-mails
I came into work this morning and I received an e-mail from myself. An
e-mail I never sent. From: Annsmith@mydomain.com To: Annsmith@mydomain.com
How is this possible?. The message was for weight loss pills. I look after
the computers in the office so I have to find an answer for this problem.
Also, last week, an employee received and e-mail from someone using our
domain name. email@example.com. Right away I knew it was fake because we
don't use that kind of naming convention here at the office and I'm the only
one who sets up the e-mail addresses.
We use Outlook 2000 and excha...Macro to edit cell
I would like to write a macro that edits the first 6
digits of a cell but leaving the last 6 digits as is.
for each c in selection
"Kevin" <firstname.lastname@example.org> wrote in message
> I would like to write a macro that edits the first 6
> digits of a cell but leaving the last 6 digits as is.
...how to match between two list using letters
I have two sheets as follows:
D-173 abcd ?
B-241 efgh ?
TI-242 XYZ ?
E-0124 STQ ?
What I want to do is taking the value from Colum A in sheet1 and put it in
column C in sheet 2 using Sheet1 Column B as an indicator.
Please note that the Column A in sheet2 has the same data of Column B in
sheet1 but there are extra letters and characters.
In C1 of sheet2 and copy dow...