Format Cells to Capital Letters
Can you format a cell so that regardless of how you type the letters into the cell it will format it to capitals?
Cannot do this via formatting.
You can enforce CAPS by using Data Validation, but this would just make the
user have to re-key.
Either make sure Caps Lock is on or use event code in the worksheet.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Target.Cells = Range("A1") Then Exit Sub
'for a column use this next line
' If Target.Column <> 1 Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
...Merge 2 tables with conditions
I have 2 tables:
Both tables are related by "ID" field, but both tables have not the
same number of elements
I want to obtain a new table with this structure:
As I said before both tables have not the same number of elements
(rows) so in my result table, "Status" field should be empty for those
"ID"'s not found in "Status" table.
Users: 1 2 3
...Default Cell format of existing Worksheets got changed automatical
I am using MS Excel 2007. Most of my excel files saved with default cell
format as General while creation; currently for the last two days when i open
those files the cell content format is showing as currency format.
Pls let me how to fix the same
If you reference a cell that has a format (other then General), the
reference cell will adopt the same format.
"NGBalaji" <NGBalaji@discussions.microsoft.com> wrote in message
>I am using MS Excel 2007. Most o...How can I merge and purge two excel work sheets?
...Wrap Text for Merged Cells
Can somebody post some good VBA macro for wrapping text in case of
merged cells (merged rows as well as merged colums or both merged rows
Lot has been posted in this regard till now but i could'nt find any
perfect solution to this.
If you've seen lots of code that has been posted, yet you don't say what
makes that code "not perfect", how do you expect anyone to respond to
your post? Should responders just guess what "perfect" means to you?
Try posting back with more information - what you've tried, why it
didn...Using Popups to select a range of cells.
I have a small request for help with code.
I need a macro to have popups request the first and last cells in a
range , and then to select them.
Can someone help?
Dim Rng As Range
On Error Resume Next
Set Rng = Application.InputBox("Select your range", _
On Error GoTo 0
If Rng Is Nothing Then Exit Sub
HTH. Best wishes Harald
"Colin Hayes" <Colin@chayes.demon.co.uk> wrote in message
> Hi All
>...Cell Number Format includeing other Cell Value
I needed to create some conditional number format - to say so...
Cell A1 content is "m2"
as I Enter in cell A2: "10" -> the output to cell A2 should be "10
Cell A1: "Dollars" -> A2: 200 -> shown "200 Dollars"...
and so on....
Is that possible in Excel? (don't know VBA - yet :DD)
Any help is appreciated!
How about just use a third cell:
=a2 & " " & a1
> I needed to create some conditional number format - to say so...
> M...combine cells #3
Hello, say you have Mindy in A1, Thomas in B1, how can you combine those in
C1 with a space between the names?
Mindy, here is one way,
=A1& " " & B1
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
"Mindy" <Mindy@discussions.microsoft.com> wrote in message
> Hello, say you have Mindy in A1, Thomas in B1, how can you combine those
> i...Auto fill keeps adding 1 digit or 1 cell
When I try to drag a formula or value down or across the
spreadsheet in Excel 2002 (Win XP Professional)it
automatically adds 1 didgit to the value or references
the cell next door to calculate the formula. How can I
make the drag and autofill more accurate?
Try draggin with the right mouse button. When you get to the end of your
range you get a list of fill options.
"Ricardo" <firstname.lastname@example.org> wrote in message
> When I try to drag a formula or value down or across the
> spreadsheet...sum the color cells
i have data with different color.
i one column i have 3 color, blue, yellow and red.
now i want to count & sum based on the colors.
how to create formula for that?
you have xl07 or higher, you are suppose to have this capability already.
see help. i am having research trouble confirming that. if you have xl03 or
earlier, see this site....
> hi all.
> i have data with different color.
> i one column i have 3 color, blue, yellow and red.
...Merged Cells #5
Is there some way to find which cells are merged without having to look at
each cell? Sometimes I want to move or delete cells and it says" cannot move
part of merged cell" or something like that, but it does not show me which
cell is merged.
I am not aware of any inbuilt method for displaying merged cells.
One possibility would be to use a macro like the following which will select
(and therefore temporarily highlight) merged cells. As written, the macro
will select all merged cells in the selected range. If the selection
comprises a single cell, all merged cell...Possible to 'copy' cell data into another cell?
If for example
A1 contains: hello
B1 contains: you
Is there a equation/command to put in cell C1 to get it to copy info
from other cells i.e. so C1 shows: hello you
=a1 & " " & b1
If you decide you want money or dates, you can use something like:
> If for example
> A1 contains: hello
> B1 contains: you
> Is there a equation/command to put in cell C1 to get it to copy info
> from other cells i.e. so C1 shows: hello you
> ??????...how to make gaps in plotted data when cell has formula
I have a simple data set with a value for each month except June.
I want the chart to reflect a gap for June, not a zero value. Although I
did tools, options, chart, make empty cells plot as gaps, it still plots
the June value as a zero.
In an attempt to get rid of div/o error, I have a formula in the data set
as If (b5=0, "", c5/b5) . What I'm plotting is the quotient c/b.
We have determined that the formula is what is causing the problem.
Interestingly, if I open this same worksheet (as is) in quattro pro, the gap
appears in the chart.
Is there a different way to do th...Re: how get rid of cells with unused formulas
Actually a much older solution is better for suppressing zero values
selectively -- format with a custom format so that the third parameter
is empty which is a zero value. This allows you to be selective.
Format, cells, number
the fomat of custom formatting is
positive numbers; negative numbers; zero; text
you can override positive, negative but those are the defaults.
see the topic "Create a custom number format" in your Excel Help.
David McRitchie, Microsoft MVP - Excel [s...message comes up when click a cell
I have been trying to work out how to get a message to come up when you
click on a cell. I have attached an example of what I am trying to do,
which was in another spreadsheet which I had, but I didn't make it. I
noticed the feature and wondered how to do it? Thank you for your help
File Attached: http://www.exceltip.com/forum/attachment.php?postid=300547 (example.jpg)
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/
use DATA/VALIDATION menu.
i think "Input...Excel Cell
Im trying to read an Excel file using OLE DB.
I have around 500 characters in one cell. But the program is reading only
How do i make my program to read all characters?
Thanks in advance.
hi, DNKMCA !
> Im trying to read an Excel file using OLE DB.
> I have around 500 characters in one cell. But the program is reading only 255 characters.
> How do i make my program to read all characters?
see if this help:
-> PRB: Transfer of Data from Jet 4.0LEDB Source Fails with Buffer Overflow Error
http://support.microsoft.com/default.aspx?scid=KB;EN-US;q281...Format a whole row based on value of one cell
I want to use conditional formatting to colour a whole row of cell
based on the value in column C.
I understand how conditional formatting works on the single cell yo
select, but I don't know how to change the format for multiple cell
based on the value of a single cell.
Message posted from http://www.ExcelForum.com
try the following as an example:
- select row 1
- goto 'format - Conditional Format'
- choose 'formula' and enter the following formula
- choose a format
> I want to u...Incert a photo into a cell based on info in another cell
I need to insert a photo into a cell (a cell that has been merged btw)
based on the information in a cell (i.e. A1). This cell (A1) uses list
validation and allows one to choose from a list of names. What I want
to do is put a photo into a cell (say B1) from a folder (say
C:\Photos\) based on the name that is selected in A1.
azidrane, have a look here and see if this will do what you want
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Fee...Inserting pictures into cells
We are trying to insert pictures into cells that can be deleted when you
delete the row.
Insert a picture as normal
Right click and go into format picture
choose properties, and check move and size with cells
then when you move, resize or delete cell/row the pciture will b
Lynxbci3's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1037
View this thread: http://www.excelforum.com/showthread.php?threadid=31489
This will not delete the picture
Use F5 objects after you do ...condition of 2 cells = result of 3rd cell
I'll try to ask my question with an example:
Between [ ] = cell numbers
[A1] item1 [B1] 150
[A2] item2 [B2] 290
[A3] item3 [B3] 80
[A4] item4 [B4] 159
And so on .... in fact the A column are products and the B column are the
Here I have made drop down lists, those are working perfectly. This has been
done via "Data/Validation/Allow (list)"
In the drop down list I see all the items of the sheet 1 A column (item1,
item2, item3 etc. ...)
Finally what I want is, when I select an item in a drop down list that its
correspo...Macro to merge cells of the same colour/pattern index
I am hoping that some one here can help me,
First off i have a very, very basic knowledge of excel and vba so please
bear with me and i will try to describe what it is i am trying to do as best
I have a worksheet that has a list of equipment down the left hand side and
week numbers going across the top 1 to 52. The spreadsheet is essentially a
plan of what equipment is being used by certain processes, represented by
coloured or patterned formatted cells in the weeks they will be in use. Kind
of like a gantt chart. Currently i have a spreadsheet were i...ActiveX Command Button
I am using Excel 2007. I have been placing several ActiveX Command Buttons
on a worksheet. I was wondering if there is a setting that would force the
boundaries of my Command Buttons to "snap" to cell boundaries on the
Thanks in advance,
If yes, hold down the alt-key when you're positioning the commandbutton (or any
It'll snap-to the edge of a cell. Get close to the corner and it'll snap-to
Paul Kraemer wrote:
> I am using Excel ...Full cell not printing
I have a cell with 1370 characters. It shows all the text in the formula
bar, but when I print preview or print, only the first 1060 (I think)
characters are shown.
Is this a known limitation, and is there any way around it?
Thanks in advance
The Excel help somewhere states a maximum of 32,767 characters per
cell but only 1,024 'visible'.
Solution perhaps: split your story over multiple cells by using Left, Mid
"Daniel Bonallack" <DanielBonallack@discussions.microsoft.com> wrote in
message news:D3002EB3-1F91-4EA2-B...Adding spaces between numbers in a cell that contains a full address
3-764 Neighbourhood Cir
39 Frostbite Lane
3938 Stardust Drive
4 Jones Lane
I have a spreadsheet that contains addresses in a column such as the
ones above. What I need to do is separate the numbers in the addresses
in one of two ways. Either would be ok.
The first is (and better for me) would be so that the number portion of
the address would have spaces between every number, and then the rest
of the addresses in the same cell. For example, "3-764 Neighbourhood
Cir", would become "3 - 7 6 4 Neighbourhood Cir". The second would be
to split the num...Change Text Case to Upper in a cell range A2:A92
I have a workbook of Contacts. In column A there are Names. I wish to
convert those to UPPER case. I've seen a few VB codes that change the case
to UPPER if I change something in the cell, GREAT if I want to go through
the entire Column and change something. But is there another way?
> I have a workbook of Contacts. In column A there are Names. I wish to
> convert those to UPPER case. I've seen a few VB codes that change the case
> to UPPER if I change something in the cell, GREAT if I want to go through
> the entire Column and change s...