What is best way to determine whether a value exists in a column of values?
What is the best way to lookup a value (i.e. a number)
to see whether or not it is in another column of values?
I need exact matches only
I cant get lookup() to work...
Strangely enough I have now discovered that the function =countif()
seems to work quite well!
...Help with mailing list!! If 3+ people @ 1 address, send 2, if 5+ s
So, I guess I am in need of help with an "IF THEN" statement here... I have
18k records on a sheet. I am trying to 'auto-evaluate' the database - I
don't want to send 10 catalogs to 1 address if there are 10 customers. I
just want to send 2 for up to 5 customers, 3 for 5+... I don't want to waste
that much paper (both capital, and physically, paper). I was hoping I could
simply filter down the listings, not from 10 to 1, but from 10 to 3, and from
5 to 2... Does this make sense? Any help is greatly appreciated...
...how do I copy several lines of word text into one excel cell?
In a word doc I need to copy several lines of text and paste them into one
excel cell. I can't seem to find any help telling me how to do this (each
line pastes into a different cell)
You can either double click the cell or press F2 key to get into edit mode
for the cell, then the text pasted would all go into the cell.
> In a word doc I need to copy several lines of text and paste them into one
> excel cell. I can't seem to find any help telling me how to do this (each
> line pastes into a different cell)
thank you SO much Simon CC - ...ScrollIntoView Non-Selected Cell ..?
How do i..
Without selecting it.. ScrollIntoView a Cell.. Say... R120C140
ActiveWindow.ScrollintoView .. but how to find the Document Coords in
Pixels of that Cell ..? when Rows/Columns are uneven sized ..?
Any Feedback would be Appreciated..
ActiveWindow.ScrollRow = 120
ActiveWindow.ScrollColumn = 140
This would put row 120, column 140 in the upper left corner of the screen.
"Andrew" <NoToSpam@ReadItYourSelf.com> wrote in message
> How do i..
>...Protecting of cells
Can someone help?
I have been doing a spreadsheet which has some pretty
complex formulas included in it. It will be used by
people who don't have a great understanding of excel and
all i want them to do is enter a value and allow the
program to make the calculations for it. If they delete
any formulas it may stuff the whole thing up.
Am I able to protect particular cells so that the
formulae and content cannot be altered?
Yes. Look up Protection in Help. First you lock the cells to be locked (that's the default), or
unlock the cells where they can ...Tab Jumps 8 Cells
When ever I click on Tab, it jumps over 8 cells. This
happpens on all worksheets even new ones.
Have you got Transition navigation keys checked in Tools | Options ?
"Brandon Cramer" <email@example.com> wrote in message
> When ever I click on Tab, it jumps over 8 cells. This
> happpens on all worksheets even new ones.
> Any ideas?
That worked! Thank you!!!!!!!
>Have y...Searching (Find) in Merged Cells
How can you search in merged cells for text content? The
Find Method works on single cells but I can't get it to
work on merged cells.
Excel treats merged cells as if they were the cell at the upper left of the
merged area. In VBA:
Dim myCell As Range
Set myCell = Cells.Find("find this", , xlValues, xlPart)
If Not myCell Is Nothing Then
MsgBox "Found in " & myCell.Address
MsgBox "Not Found"
In Excel, simply select all the cells using the select all button to the
upper left of A1, then use Ctrl F.
B...Display Color Hex Values in a DropDownList
How can I populate a DropDownList with the Available Color Hex Values
while displaying the Color? I'd like to allow my users a way to set a
The problem being exactly ? Is this to get the hex values or to display a
color for each item or to enumerate available colors ?
You also have ColorDialog
"Dave" <Dave.Burkett@Jacobs.com> a �crit dans le message de
...Setting Value Of One Cell Equal To Value Of Selected Cell
Without using VBA - If cell A1=red, A2=white and A3=blue, can I set C1
to be the value of the selected cell so that if A1 is selected, C1
will equal red, if A2 is selected, C1 will equal white, etc.?
Thanks, Sheldon Potolsky
In a word, no. No worksheet thing comes to mind that works as a function of the active cell.
You might want to use some radio buttons or a list box (Forms Toobar or Control Toolbox)
instead of cell selection. They'll let you click to make such choices, and you can use
formulas that will change a cell value as you wish.
Regards from Virginia Beach,
...Promt before changing a value
I was asked the below question by someone and was wondering if you
guys have any advise I can relay to him.
"I am looking to create something, most likely with VB code, in excel
that will pop up a dialog box to warn the user that they are changing
the quantity of a part by a more than normal amount. For example, if
the current inventory quantity for a specific insert is 10,000, then
if that cell is changed by more than 3000, a dialog box will appear to
prompt the user to double check their change. So, if that cell is
changed to 13,000 or higher, or, 7,000 or lower, the dialog box will
...Macro to remove contents of cell and move all other contents up one row
I've very lillte or no experience of macros and got some very usefu
help from a kind user of this forum a couple of days back and I'm bac
looking for more help. Its a bit long winded but here goes.
I need a macro which checks the contents of a couple of ranges o
cells, starting with the first range of cells lets say it checks cell
A4,B4,C4,D4 down to cells A20,B20,C20,D20. Now if for example cell
A7,B7,C7 and D7 have values (note the formulas used in these cell
means that if A7 has a value then B7,C7 and D7 will also have a valu
if A7 is blank then B7,C7, and D7 will be blank) and no...Excel Cell Format for Numberic Values
When I export data having 20 numeric characters, Excel will put in place a
scientifc equation. The numbers are rounded off after 15 characters. Cannot
get Excel to display all 20 numeric numbers. Any ideas on how to get Excel
to read all 20 numbers in a cell?
not possible. Excel only supports 15 significant digits
"Al" <Al@discussions.microsoft.com> schrieb im Newsbeitrag
> When I export data having 20 numeric characters, Excel will put in
> scientifc e...In excel can you select certain cells which contain the same text
If I have several cells with the same text in them can I filter these out and
select them. I know you can do this for formula etc but can it be done for
"ade" <firstname.lastname@example.org> wrote in message
> If I have several cells with the same text in them can I filter these out
> select them. I know you can do this for formula etc but can it be done for
...Restore pick list values
We edited the invoice 'status reason' picklist ( actualy deleted some values
) and modified others. This we have done on other picklists with no problems.
However we have noticed that when this attribute is displayed on selection
lists the text displayed is not the same as that on the picklist. We now
presume there is code behind particular list values and want to restore the
old values with the same list value. We have a backup of the DB available
through enterprise manager and can see the missing records there in
STRINGMAP and STRINGMAPBIT . We are missing values 4,5, 6 and a...reset the value of a spin button
Q: How can I reset the value of a spin button with out setting off the
I am using Office Pro 2003 and have tried the following with out
Application.EnableEvents = False
spnButton1.Value = VarX
Application.EnableEvents = True
Private fNoChange As Boolean
Private Sub spnButton1_Change()
If Not fNoChange Then
fNoChange = True
spnButton1.Value = VarX
fNoChange = False
"cubbybear3" <email@example.com> wrote ...CRM 1.2 upgrade to CRM 3.0 Worst case scenario
I am trying to upgrade CRM 1.2 to CRM 3.0 in SBS 2003 but encountered problems:
1. We're having a hard time to resolve the problems mainly because of the
CRM 1.2 being saved and in the CompanyWeb container/site.
2. The Default website is also being used by the Company's corporate website.
3. Can't set the Authentication to "Authentication,NTLM" even after following:
215383 How to configure IIS to support both Kerberos and NTLM authentication
in http://support.microsoft.com/default.aspx?scid=kb;en-us;215383. It just
doesn't seem to take effect even after IIS rese...Count values either vba or formula
Sample of what I am trying to achieve:
Worksheet1: (column A, B, C, D, E) - source data
id,primary region, secondary region, tertiary region, status
111,americas,new york, NA, increase
Worksheet2: (contains summary sheet) - summarize data
111,americas,NA, increase, 2 <----- count instances found from worksheet1
111,americas,LA...Splitting names from cells
My first question to the hordes of folks better equipped to answer than
the people around me. I'm a policy advisor and I only use Excel and
Access as they relate to research. I'm trying to figure out if there
is a method for splitting first and last names from a cell where both
are contained. We have a huge fleet database with thousands of
operator names, but when some moron first set up the system, they put
entire name (JOHN A DOE) in one cell. Is there a way I can search for
the string following the final space, or some such thing, so I can
separate the names?
Goe...In SUM cells the # sign keeps showing instead of total
AYE! my head...
i'm sure it is simple, but i cannot get the SUM cells to show the currency
amount. instead, the cells keep showing ####### after i enter the formula.
can anyone advise me in how to stop this from happening? what's more
annoying is that one SUM cell IS working properly. what have i done?
Widen the cell size
> AYE! my head...
> i'm sure it is simple, but i cannot get the SUM cells to show the currency
> amount. instead, the cells keep showing ####### after ...MS Dynamics CRM 4.0 KNOWN ISSUES / BUG
Take 2 at this post as I mashed the keyboard on my last attempt and
posted it half the way through! :)
Wondering if anyone knows any timeline on Update Rollup 1. I have a
few bugs in which when followed up with MS they have simply explained
that it is a known issue and they will be realeasing an Update Rollup
to address the bugs, mine included. They cannot provide a timeline
and I have clients crucifying me over it.
Some of the bugs have workarounds, but for sites with a lot of users
OR for our hosted clients which are generally remote users that are
geographically all over the p...Re: Changing a block of cells from relative to absolute references
To the kind person that posted VBA code to do this, my thanks. It appears that
this is in fact the easiest (read only) way to do this. I sent it to someone
that knows a bit more than me about such things (ie something :) ) and they
This is a very nice tidy piece of code and should work quite well... Except
missing a vital "End With" :-)
Aint it always the way. :)
For the sake of completeness and in the hope that it might prove useful for
others, here is the code that worked :
'**************************** START OF CODE ******************************
Opti...How do I find an unknown value on a line made from known data?
I'm trying to find an unknown data point (X) by using a set of standards (two
sets of data, an X and Y)that have been made into a line graph. Usually I do
this manually by graphing the line and printing out a large version and just
giving a best estimate to where the point would be. The closest thing I can
find that functions in this way is a trendline, but the point found needs to
actually be on the line itself, not the trendline. I was wondering if any of
you know how to do this using excel because I can't seem to figure it out.
It sounds as though you need to inte...Data cells ignored by formula
I am using the following formula =COUNTIF(M3:M13,">0") but it is
ignoring cells M7 to M9. I have the same formula and data type in the
columns on both sides and they are working fine. What is wrong?
Those cells may actually be text - try reformatting the cells as numbers, then reenter the data in
MS Excel MVP
<firstname.lastname@example.org> wrote in message news:email@example.com...
>I am using the following formula =COUNTIF(M3:M13,">0") but it is
> ignoring cells M7 to M9. I have the same...Contents of cell in footer
I want the contents of a specific cell to be in a footer, can I do this?
...Monthly quantities to Weekly
I have a linked query that shows projected quantities on the first day of
each month for that month. It also does not display any other dates except
the first date such as
I need to break these quantities into weekly buckets and then develop a
query that provides a rolling 13 week average query. I can create the query
to perform the 13 week rolling average, but I can't seem to create a query to
break the quantities into the weekly bucks from the monthly. Any Ideas?