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
...Excel Function VLOOKUP
I'm having trouble looking up a table of "Names". The table deifene
below is called ROL_IS and list hundred of other defined tables.
but no errors where
=VLOOKUP(CUR_MON,ROL_IS,3,FALSE) ANS: ACT_IS
The array function in VLOOKUP appears not to converting formula to
If anyone knows or needs further details if would be appreciated.
Message posted from http://www.ExcelForum.com
If I understand you correctly
=VLOOKUP(A33,INDIRECT(VLOOKU...Xpath / XPathExpression equivalent of SQL's "Top" function
Is there one?
Also in either xpath or XPathExpression whats the best way to get the
full xpath of a node.
As i am using xpath query of (//books/.......)
So I would like to see what is before the /book tag?
> Is there one?
Yes you can do /books/book[position()<6]
That will select the first 5 book elements in the books node.
> As i am using xpath query of (//books/.......)
> So I would like to see what is before the /book tag?
Hmm you can get the parent node once you get the child I guess.
"ree32"...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...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...Close Database Function?
I want to use task scheduler to open an Access DB, run a macro that updates a
table from an external source, then close the DB and Access. I do not use
any forms on this database, just one table and one macro.
So I guess my needs are as follows:
1. Automatically execute 'My_Macro' when the DB is opened from Task
Scheduler. My Scheduled Task uses the /X switch to specify and execute the
Macro - my main problem is determining how to programatically close the DB
and Access. I would like to continue using the command line method of
executing the macro if possible so that I can m...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...Passing an Array created in a Function or Subprocedure back to the calling Subprocedure
I want to have a Function or Subprocedure that can create an array with
52 specific values in it. I then want to call this Function or
Subprocedure from a main Subprocedure and be able to access the
contents of the array. Everything can be of type variant for the
purposes of this exercise. Can anybody help, please?
This worked ok for me:
Dim myArr As Variant
Dim iCtr As Long
myArr = BuildArray(myArr)
For iCtr = LBound(myArr) To UBound(myArr)
MsgBox iCtr & ". " & myArr(iCtr)
Function Buil...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 ...Search function inoperative in Entourage address book
> This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
My search function is no longer working in the Entourage Address Book.
Initially, large sections of my contact list would simply vanish, allowing
me to scroll through long lines of blank space. After rebuilding my
database, contacts don�t vanish but the search simply doesn�t do anything.
If I type in the �Name Contains...� search...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)
----------------------------------------...How can I update endnote cross-references in Word 2007 ?
I created a long document with many endnotes and some cross-references to
these endnotes. When I insert a new endnote somewhere in the document, the
following endnote numbers change but not the cross-reference numbers. This
seems to be a bug in Word 2007 as it worked perfectly in my previous Word
version. How can I update endnote cross-references in Word 2007 ?
Ctrl+A, F9 should work in all versions of Word.
Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA
"Joe56" <Joe56@discussions.microsoft.com> wro...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
> ...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 ...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...how do I create a macro to select all colored cells in a worksheet
Need help writing the code for an Excel Macro that would find all cells that
have any interior color so that an outer border can be added around all
Try this. I've included the code to add the borders but you can delete this
if you want to do it manually. Not this will not work for conditionally
formatted coloured cells
Dim CopyRange As Range
For Each c In ActiveSheet.UsedRange
If c.Interior.ColorIndex <> xlNone Then
If CopyRange Is Nothing Then
Set CopyRange = c
Set CopyRange = Union(C...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...Preserving lock on cells even when copied and pasted within a sheet
(Sorry for repeating this post from another group; I inadvertently
neglected adding both groups in my original post in
microsoft.public.excel.misc. In any case, I found a couple of minor
errors and omissions that I have corrected :-P.)
I am trying to create a *protected* worksheet that is flexible enough
so that additional rows can be created (by copying existing rows from
above) if additional data becomes available to be entered. Locking
rows with formulas and protecting the sheet does allow the user to copy
and paste the cells or rows (most often accomplished by dragging by the
handl...Put result in different cell
Because the formula is in cell C4, the result appears in cell C4.
How can I have the result put into a different cell?
...Cannot Choose cells (or ranges) after column S
With Ctrl + A, I can use highlight Column A - R. And cells in Column S and after cannot be chosen by mouth. Do you know what is the reason? Thanks.
try hitting CTRL+A twice. This behaviour depends on your Excel version.
The first CTRL+A selects the current used range
> With Ctrl + A, I can use highlight Column A - R. And cells in Column
> S and after cannot be chosen by mouth. Do you know what is the
> reason? Thanks.
...seeing the formula (not the cell)
Suddendly, I have this strange behaviour.
I have a cell say C3 which contains 2b as a text.
Then on cell I4, I want to se the content of cell C3. on cell I4, I used to
=C3, then on I4, I would see 2b. What is strange now, is that on I4, I see
=C3 (the "formula" and not the result). What is more surprizing, is that
on this excell file, I have 2 sheets. On the first sheet, referencing works
On the second sheet, I can see only the formula.
What do I do to not see the formula, but the resut ? I don't remeber touching