Is there a way to use an If statement to check to see if a range of cells is
blank? For instance, I would like to use a formula to sum B2:B10 if there is
data in at least one cell. But if all cells are blank, I would like the sum
to show a blank instead of an 0. Something like, If (B2 OR B3 OR B4...)<>"",
Sum B2 to B10, Else leave blank. Any suggestions? Thanks.
"Brent E" <BrentE@discussions.microsoft.com> wrote in message
news:542AE08D-B23B-4C03-BCA9-53054EC65BE...Double-Clicking a cell for Multiple Options
I recently came across a spreadsheet where a cell was double clicked for the
multiple options to become available.
This was similar to a drop-down list, but there was no drop down list!!
The cell currently shows a value of "A".
I double-click on this cell ... the cell changes to "B".
The new value is not random, but coming from a pick list or a pre-determined
Does anybody know how such a feature is created?
If you rightclick on that cell and choose "Pick from DropDown List" do you get
the same effect?
If yes, ...Blank Field
I transferred data from one excel file to another. Upon
transferring the data as Value only, the new excel file is
capturing a hidden character in the new sheet. Any idea
how I can tell excel to ignore all blank fields on an
excel spreadsheet ?
>I transferred data from one excel file to another. Upon
>transferring the data as Value only, the new excel file is
>capturing a hidden character in the new sheet. Any idea
>how I can tell excel to ignore all blank fields on an
>excel spreadsheet ?
What's the hidden character, as in CODE(CellContai...Cell shading based on date conditions
I want to shade a column based on a date criteria. The date criteria is
entered by the user in column B5 and the day could be other than the
I have 20 columns that display values and the date is displayed as
heading. For example
Column C Column D Column E
Row 6 7/1/2006 8/1/2006 9/1/2006
Row 8 1,000 500
Row 9 500 500
Row 10 500 500
Row 11 500 500
If the user enters 8/31/2006, I want Column D to be shaded. Column B5
changes every month. Is it possibl...Avoiding returning a 0 when no data in cell
I have worked out the formula I need to use (with help from someone
from this group).
However how do I stop it returning a 0 when no data is present in the
You may want to suppress the #n/a error, too:
> I have worked out the formula I need to use (with help from someone
> from this group).
> However how do I stop it returnin...how do i convert 100 in one cell to one hundred in another cell
i want to convert number (that could also be result of a function) in one
cell to words. Example if one cell has 121 then in another cell it should
read one hundred twenty one.
This should work
Microsoft MVP - Excel
"Numbers into words" <Numbers into firstname.lastname@example.org> wrote in
>i want to convert number (that could also be resu...issue with Ignore Other Applications option
I want to view my Excel workbooks in separate windows (I have 2
monitors and want different workbooks displayed in each). Looking
around online it seems the setting to allow this is:
Tools > Options > General > Ignore other applications
I selected this, and it works. When I click on an Excel file in
Explorer, a new instance of Excel opens.
However, when Excel opens, there is no workbook. I have to go to File >
Open and manually navigate to the file to edit it.
Is this a known side effect or some anomaly? Is there anyway to fix
this while still being able to open workbooks in se...Referencing a cell even if its contents are moved moved/replaced
I want to reference a cell no matter what happens to it.
From A1 down:
From B1 down:
If I cut and insert B3(15) into B2, the A column would then read as:
How do I keep the A column referencing the original cell?
will always point to B1 -- no matter if you delete or insert any
> I want to reference a cell no matter what happens to it.
> Simple example:
> From A1 down:
&g...Including cells that are highlighted in a formula
I have a strange requirement maybe, but I do a budget spreadsheet on which I
highlight items that have cleared my checking account. I would like to be
able to write a formula that sums only the cells that are highlighted a
certain color. Is this possible?
let me clear this up a little. I don't really mean "highlighted" as in I
highlighted them with a mouse. What I mean is formatted with a fill color.
sorry for the confusion.
> I have a strange requirement maybe, but I do a budget spreadsheet on which I
> highlight items that have ...Strange cell behaviour #2
There's a very strange problem i'm currently having when entering
figures into any cell in any workbook.
For instance, when i enter '1', the figure 0.01 is displayed, 10 = 0.1
and so on.
I've tried cell formatting but to now joy.
Alternatively, is there a way i can reset all excel default settings
without having to re-install?
Your help would be gratefully received.
madhatter_scfc's Profile: http://www.excelforum.com/member.php?action=getinfo&userid...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"...adjust cell height
Is there a formula to adjust cell heights in a cell so there's a certain
amount of pixels above and below the letters/numbers? This is so the numbers
aren't so crowded in the cell if formatted "text-wrapped"?
You can auto fit the row height. There is no option for pixels above and
below similar to Word where you can format the text to have leading and
> Is there a formula to adjust cell heights in a cell so there's a certain
> amount of pixels above and below...Formula to copy cell in Excel from on file to another
I have 19 excel files in a folder and I have created another excel
file (B) in which I am trying to set a formula that can look in Cell
"A6" which has the path with the file name of the first file (1) then
copy From the first File (1) cell "L25" an place it in Cell "Y6" of
I want the file names or file names with the path to be in file (B) so
that I can change the name easily instead of changing all formulas.
Hope I have clear explanation!
The function you'd want to use is =indirect().
But =indirect() won't work ...can't type "HSA" into a cell
I have a part name HSA but can't type it into any cell, because Excel will
change it to HAS automatically? Please help.
Excel is trying to "correct" your spelling.
Tools > Options... > Spelling > Autocorrect Options > Autocorrect
and fix the dictionary
Gary''s Student - gsnu200909
> I have a part name HSA but can't type it into any cell, because Excel will
> change it to HAS automatically? Please help.
...Need fast way to move from cell to home
What is the quickest way to move from a cell to home (A1) in Excel? I
thought you could click on something or hold down two keys in order to
accomplish this, just can't remember. Thanks.
Microsoft MVP - Excel
"Debbie" <Debbie@discussions.microsoft.com> wrote in message
> What is the quickest way to move from a cell to home (A1) in Excel? I
> thought you could click on something or hold down two keys in order to
> accomplish this, just can't remember. T...How can I only the cells showing by a filter?
If I select to copy a filtered list of cells and pasted to another app, only
the visible cell contents got copied. If I pasted to another excel sheet,
all hidden cells got copied as well. How can I only copy filtered cells
within Excel? Thanks!
Excel should paste just the filtered data between sheets.
I don't know why it doesn't for you.
Which version of Excel are you running? I tested on 2003 version.
A workaround would be.......................
F5>Special>Visible cells only>OK
Copy and paste.
Gord Dibben MS Excel MVP
On Wed, 19 Dec 2007 13:54:03 -0800, Pin...Data range reference in a cell
I'm working with a large number of data ranges and i want to be able to
change the source data of one chart by typing the range name in a cell rather
than directly into the chart source tab.
For example: in B1 i've typed "SCK" (which is defined as a range on another
I want to be able to change B1 to read "JAB" and the chart to change it's
source data to the JAB range which is also defined.
...Auto-checking a checkbox based on cell contents.
Is it possible to set or "check" a checkbox in real time, based on the
contents of a cell?
I know the cell link field can tie a cell to a check box, but (if I
understand it right) it's a one way link (meaning the cell reacts to
the check box).
Any help would be appreciated.
this would require VBA using an event procedure like worksheet_change
"Ramon Cantu Jr." <email@example.com> schrieb im Newsbeitrag
> Is it possible to set or &quo...Number of characters per cell
A friend sent me a file that she swears was created and modified only in
Excel 2000. However, there is one cell with a lot of text in it that appears
to be showing only the first 255 characters (I didn't count, just guessed at
the number). I believe that although Excel 97 allowed only 255 characters
per cell, Excel 2000 allows up to 65,000 characters. The cell is formatted
General, left aligned, wrapped, vertically centered and the row height is at
the maximum of 407 - but visually there is room for more text in the
row/cell (above and below the vertically centered text), but the text
does...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.
...Cell Names, References ?
I am attempting to re-work an existing worksheet that contains cells
that have been "named". Does anyone know how to change or delete a
name once entered into the "Name Box"? When I attempt to write over
the names, I receive and error message regarding references. Thank
tbryson's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36246
View this thread: http://www.excelforum.com/showthread.php?threadid=560418
Use CTRL+F3 to bring up the Defined Name dia...sum based on content of other cells
Cells A7:A49 have pull downs with 20 categories to pick from.
Cells H7:H49 has the value in hours spent on that category.
I would like to get the sum of hours based on each category, and put
this value in another cell.
Can someone please help me with this ?
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/
You want to use the Sumif function.
>Cells A7:A49 have pull downs with 20 categories to pick
>Cells H7:H49 has th...Cell formulas that expire after a month
I have a spreadsheet that I'm using to track progress over the period
of a year. One sheet is raw data that is manually entered daily and
one sheet is monthly statistics based on that data. In the statistics
worksheet are cell formulas calculating statistics relating to each
month based on the raw data.
If I change the data in March, I don't want January's or February's
statistics to be changed.
Is there a way to do this?
On Mar 7, 11:52=A0am, PaulH <paul.h...@gmail.com> wrote:
> I have a spreadsheet that I'm using to track progress over the perio...Bad visbility off a cell
Operating System: Mac OS X 10.6 (Snow Leopard)
Hi, i am new here. I have a question, when I click in a cell in an excelsheet then this is obscured. I mean the square of the cell is almost invisible. So if there is a lot of figures in this sheet you can not find back the cell where u was working. Has anyone a solution how to change in a better visibiliy? Thanks in advance. Thijs
On 3/3/10 8:04 AM, Thijs@officeformac.com wrote:
> Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor:
> Intel Hi, i am new here. I have a question, when...Font enlarges when cell is linked to email
When I enter an email, the font enlarges from 8 to 10 and underlines
itself. I can live with the auto underline if I must, but I need to
find a way to prevent the change of font size. Are there any
remedies? Thanks for your help.
In the AutoCorrect>Autoformat as you type turn off "internet and network
I have never found a way to prevent the hyperlink from defaulting to 10
Gord Dibben MS Excel MVP
On Fri, 12 Jun 2009 16:09:52 -0700 (PDT), Michael Lanier
>When I enter an email, the font enlarges fro...