Cell formatting behaviour question
I have 2 related cases that I dont understand how to get Excel formatting to
1. If I imported or copied rows of data (numerical) from another file (Word
or Access) into Excel, the data is presented as raw numbers in each cell.
Now I apply a formatting (i.e. I want comma separation for thousands, etc.)
and it will not show up. However, if i then double-click inside a cell (as
if to edit the contents directly), then hit enter the formatting I want
shows up... but only that cell. I have hundreds of rows and I really need to
force excel to display the formatting i want immediately...Hide multiple rows when particular cell is zero
Can anyone tell me how I can hide multiple rows when a particular cell
It has to go automaticly, so no buttons...
When D37=0, hide rows 37-48
When D49=0, hide rows 49-58
When D59=0, hide rows 59-68
When D69=0, hide rows 69-78
When D79=0, hide rows 79-88
How can help me with this macro??
Thanks in advanced.
What should happen if e.g. D38 = 0?
Maybe this applied to the desired sheet:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim a, rng As Range
Set rng = Range("D37:D1000")
For Each a In rng
...Using a cell reference as the axis value for Horizontal axis crosses
Is there any way of referencing a cell as the value in the "axis
value" area of the "Format Axis dialog box. I am currently entering a
number i.e. 2.5. I would like to be able to have this number change
when a user enterer a value in a cell? Would it have to be VBA code
or is there another way?
A coded solution is currently the only way.
Andy Pope, Microsoft MVP - Excel
<email@example.com> wrote in message
> Is there any way of referencing a ce...Simple Search and Replace Question
Hi. I am searching and replacing various characters in an Excel column. I
need to delete all the asterisks (*) in this column. How do I do this? That
is, how do I make Excel "think" the asterisk is NOT a wildcard characteer but
a real character that I want to get rid of?
> Hi. I am searching and replacing various characters in an Excel column. I
> need to delete all the asterisks (*) in this column. How do I do this? That
> is, how do I make Excel "think" the asterisk is NOT a wildcard characteer but
> a real character that I want t...Find/Replace text, NOT format!!!
I am running into an extremely irritating problem in excel 2003.
Here is the situation, I am dealing with files that are very large,
for example the file in question has 12,900 rows.
All of the information is imported from a text file, generated by
another program. I didn't originally bring in the information,
someone else did, and now I am in charge of this file.
So here is the situation, there is a column called "DISPOSITION"
containing 4 digit names. One of the names is "5APR"
When this list was imported some time ago, the format of the column
must have...Please quote.dot for 1.2 its really urgent
...Macro to save & replace & open txt file
I want you help to do Macro to save data for example A1 to A7 in txt file
and in same time if press the button 2 time the file replace all date with
also when i press the but and afte load the data it will lunch auto
You may want to start by recording a macro when you do it manually.
You may have a macro that works or needs very little tweaking. You can post
back with your questions, then.
I don't understand the second question.
> I want you help to do Macro to save data for example A1 to A7 in txt file
> and in same time if pr...Referencing only certain cells in a table
I posted this question earlier in the execl.worksheet.functions group,
but did not get an answer. I apologize for the repost.
I need some help referencing certain cells from one worksheet in
another. Here is an example of what I am trying to accomplish:
Item # Name Value
1 item1 100
2 item2 0
3 item3 0
4 item4 54
5 item5 0
6 item6 0
7 item7 70
I need to create a separate table in Worksheet2 that as only the NON
ZERO values from the table above in Worksheet 1. S...another applic. did't shut down properly messed up outlook.pst
I was setting up companion link to sync goldmine calender. It did not work
so I uninstalled companion link and tryed to reenter outlook. Now I get this
error message: Unable to display folder. Another application closed while
using your personal folders file....outlook.pst. How do I fix the error and
use my outlook.
Billy Calhoun;117725 Wrote:
> I was setting up companion link to sync goldmine calender. It did not
> so I uninstalled companion link and tryed to reenter outlook. Now I
> get this
> error message: Unable to display folder. Another app...adding number only if CDN is displayed in next cell
I have a list of sales for Canadian and US Customers. I marked in the column
besides the sales amount if it is CDN or US.
How can I add the CDN or the US Sales only.
Thanks for your help.
See HELP for details
"ED" <ED@discussions.microsoft.com> wrote in message
>I have a list of sales for Canadian and US Customers. I marked in the
> besides the sales amount if it is CDN or US.
> How can I add the CDN or the US Sales only.
> Thanks for y...checking input in a cell and return by copying info from a other c
What I want to do is the following. I have a worksheet in which I have
created the following; Name-Column , select-Column. I now want to created a
formula in a new sheet which check if the are "x" in the select column and
copy the names of the name-columns to cells in that new sheet. (which is in
the same workbook) is that possible? If so please let me know.
Thanks in advance.
To find a single "x" you would use VLOOKUP(). For all the "x"'s, see:
Gary''s Student - gsnu20...Averaging List with Blank Cells
Someone gave me this formula to average the most recent five items in
a column of numbers:
It works great unless the list has blank cells. In that case it does
How can I make it work when the columns have some blank cells?
>Someone gave me this formula to average the most recent
five items in
>a column of numbers:
>=AVERAGE(OFFSET(B2,COUNTA(A...Pivot table overwrites neighbouring cells #2
I have multiple pivot tables on the same worksheet to allow for easy
comparison of data. The source data is based on the week and expected to be
refreshed daily. As the week progresses the size of the pivot tables will
grow and overwrite data below it.
I am looking for a workaround to this problem or if there is a way to set
the pivot table to insert a new row below it if the size will increase and
before data is updated.
Thanks in advance,
There is no feature of pivot tables to do what you wish. You pretty much
have two options that I can think of:
1. Prepare e...Splitting Text from single cell in column across multiple Columns
Text To column wont work because I have name and address info in a single
cell in this format. Each cell has different data representing different
St # St Name
City, State zip
I want a formula or something to take first line and put in one column, the
2nd line in another column and the 3rd line spread across 3 columns. Although
if you could just find a way to split each line into a column, that would
Once again, Text to colun won't work, I have multiple cells with diffeent
addresses, they are not all the same and there are no deli...Is there any way of calculating a running total within a single cell in a column
Can anyone help with this query?
e.g. I want to work out how many people are working at any time in a
24 hour period. The number of people rostered on differs each day of
Monday Time No. rostered on
(and so on)
Can I add additional staff number in my 'no. rostered on column' by
just entering the number into the cell.
Say, if there were an extra 2 staff starting their 8 hour shift at 9.
am. is there a way of just typing in '2' into that cell and getting
What I do at the moment ...another OGA question
I bought a copy of Office 2007 Pro from an online seller who advertised it as
a genuine, new copy. It came in a sealed box with the hologram CDs and
installed with no problems using the product key in the box. After
downloading updates, I started getting the "not genuine" message after
reboots. The seller insists that this is a legitimate licensed product which
was purchased from a Circuit City store that was going out of business, and
that the messages I'm getting are due to some problem with the installation,
not with the authenticity of the software.
I ca...Bug! Error Bars Inherited from Another Workbook
Got an odd one here.
I have suite of macros that create a workbook with a number of sheets,
containing charts from a number of separate workbooks. The charts show
a number of series, including a mean with custom error bars, taken
directly from two columns in the source data, spec for the + bars
='C:\Temp\[Some_File_Or_Other.xls]Data_Sheet'!$CK$2:$CK$366, and the
following column for the - bars.
I run the macro once, and save the resultant chart-filled workbook as
I change the data in the source workbooks, rerun the macro and save the
results as DirectoryB/FileB...Upgrade from 1.2 to 2.0
My RMS reseller upgraded me from 1.2 to 2.0.
After doing so, none of my matrices work due to the fact that attributes and
codes are not in the matrix dimensions.
I have lost all customer purchase history, but I do have the totals they
I have lost all my purchase orders.
They installed SQL express.
I can not connect to either of my stores through HQ.
Am I with the wrong reseller?
Can someone help?
Ouch- That has got to hurt- If I lost that Data, I would want to strangle
"lax guy" <firstname.lastname@example.org> wrote in message
news:EDEE3830-4C03-4778-...Custum Cell Format
I have an array formula which automatically summarizes all available
values of a certain column, in ascending order. The summary is fixed to
a maximum of 8 cells and in many cases it uses less than the available 8
Is it possible to format the cells by showing a 0 when the result/cell
In other cells in the sheet the cells show empty instead of a zero by
using the custom format of
Now I want an empty cell to show 0
Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands)
----------------------------------------...Printing 1 of ? pages.
Is it possible to print two copies of the same sheet onto one sheet (A5 onto
and have each A5 sheet numbered 1 of 2, 2 of 2 even though thy are on one
I can print one sheet at a time numbered 1 of ?, 2 of ? ... up to a number
entered in cell I2 using:-
Dim CopiesCount As Long
Dim CopieNumber As Long
For CopieNumber = 1 to CopiesCount
.Range("C12").Value = CopieNumber & " of " & CopiesCount
Next C...How do I draw a circle around a cell I want to highlight
How do I draw a circle around a cell I want to highlight - I want to
see the text within the cell and colour the circle red. I tried
inserting a circle from the 'drawing toolbar' but I couldn't see the
text in the cell.
i have seen Excel worksheets with this feature and would like to
See Tools/Formula checking/Display Formula checking Toolbar/Circle unvalid
„Janev” ezt írta:
> How do I draw a circle around a cell I want to highlight - I want to
> see the text within the cell and colour the circle red. I tried
> ...How to copy VB macro from one spreadsheet to another?
How do I copy a VB macro from one spreadsheet to another.
One way is to open both wb's at the same time; select "show code", find
the sheet or module in the source wb that contains the macro, highlight
the code and copy. Select the desired sheet or module in the target wb
protonLeah's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=32097
View this thread: http://www.excelforum.com/showthread.php?threadid=531743
...All cell contents within a range of cells turns white if Sheet1!A1 = 1
If the return of Sheet1!A1 = 1, I need all fill, font and line colors
to turn white from A1:E10. If the value of Sheet1!A1 changes to 0,
all colors must return to normal. Does anyone have any thoughts on
this? Thanks for any suggestions.
If your range to format is on Sheet1.............
Format>CF>Formula is: =$A$1=1 Format to suit
If range to format is on a sheet other than Sheet1
Select Sheet1!A1 and give it a name..............insert>name>define
On other sheet select range A1:E10 and Format>CF
Form...Dynamic cell references in cube functions
What is the syntax to make for instance the cubevalue function pick up
parameter values from the spreadsheet so my report becomes dynamic?
I have the following statement:
I want to make a dynamic report so that the year  and the month
 changes according to dates I put into the spreadsheet (for instance
today()) so the report automatically updates the query to current time
Thanks in advance!
I don't know cubevalue and it isn't in Excel help on ...How to link cells only if...
In cell a1 I have an option for an "y" "n". Based on that cell I want
to link cell b1 to cell d1. If it is "Y" then link it, if it is "n"
then I want it to go to cell b1 and check it and so forth. I only want
to link the items in column "b" that have a "y" next to them. I can do
this for one cell and even skip and go until I find the first "Y".
What I can not do is make the second link start after it has found the
first "y" in column "A". Can anybody please help. This is the 3rd
time I am asking f...