if cell is text move left one column
ColB is a long list with sections names followed by category codes
I need to move the text into colA leaving colB with codes only (all numbers)
Dim lngRow As Long
For lngRow = 1 To Cells(Rows.Count, "B").End(xlUp).Row
If Not IsNumeric(Range("B" & lngRow)) Then
Range("A" & lngRow).Value = Range("B" & lngRow).Text
Range("B" & lngRow).Value = ""
...Formula to count the number of different values in a range
I'm looking for a formula that will give me the number of different values
in a range.
Example: Column A may have five cells that are "4", five cells that are
"7", five cells that are "9". Of the fifteen cells that contain data, there
are only 3 different values. I'd like to use a formula that will count the
number of different values in column A, in this case the result is "3".
=SUM(IF(A1:A15<>"",1/COUNTIF(A1:A...Nested If Functions #4
Below is what I wrote and I am missing a () I think.
What have I done wrong?
cefpe's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1357
View this thread: http://www.excelforum.com/showthread.php?threadid=26722
>Below is what I...How specify number format of cell value in concatenate function?
When combining text and cell values in a concatenate function, I would like
to control the number format in the concatenate cell: comma separator,
decimal place, etc.
="this is text " & text(a1,"$000,000.00") & " due on: " & text(b1,"mm/dd/yyyy")
> When combining text and cell values in a concatenate function, I would like
> to control the number format in the concatenate cell: comma separator,
> decimal place, etc.
...Multiple "IF" functions one one cell returned from different cells
OK, maybe that wasn't the best way to ask the question...
I need to create this scenario:
Question: "Lot Size?" Answered by "X" under "Small"(A3) "Medium"(A4) or
If (A3) = "X", then (J2)= 500
If (A4) = "X", then (J2) = 600
If (A5) = "X", then (J2) = 700
Obviously, these function will have to recide in the J2 box. I can get it to
work individually using the "=IF" function, but how can I insert multiple
"=IF" functions into the (J2) box?
I have tried using the "=OR"...Function for identifying the last entry in a table
Certainly there is some function or combination of functions that
solves for identifying the last entry in a column. Any help would be
appreciated. Example below.
row 1 aaa
row 2 bbb
row 3 ccc
row 4 ddd
I would like a function that would use the array A1:A6 to identify the
last entry, that being "ddd" which was found at A4.
(Make that 999 big enough to extend past the last possible row--but don't use
the whole column.)
> Certainly there i...xl 2007 how to validate that cell 1 invalid if cell 2 not blank
microsoft office 2007 xl - how do you validate a cell such that you cannot
enter a value in cell 1 if a value has been entered in cell 2?
...Count Only Weekday in the DateDiff Function
I have a report where the following function counts the interval of days
between two dates. This is one of the actual formulas being used:
Now, I need to adjust this syntex so that only the weekdays, workdays, are
counted. Please help!
Microsoft Access MVP
> I have a report where the following function counts the interval of days
> between two dates. This is one of the actual formulas being used:
&...highlight cell with colour if date overdue
can anyone tell me please how do i highlight a cell in a spreadsheet with
colour if date overdue
And how exactly should XL determine that the date was overdue?
Take a look at Conditional Formatting in XL Help.
If, for instance, the due date is in A1 and the date is overdue if it's
in the past, select A1, choose Format/Conditional Formatting... and use
In article <3B7B11E7-894B-458A-BDE6-7710E0743D31@microsoft.com>,
Harvey <Harvey@discussions.microsoft.com> wrote:
> can anyone ...How can i create in one cell scroll-down different options?
I am working in excel and I would like to create like a menu/scroll-down in
one cell with different options (like including kind of transport, train,
airplane, car, etc...) where you have an arrow on the right side and you can
choose one of the options given.
Thanks for your help.
You can use Data Validation to create dropdown lists. There are
instructions in Excel's help, and here:
> Hi there
> I am working in excel and I would like to create like a menu/scroll-down in
> one cell with different op...Redifining cell
I'd like to change the size of a cell independtly of another column o
row. Someone told me I need to redifine the cell to unlink other cell
in that range with this one. That "someone" is no longer around
Message posted from http://www.ExcelForum.com
AFAIK you can't change a single cell indepently from its row/column.
The only thing 'someone' could mean would be the merging of cells (goto
'Format - Cells - Alignment). But I would recommend not to use this
> I'd like to change the size ...Return a cell reference as a result of an IF formula
I am using an IF logical. If the result is true, I would like to return the
SUM of a range or cells. Or at the very least return cell reference. eg
=IF(K3<=DATE(2010,1,31),AND(K3>=DATE(2010,1,1))) Since this is a true
statement, I would like it to return the SUM of A1:A10
Because you're using the complete month, you have other choices, too:
=if(text(k3,"yyyymm")="201001",sum(a1:a10),"not in Jan 2010")
=if(and(month(k3)=1,year(k3)...How do I unlock specific cells in a spreadsheet that I protected?
You don't. First unlock , then protect.
HTH. Best wishes Harald
"onemanhattanrebel" <email@example.com> skrev i
...highlight range if cell contains desired data
If any cell in column D:D contains desired data in string format ( i.e.
"Bill G" ), how would I highlight the data range in that respective row?
cell d4 contains the text "Bill G". Excel automatically colors the range
4a:4i in red.
1. Select columns A to I.
2. Go to Format > Conditional Formatting
3. Select "Formula Is" on the drop-down arrow, and put:
4. Press the Format radio button and format as desired.
>If any cell in column D:D contains desired data in
...inserting data from a row to a cell, when the row number is specified by a formula in a cell
in cell H3, I have a match formula that searches my spreadsheet for a
string of information. when it finds the row with the matching string
it displays the row number in cell H3 example '63'. In cell C3, I want
to display the full text that displays in the row specified in cell H3
'63'. I can do that by simply entering =r63 in the cell C3. I want
the info to display, what I would like to do is to automate this so I
do not manually have to enter =R63,for each cell. example of my
in Cell H3 the formula is =MATCH(I2,(Q$1:Q$30001),0) the result is 63
meaning row 63....If the cell is not empty, then...
I have a many rows of 12 cells where it is possible to write a number/
text in the cells.
I would like to be able to calculate how many of these cells, in one
row, that are different from empty,
and then multiplie this with the value 8,3
Like if 7 cells in one row are different from empty, then =SUM(7x8,3)
how can I do this ?
"SpookiePower" <firstname.lastname@example.org> wrote in message
>I have a many rows of 12 cells where it is possible to write a number/
> text...short cut way to go to specific but unknown cells
Is there a short cut way to go to the
1) first row first column
2) last row, last column
3) last row, first column
4) first row last column
1 - ctrl/home
2 - ctrl/end
3 - ctrl/end, then home (not ctrl/home)
4 - ctrl/end, then right arrow, ctrl/up arrow, left arrow
"Jerome Ranch" <email@example.com> wrote in message
> Is there a short cut way to go to the
> 1) first row first column
> 2) last row, last column
> 3) last row, first column
> 4) first row last column
> je...if value not found in table ?
I need to display a msgbox if a value is not found in a table.
If value not_in table.field then
I know that code wont work is just an ilustration of what i am looking
Thanks in advance
Use DLookup() to see if the value is in the table.
If it's not found, the result will be Null.
So, use IsNull() to test the result.
Here's how to get your Dlookup() expression working:
Getting a value from a table: DLookup()
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access us...Excel "IF,THEN, INSERT ROW"
IS it possible to scan a list and if criteria is met
insert a row that contains a simple summation?
Ex: Our company runs a manhour report and once converted
to excel it will not give daily totals of manhours just a
long list. There will be numerous individuals for one day
and then it starts over for the next day but they are not
seperated. Can I Search the date and insert
the "summation row" at the end of the day.
Manually this takes for ever.
If you sort them by date you could use Data / Subtotals instead. Another
option is to use a pivot...nested if functions?
I am producing a cashflow. It is over five years, I have established that
average contract values for 2006, 2007, 2008 2009 and 2010 will be 800k,
1200k, 2000k, 2500k and 3000k respectively. I want the cell with the contract
value to change automatically if a project start date is entered, eg if a
project starts in March 2007 its value will be 1200k.
Can anyone help me?
Here are some ideas:
come back with specific question
Bernard V Liengme
re...If match copy cells in visual basic.
How do I write the visual basic code for the following.
If the value in the cells in column A (sheet 1) matches the value in column
A (sheet 2), copy contents of cells N, O & P, columns in the same row, to
sheet 2 in A,B & C.
Many thanks, in anticipation.
I didn't quite follow your logic
A (sheet 2), copy contents of cells N, O & P, columns in the same row, to
> sheet 2 in A,B & C.
This would overwrite the value in column A which I assume you don't want to
do so instead this writes to columns B,C & D in sheet 2.
This should work as worksheet code or...Colouring cell if it has a specific date
I have a column for each month where the dates are entered on rows. Is
it possible I can color a cell if the date entered in a cell for that
month is 15 days past the end of the month. Like, if in a January
column I enter a date on one of the rows as Feb.20, then the cell
fills with red colour.
I tried doing it using conditional formatting, but don't know how to
say 15 days past end of the month?
Any help will be appreciated! Thanks very much!
What version of Excel are you using?
Microsoft Excel MVP
"Shivam.Shah" <firstname.lastname@example.org> wrot...Finding specific values within a string of numbers and/or letters that is always different
I wish to find specific information within the following string of
numbers that is always different. Here is an example of what I am
Where to look:
What I wish to find:
If there is an occurrence of "OH" then send to a new tab titled "OH"
Also just for fun, if there is an occurrence of T4 send to new tab
and if there is an occurrence of YI send to new tab titled "YI"
Look at Find and Search in help, that should get you going.
Hi, This is URGENT.
Problem: In MS Excel, Column A contains values from A to Z and Column
contains value from 1 to 5. Now, I need to count the records that hav
"A" has value in Column A and "3" in Column B. I had tried with COUNTI
function but it works only for one criteria, i.e. it can be used if
want to count the records with value "A" in Column A or the record
with "5 in Column B. Is there any function that can be used for thi
Pls treat this very urgent
~~ Message posted from http://www.ExcelTip...using `if` a value is between
I am using zz as my variable
what I want to do is
if zz is between 7 and 10 then print out a range within a sheet
is the print range line correct ????????
any assistance appreciated
If ZZ = >1 or < =7 Then ' check value of zz
Range("A2:G36").PrintOut.SelectedSheets ' Print selected range
If ZZ > 8 or < 14Then
You will need to use AND and get the operators in the correct syntax:
If zz >=1 And zz <= 7 Then
which means if zz is between 1 and 7 (inclusive) then do something ...