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)
----------------------------------------...Plotting with a formula in a cell
I need help with this, I am sure it is a simple answer,
but I cannot figure it out. I have a workbook with 2
worksheets in it. The first worksheet has all the inputs
from my data in it, and the second worksheet has the
charts in it. I have pasted the links into the second
worksheet and all is fine with that, but when the value is
zero, it plots it as a zero. I would like to just have it
as a gap when the value is zero. I have gone into the
options menu and clicked on the one that reads "NOT
PLOTTED,(LEAVE GAPS)", but it does not seem to fix it.
What I found is that be...Count of values within a cell?
I have a spreadsheet containing a row with data something like:
A | B
1 | 1111
2 | 1112, 1113, 1114
4 | 1115
If I were to do a COUNTA(B:B) I would see a count of 3. What I'd *like*
to find is a way to see a count of all numbers in that row (in this
case, a count of 5).
(replace somewhere in email address with gmail if mailing direct)
<email@example.com> wrote in message
news:1163...how can I view all the data in a cell?
I'm working in Excel 2003. Throughout the entire spreadsheet there are cells
that when printed do not show all of the data. We have tried formatting the
height and width, wrap text but nothing seems to work. Does anybody have any
Excel Help on "limits" or "specifications" reveals that Excel will allow
32,767 characters to be entered in a cell.
However, it goes on to state that "only 1024 characters will be visible or can
To work around this limitation, stick a few ALT + ENTERs in at appropriate
spots, about every 200 characters..
...Count only cells with red font.
I am working with Excel 2003.
I currently have my data range set up to count only non-blank cells with a
counta function. However some of those cells contain gray font and others
contain red. How can I change my formula to count only non-blank cells that
have red font?
Have a great day! Thanks for your help!
Start with Chip Pearson's site:
If you're new to macros:
Debra Dalgleish has some notes how to implement macros here:
David McRitchie has an intro to ma...comparing from one sheet to another
Hi. I am fairly new to Excel, and am trying to make a
workbook to analyze home purchase costs, etc. One thing I
want it to do is be able to enter a purchase price on the
main sheet, and then compare that price to data on another
sheet that has a range of home prices and the title/escrow
fees at that price range, and them put the corresponding
fee back on the main page. Any idea how I do this?
have a look at VLOOKUP. See:
"Trish" <firstname.lastname@example.org> schri...#Value! errors
I have experienced a problem that I am unable to find a rectification
for. When my staff constantly use the space bar to delete an entered in
error number and leave the cell blank (although there is a space in the
cell) any cell which uses that cell within its formulation shows
#Value! or #####. I am aware that they should not be using the space
bar to delete numbers and the space within the cell is the reason for
the other formulation errors. I guess my question is how do I format
the cell so that only a number and nothing else can be entered?
--------------------------------...INSERTED table no holding exact float value
I have a table A with a float column and a trigger to write to a log on
inserts to table A.
I am using the sql INSERTED table to get the value of the float column,
and insert it into a varchar column in the log (cant be changed, its
generic and used for multiple tables).
I am having a weird issue where the value in the log gets rounded to the
next integer in the audit table, it inserts correctly on Table A though.
Is this a known issue? Is there a workaround? If not, what am i doing
Thanks in advance,
*** Sent via Developersdex http://www.developersdex.co...change default value "save message"
I have a little problem.
When i wanna save a mail message, de default filetype =
I wanna change that to *.msg file.
But i can't get that default!
i have to change it every time manual!
...Inseting data in cells
I have a row of cells that have last names in them, is there a way I can
insert some data into all of the cells, this data will be the same for
then if possible export all of the names to a word or text file all in
Thanks for any help!!!!
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/
Assuming last names are in A2:Z2
Put in A3 :...Get values from blank cells
I have some width and height values in several columns with a lot of
blank cells of sheet2. Like this:
A B C
blank blank 2*987
blank blank blank
blank 4*500 blank
5*400 blank blank
How can I get only the cells with values in column A of sheet1:
Can this be done!
Thank you so much
What do you want to do if there are two values in a row? Average them? Add
them? Both of these would work as long as if the values were numbers or
"canvas" <spyele123@gmail...MS Excel VBA Pivot table link cell fetch records
MS Excel VBA Pivot table link cell fetch records
I have a table 'tbl_Final' in MS Access 2007 where the data is used
and cached in a pivot
table found in the 'Data' worksheet in MS Excel 2007.
In this MS Excel 2007 workbook, I have various worksheets reports
which links to the pivot table values
found 'Data' worksheet.
I linked formulae in one of the cells found the worksheet reports,
looks like this:
Lets sa...How can I assign a value to a cell without putting a formula in it
I have one cell (C33) that has a list validation attached to it. The list
(column A on a separate sheet called Data_text) is a series of text
statements that the user can select from the drop drown.
I have a second cell (C44) that has a Vlookup formula based on the contents
of the first cell (C33). The Vlookup formula returns the text in column B
(from the same list, Data_txt) from the selection made in column A (or what
is now in C33)
Both cells have only text values.
I need to assign the returned Vlookup value to C44 but I don’t want to put
the Vlookup formula in it. Th...Extract names from string based on value list
I have a list of productnames and a list of brandnames.
I want to supply the productnames with an attribute "Brand".
I am currently manually filtering the list of productnames based on
"Contains... Brand" and adding the Brand to the product using copy paste,
but this takes me too long and I can only match records one brand at a time.
Can I make Excell search through the list of productnames and whenever it
comes across a brandname within the string of a productname it will add it to
the column next to it?
Assume productnames running in A2 down
A...How to prevent users from selecting protected cells in XL97 ?
I created a workbook in ExcelXP but when I put it on a XL97 pc protected
cells can be selected. Is there a way to correct this other
then upgrade XL?
Thanks a million in advance.
You can do it with VBA code
If you copy this event in the Thisworkbook module
It will run automatic when you open the workbook and
Protect each sheet in the workbook and set the EnableSelection
Test it on a test workbook
Private Sub Workbook_Open()
Dim Sh As Worksheet
Application.ScreenUpdating = False
For Ea...removing blank cells #2
Column A looks like this (there are actually 162 cells, not just the
I want to get all the cells with an @ symbol in them to be in column
without the blank cells in between and all the cells without an
symbol to be in column B. I have to do this for 30 sheets so I don'
want to have to cut and paste on all 30 sheets. Is there a way to d
this with a formula or formatting? Thanks
Message posted from http://www.ExcelForum.com
try the following:
1. In B1 enter the array formula (entered with CTRL+SHIFT+ENTER):
=INDEX($A$1:$A$...Put all cells in one cell seperated by comma
I have an Excel spreadsheet with data in seperate cells such as
I need all cells in one cell seperated by a comma such as
I found out how to add the comma after the cell data, but cannot figure
out how to put them all in the first cell.
Can anyone help?
kokopoko's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25892
View this thread: http://www.excelforum.com/showthread.php?threadid=392760
=a1&",&q...Need Vlookup to work with formula in reference cell
The following formula does not do the lookup:
When T7 cell contains =(B9-S9)
When I put the total that T7 equals in T7, it works.
Any ideas how to make it work with the formula?
mikeburg's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24581
View this thread: http://www.excelforum.com/showthread.php?threadid=393139
My guess is that T7 is not what you think it is. If you see 9, it could be
You coul...how do i pick up the values ?
This is a multi-part message in MIME format.
I have this XML file, and i am not sure how do i read this xml file and =
read it to an arraylist, for which i use to fire some SQL statements.=20
I want to be able to pick only xml attributes, say, which belong to =
Section=3D"two-4" and put them into an arraylist and similar for the =
rest of the Section attributes.
i had got the code below working, till the time, i had to change the...2nd Request
The two tck_201_ fields are text boxes on a form that are being set by to
calculated text boxes on a different form!subform. As you can see by the
debug statements and results, the values will just not transfer. I have set
the format for both of the TCK _201_ fields to Fixed! Any Ideas?
Me.TCK_201_Done = Forms!frmPTS_Edit!SFrm_Date_Used_E!TMS_Actual
Me.TCK_201_Tot = Forms!frmPTS_Edit!SFrm_Date_Used_E!TMS_NumDates
Debug.Print Forms!frmPT...Securing a cell.
Is there a way to lock the data in a cell so no other
user can edit it and still leave the edit ability in all
1) Select the cell, use menu item Format|Cell; open Protection tab; click to
remove check mark in Locked box
2) Use menu item Tools|Protection and click Protect Sheet (password is
"Peter" <email@example.com> wrote in message
> Is there a way to lock the data in a cell so no other
> user can edit it and still leave the edit ability in all
> othe...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
<firstname.lastname@example.org> wrote in message
> Is there any way of referencing a ce...Cell Referencing Problem
In excel 2000 I would like to be able to read in different values for AK into the following cell entry: ='Feb01-04'!$AK39
Stated another way, I would like to rewrite the above cell reference so that the value AK is read from another source cell.
For example I might type AX into source cell A1 and the above reference would change to ='Feb01-04'!$AX39
Any help would be appreciated.
You can use the INDIRECT function to do this. E.g.,
M...Add Date And Time in a cell.
I want to add the time and date for a cell, but I want these date and time
to be automatically updated itself with each passing day, and second. So
when I log on this excel sheet later, the cell will show a different date
and time in which I am currently in. How do I accomplish this? Is this
possible at all?
use the Now() function - this is volatile and will be updated after each
sheet calculation takes place.
put the following in the cell required - it formats to date and time (which
you can change using cell format)
"Raymond" <NotValidEmail@yahoo.co...How to merge or split cells?
Hi, is it possible for Excel merge cells or split cell like tables in Word?
Excel will allow you to merge cells, but not split them (unless they are
part of a merged cell).
To merge a cell, highlight the cells you want merged,
Format>Cells>Alignment, and select merge cells from the text control
sections (Excel 2003 - similar for 2000)
Hope this helps.
"Min" <email@example.com> wrote in message
> Hi, is it possible for Excel merge cells or split cell like tables in