VBA to insert .xlborder if cell value not equal to previous cell
I've got a worksheet and I'm wondering whether it is possible to
insert a line when a value in Column A, B, C & D does not equal the
values in the row above or below it.
I've currently got a formula in Column A that reads....
=IF(AND(B3=B2,C3=C2,D3=D2,E3=E2),"","IL") and a conditional format
that if the cell value is equal to "IL" then put a border. Wondering
if there is a better way to do this via VBA or is that the better way?
In your conditional formatting formula, instead of
(which I assume is what you've...delete drop-down box in cell
I have a problem removing a drop-down list box in a cell and have trie
using clear contents, delete cell, the delete button on the keyboard
Can someone please tell me how to remove this box from the cell.
Message posted from http://www.ExcelForum.com
might be one created by data / validation ...
click on the cell
choose data / validation
choose Clear All
"brain77 >" <<firstname.lastname@example.org> wrote in message
> I have a problem removing a drop-down list box in a cell and have tr...vlookup on cell below
Is it possible to do a VLOOKUP but instead of returning the value in row
that contains the lookup, it returns the value in the row below?
1 no x
2 yes y
3 ok z
=VLOOKUP("yes",A1:B3,2,FALSE) would return "z" instead of "y".
Th only way I can think of is to add a row header at the top and use the
MATCH function in column A to find the row position of "yes", then use
that in an HLOOKUP in column B. I was hoping there was a simpler way.
Not VLOOKUP, but INDEX & MATCH
I have Name, PO Box, street address, city, state, zip across a row in 6
I want to have this format in 1 cell:
City, State Zip
> I have Name, PO Box, street address, city, state, zip across a row in 6
> seperate cells/columns.
> I want to have this format in 1 cell:
> PO Box
> Street address
> City, State Zip
You do realize that this will screw up your ability to sort the data.
A better solution would be to describe what you want to do with the
data. You may not need it in the ...Formula'd cells charting as zero values
I have a very simple series, on which a line chart is based. The series
contains the following formula:
Therefore the cells in the range are blank if the source cell (row I) is
However, all cells with the above formula are being plotted as a zero
value in the line chart?
Can anyone advise how to prevent this happening while retaining the
formula? I want NO value to be plotted instead of a zero.
Use an error instead
will work, if the error annoys you from a design point...counting highlighted cells
not sure if you can do this but does anyone know if there is a function
of macro that i can use to count the number of cells that are
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/
Right-click on the status bar and select "count".
Gord Dibben XL2002
On Thu, 16 Oct 2003 22:46:54 -0400, Dragonstear
>not sure if you can do this but does anyone know if there is a function
>of macro t...Replacing cells with other cells
I have recently set up a time sheet for myself at work. At my last place you
could click on a button called 'Annual Leave' or 'Bank Holiday' and the cells
would be filled with a standard day. Does any one know how this is done? I
would like to add it to my time sheet if poss.
Sorry - I forgot to say that I'm currently using 2003.
> I have recently set up a time sheet for myself at work. At my last place you
> could click on a button called 'Annual Leave' or 'Bank Holiday' and the cells
>...Cell Error Message
I am running Excel 2002 and keep getting a comment like
deal in all cells that are not calculating to the end of
the range. For example if I have data in Col. a.b.c.d.e.f
but do not include Col. a, I get a comment type message
that the formula did not calculate the entire range(which
I do not want it to). I know that I can turn this off
manually (in each and every cell effected)(about 50),
however does anyone have an idea of how to turn it off
completely, either my use of a VBA or Excel itself.
Howl do set the cell size to inches
rather than no. of characters per font
You can't change the default measurement system for column width, but
you can use a simple macro to convert inches to characters, and adjust
cell sizes this way. This macro changes the worksheet to an array of
2-inch square cells.
Dim i As Integer
For i = 1 To 4
.Cells.RowHeight = 144
.Cells.ColumnWidth = _
.Cells(1, 1).ColumnWidth * 144 / .Cells(1, 1).Width
Jon Peltier, Microsof...Data in cells not visible
I have a customer with several custom spreadsheets that he's developed
over the years. A few months ago, I replaced his two computers and
upgraded Office to 2007. He began to notice that some of his data
wasn't showing up in some cells. However, when he clicked on it, the
data would still be visible in the formula bar. This occurs on both
computers and on different spreadsheets.
The cells that appear empty are random. If he scrolls the spreadsheet
so the blank cells are no longer visible, then scrolls back, the data
reappears. It will also come back if excel is minimized and restored,...Data bar conditional formatting based on another cell
I'm using Excel 2007 and have my setup as below:
Column A contains list of names
Column K contains percentage gains for the names in column A.
The percentages vary between -100% and +100%
I want to apply conditional formatting as below:
Have a 'data bar' in column A with GREEN color if=20
the corresponding value in K is positive.
Have a 'data bar' in column A with RED color if=20
the corresponding value in K is negative.
There is another sheet where column K contains numbers=20
instead of percentages. I would like to format column A=20
in the same way here as we...VLOOKUP reference
I have this in a Cell :
But I want to replace the "77" with the value in Cell BD2
of the current sheet.
RClay AT haswell DOT com
=VLOOKUP(AL14,INDIRECT("'[LELTACS.xls]Ali2'!$B$28:$B$" & BD2),1)
note: works only if leltacs.xls is open!
Robin Clay wrote:
> Greetings !
> I have this in a Cell :
> But I want to replace the "77" with the value in Cell BD2
> of the current s...substract cell F from cell H and total into cell I
i am new to using excel. i need to be able to put in pay price in cell F and
selling price into cell H and get total in cell i. so it would have to
substract cell F from cell H and put total into cell i. how do i do this?
Assuming your data is in Row 1, put this formula in I1 and press
Enter.........be sure and type it just as shown, as the leading equal sign
is what tells Excel that the following characters are a formula.
Vaya con Dios,
"vadarpug" <email@example.com> wrote in message
news:617768AC-D261-47AE-861A-E6BFD...How do I have a cell update itself only if it's currently blank?
I need to track to retain the first value of a date field. Each time
I load the worksheet the date field has the potential to change, I
need to keep track of the initial date in addition to the current date
that is in the field.
I have date columns called ASSIGNED and 1st ASSIGNED. The initial
value of both will be blank. At some point the ASSIGNED will be
populated with a date. When it is populated for the first time I want
to save this value in the 1st ASSIGNED column.
I need something like:
IF ( ISBLANK(ASSIGNED), "", ASSIGNED )
in my 1st Assigned cell, however, I do not ...cell reference problem
I have a spreadsheet that is linked to many supporting
spreadsheets and I send the master spreadsheet to the CEO
and continue to have him receive the spreadsheet and the
some of the cells that are linked have the ref# in the box
and need to be updated, how can I avoid this from
I need a better way to have the information entered on the
supporting spreadsheet automatically be entered into the
master spreadsheet with all company statistics. Any
ideas. The way is now works is by typing the =and then
double clicking the cell with the information, there must
be an easier met...Using Cell Contents for X Axis
I am using Excel 2003. I want to have the X Axis tic mark names to be taken
from the contents of a range of cells. I remember that in some prior version
I had the option to specify a range of cells as the source of an axis'
values. How can I do that in Excel 2003?
...separating single cell value numbers in different cells #2
512553.21 result 0 0 0 0 5 1 2 5 5 3 2 1
1512553.21 result 0 0 0 1 5 1
2 5 5 3 2 1
result of =MID(TEXT(A1,"0000000000.00"),1,1)
21254.32 result 2 1 2 5 4 3 2
1254.32 result 1 2 5 4 3 2
result of =MID(TEXT(A1,"0.00"),1,1)
The dost must not be shown.
The actual mean of this, is that i represent a single cell value in 1
actualy this is an ammount 1254 Dollars and 32 Cents. The "cent" cell
must always be the last two cells (A11; A12) i...If there is something in a cell
I have a excel sheet where some of the cell is filled with letters.
I want to have a cell that tells me how many cells have something in
How do I do this?
You could try
This will tell you how many cells are not empty
EdMac's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3073
View this thread: http://www.excelforum.com/showthread.php?threadid=52422
> I have a excel sheet where some of the cell is fill...editing cells
I am looking for help as follows: in a column of several thousand cells, how can I delete the space(s) preceding the text (or numeric) value in some cells. Doing it one by one is practically impossible. I tried manipulating both the concatenate and the text to column functions but they didn't work - unless I used them erroneously. Thank you.
You can use TRIM:
or download a macro from MVP David McRitchie's website:
>I am looking for help as follows: in a co...Can not see text in Excel when cell format is set to wrap text
When I select wrap text in format cell, or open an Excel file with wrap
texted, the text is not visible. Tried changing row height & column width,
but does not help.
One way to make text "invisible" is to set the text font color the same as
the background color.
Make sure this is not happening.
Make sure the text is not heavily indented
Make sure there are not an excessive number of hard returns at the beginning
of the text.
Gary''s Student - gsnu200725
> When I select wrap text in format cell, or open an Excel file with wrap
> ...formula to place real time in a cell when data entered in an adjacent cell
Still using 2003.
I have a spread sheet that records data in a column that is entered by
the user. I need a cell next to the entered data cell to reflect the
accurate date and time the data was entered.
Is there a formula I can enter into the adjacent cell that will
automatically place the instant date/time that will not update with
the next data entry?
Thanks in advance,
I'm not sure which column you are concerned with so I assumed Col. A is where
users enter data and Col. B is were you want the time stamp to go in, right.
This macro should be placed in t...add text to cell by clicking a choice in drop down list
I have made a drop down list in excel. When I click on an option I want the
text to be added to another cell, so you in that way can choose several
options from a dropdown list and have them all displayed in a cell just next
to the list.
I guess there is no way to do it :(
...Formatting Cell problem
Hello guys, I have one frustrating problem. I copied a table from Amsouth
Bank and pasted it into Excel. I then wanted to do some calculations. No
matter how i formatted the cell whether Number or Currency the number always
appeared on the left hand side and would not calculate. Number are supposed
to be aligned right. Any suggestions. This is frustrating!
- select an empty cell and copy this cell
- select your imported numbers
- goto 'Edit - Paste Special' and choose 'Add'
"Jacob" <Jacob@discussions.mic...absolute cell reference A spreadsheet cell reference that does no
Well help with what!?
Enter your question in the Body of the message instead of the Subject line.
Your question got cut off!!
Do you just want to use an absolute reference? If so enter $'s around your
$A$1 -or- $A1 -or- A$1
A $ preceding the column reference ensures the column letter doesn't change.
A $ preceding the row reference ensures the row number doesn't change.
Does that help?
...hide cell contents and make it reappear (Excel)
I need to hide the contents of a column of cells and make each corrisponding
cell contents reappear when another cell value changes from "current" to
=IF(<reference cell>="overdue",<your formula here>,"")
"Dave" <Dave@discussions.microsoft.com> wrote in message
> I need to hide the contents of a column of cells and make each
> cell contents reappear when another cell value changes from "current" to