Use of '=AND( COUNTIF(A:A, B1))' to check if a cell is in a column or range
Can someone please help, I'm using Excel 2003 and I wondered if this is
an efficient way of checking whether an item is is a column or part of
a column of (length 5000 rows) or not: -
=AND( COUNTIF(A:A, B1) )
It seems like an odd use of the AND function as it only has one
parameter but it returns a TRUE or FALSE as you would expect.
I don't want to count the number of occurences, I just want to know if
the value is present in the column but I can't seem to find an
alternative to COUNTIF.
The IF and FIND commands don't seem to apply here.
Thanks for any help given.
H...Find a vlaue, shade another cell
I would like to search column c in an excel sheet for any number that starts
with '46'. all the numbers in the column will be 7 digits long (eg. 4634567)
once found I would like to turn the font blue in the cell 10 columns to the
right of this 46 cell.
the macro should loop down column c until it can find no further values.
any ideas if this is possible?
lastRow = Cells(Cells.Rows.Count, "C").End(xlUp).Row
Set MyRange = Range("C1:C" & lastRow)
For Each c In MyRange
If Left(c.Value, 2) = "46" Then
...Select item from dropdown list, item code is displayed in cell
I need help with a spreadsheet problem.
I created a timesheet for users to track their hours with an in-cell
drop down for some of the values.
Currently, an user is able to use the in-cell dropdown to select a
time off code and the result is displayed in the cell. However, I
would like to have the user to see the full-name on the in-cell
dropdown, but have the abbreviated code display in the cell when
I have been unable to figure out how to do this, so any help would be
I am trying to stay away from extra Excel components or macros, as I
like to keep this sheet easy for the e...All Cells in Excel are locked, what's wrong?
I have a huge problem! All the cells in Excel is locked. I cannot edit the
cells at all. And this is with fresh started sheets as well. I can't change
I could yesterday, until some point - where it just didn't work any more.
What can I do? I really need help...
Check under <tools>, does it say protect or unprotect sheet
> I have a huge problem! All the cells in Excel is locked. I cannot edit the
> cells at all. And this is with fresh started sheets as well. I can't change
> I could yesterday, until som...Execl2002: Editing via F2, cell in light blue, Enter inserts new line
suddenly when I edit a cell via F2, the cell is highlighted in a color
like light blue. When I press Enter a new line is inserted, it's not
possible to end editing with Enter. Any hint?
Thanks and greetings
Do you see EXT on the bottom bar of Excel?
If so, you are in Extended mode. Press F8 to clear.
"Udo" <WeikEngOff@aol.com> wrote in message
> suddenly when I edit a cell via F2, the cell is highlighted in a color
> like light blue. When I p...Subtotal
OK, so I have a list of orders.
Some orders have just one row of data, some have two rows of data,
some have three, some have four, etc.
Each row represents either a delay or an activity. (So, obviously, an
order can have more than one of either.)
I need to aggregate into one row:
Order | Type of Order | Num. of Delays | Total Length of Delays
HERE'S THE MANUAL WAY I'M DOING THIS NOW:
My initial columns are:
Order Number (A) | Type of Order (B) | Action Code (C) | Action Length
I added a "marker column" - Dela...Counting unique items, disregarding thier annexs
"THIS IS A rePOST WITH SOME CORRECTION"
seeking help please.
The column A1:A100 contains items and also empty cells like
facil.1,beauty.2,facil.,facil.3,beauty.4 and so on....
Looking for a solution where on counting items will yield 2 only (for
and "beauty") and not 5 as each entry is unique, as the flowing formula do:
many thanks for any help.
On Wed, 15 Sep 2004 03:23:03 -0700, "excelFan"
<excelFan@discus...Count, Sum, Index,Match or other formula needed
Using Excel 2003:
One row is a set of numbers. I need to match that to another worksheet and
find certain results in it. Here's the trick. This second worksheet will
have these numbers over and over again with either Yes or No in the column I
want returned. Example, number in cell one is shown 10 times in the second
worksheet sometimes with yes and sometimes with no in the column I want back.
The result need to find the number in the 1st worksheet and return a No if
any of the matching in the 2nd worksheet contain a No in the column I want
returned even if sometimes...Updating linked cells within a workbook, from worksheet to workshe
(Excel 2002) I have a 5G Excel workbook with around 40 worksheets in it.
Many of the sheets contain cells that refer to values in other sheets. I
recently found that some of the values were incorrect (I believe they were
just not updated). When I double clicked on the cell, gained access to the
formula and then hit enter, the cell updated. I'm wondering 2 things -
1) Under what circumstances will cells NOT automatically update? I thought
they always updated automatically as long as you're working within the same
2) Is there a way that I can update all cells at o...How do I convert last name, first name in cells to first name las.
Cells contain names in last name, first name order. I wish to convert to
first name last name (no comma) without retyping. Thanks
Assuming your data is in column A. Select the names in Column A. Make sure
that Columns B and C are blank. In Excel 2003, click on data,
text-to-columns and choose delimited and comma. The last name of the first
entry will be in A2 and the first name will be in B2. In C2 type =B2&" "&A2
to concatenate the name with first name first and then copy down the formula
in C2 as far as necessary. As a last step be sure to copy Column C, go ...how do i link cells so that when typing in an item, the price app.
I'm trying to create a quotation template which enables me to enter in an
item in one cell and its corresponding price appear in the next cell? Is this
I have created a spreadsheet of Items and prices (of which there are going
to be over 1000) as a refernce point but am unsure how to link these for
automatic entry into the quotation.
Anyone know how to do this?
You need to build a table of the items and prices. For
example, on sheet2 in column A you list the items and in
column B you enter the corresponding price. Assume that
table...How to get notified when user inserts cells, rows, or columns in W
I'm making a client to Excel that keeps references to cells on worksheets.
These references must be updated if the user inserts cells to the left or
above the referenced cells. I have not been able to find this information,
neither in the Range that accompanies the Change notification nor in any of
the Worksheet, Workbook, or Application properties. Can anybody help me?
Using the Worksheet_Change event, you could probably evaluate the row and
column values of the target cell against the criteria cell:
If Target <> (Reference Cell) Then
If Target.Row <=( Refere...Formula works in some cells, doesn't in other
I have a multi-sheet 2003 workbook.
Sheet 1 is a summary that displays data from the other 4 sheets, the name of
one of which is "Northeast" (though experimentation proves that it doesn't
matter what the sheet is named)
On my summary sheet this particular sheet is referenced in row 4. Column A
is nothing but the text "Northeast", the formulas in each of the next
columns are as follows (without the B: C: D: E: and F:)
F: =AVERAGE(IF(Northeast...no cells, grey screen, all tabs greyed out
I just opened my spreadsheet (xlsx file), no data or cells are present and
all the tabs are greyed out. The screen (where cells/data s/b present) is
just a grey blank screen. On the very top where the file name s/b in the
border, it just says Microsoft Excel. What has happened? Can I recover my
You need to open a workbook file (the Office icon is not greyed out) or
start a new (use shortcut CTRL+N)
Microsoft Excel MVP
"John" <John@discussions.microsoft.com> wrote in message
news:0FDC...Reverse Text in Cell before and after comma (not just Word1, Word2
I've seen some workarounds for doing
But I am looking for
Word1 MaybeWord2-3, WordA MaybeWordB-C
i.e. just put all the words after the comma in front and vice versa e.g.
Assistant Manager, Building Services=Building Services Assistant Manager
Manager, Building Services = Building Services Manager
Assistant Manager, Building = Building Assistant Manager
Is there an 'easy' way to do this with cell functions vs VBA?
Thanks in advance!
I sometimes break this into smaller pieces.
Say your data is in column A (A1:A3)
I'd...min,average>0 if 52 cells read zero
I have 52 cells w265 that all read zero until data is entered this means one
cell in each worksheet example Week 1 w265, week 2 w265, week 3 w265 to week
52 w265. on a seperate work sheet im trying to caculate min & average for
efficiency but when data is entered for min i get zero because of other cells
that read zero or the average is incorrect too low I have to have zero,s in
place on the 52 worksheets but i don,t want io include them when calculating
min or average on seperate work sheet can some please help
The easiest thing to do is to completely empty all you...Cell reference, dragging across
My question is pretty simple, and if anyone knows the
answer I would appreciate a response.
If my cell reference is =b7/c8, and I want to drag the
reference cell across and change the cell number but keep
the letter constant, is that possible?
...Change Number to Text , Case error when cell is String and format is General
How to check the cell is text or number ?
When Cell is string, Case Excel close and recoveing.
Dim iRows As Long
Dim iCols As Long
Dim ir, ic As Long
Dim iNum As Long
Dim strVal As String
If Application.Selection Is Nothing Then
MsgBox "No Open Worksheet", vbCritical
iRows = Application.Selection.Rows.Count
iCols = Application.Selection.Columns.Count
For ic = 1 To iCols
For ir = 1 To iRows
With Application.Selection.Item(ir, ic)
If .NumberFormatLocal = "Gene...HLookup, multiple tables and range names in other cells
I am beginning to discover Excel with the aid of various textbooks but
I am stumped on one problem and don't wish to embark on VBA custom
functions unless I have to.
I am intending to use HLookup with multiple tables (over 15) in the
same worksheet. So here is my problem,
The 'table_array' part can be a range or the name of a range, if I
have a cell that is calculated to produce the name of a range eg. an
INDEX and MATCH formula, how can I get 'table_array' to accept this
eg.=HLOOKUP(A...how to print defined cell names with comments, not numbers?
My overall task is to print comments at the end of the sheet that display the
defined cell name instead of the cell number.
Problems: How do I define all of the intermediary cells based on the row and
column headings, and then how do I print the comments to display the defined
name, instead of the number.
For example, I would like my print out to display:
Cell: "Defined Name"
...When I filter data, sometimes I do not get row count retrived
When you are using auto filter on a worksheet and then you pick a valu
to filter, you get the list of rows that satisfy that criteria. Also
in the status bar, you get the number of rows retrived as a result o
this filter. Example you will get "3 of 25 rows found" or somethin
like that. Sometimes I do not get that message, I just get a messag
called filter mode. Why does this happen and how can I correct this?
Message posted from http://www.ExcelForum.com
If your worksheet has any formulae on it they automatically calculat
after filtering thereby suppressing the messa...AutoSave without editing, or calculating cells without change
I am pulling data from another place which gets updated on save, however not
just save, something on the sheet appears to need to be updated. I have
written a Module which saves the sheet on a pre defined timed interval. The
procedure is working (without editing the sheet). But my data will not
update unless I manually change a cell anywhere on the sheet, then at the
next timed interval of Save, all my tags are updated. How do I Change a cell
(from within my code) each pass through the timer. I beleive if the cell
automatically updated right before the save command, then my data ho...Cells(#,#).Value conversion
Hey I want to grab a serialized date and turn it into an integer in my
what is the proper conversion for this ?
Dim startDate As Integer
Dim endDate As Integer
startDate = ActiveRow.Cells(1, 2).Value
endDate = ActiveRow.Cells(1, 3).Value
In addition to Bob's suggestion, change:
> startDate = ActiveRow.Cells(1, 2).Value
> endDate = ActiveRow.Cells(1, 3).Value
startDate = Cells(1, 2).Value
endDate = Cells(1, 3).Value
"Alexandre Brisebois (www.pointnetsolutions.com)"
<email@example.com>...different formatting, same cell
Say the date that a certain event occurs (2/17/07) is in cell A1.
I would like to have cell B1 display: Completed 2/17/07
If I use the formula ="Completed "&A1, it displays: Completed 39130
Is there another way to do this?
To Excel, dates are just numbers, so you need to tell it to convert the
number (date) to text how to display that text.
Try something like this:
Does that help?
> Say the date that a certain event occurs (2/17/07) is in cell A1....Specifying source campaign in Opp., not counting as campaign respo
General usage question regarding campaigns. If we create a campaign, then
specify that campaign as the source campaign for an opportunity, that
opportunity/account is not then listed as a response to that campaign. So
when we run a Campaign Performance report, no responses are listed.
Do you have to use the normal method of entering a response within the
campaign? I'd think that if you can associate a campaign within the
opportunity, that linkage would reflect in a Campaign Performance report.