In the cell B9 the value is 01/09/2006
In the cell C9 i enter the formula
In the cell D9 i enter the same formula
It works fine. But if i delete the value in B9, cell c9 displays blank.
But cell D9 displays #value error.
Is there any way to supress this error and display balnk value in D9.
Any help appreciated.
mtpsuresh's Profile: http://www.excelforum.co...How to choose maximum value
Hi, I am wondering if anybody can help me with this. I need to take some
information from Table 1 to a new table which I am working on.
For example, for "apple" in column A, I would like to find the highest
number (30) in Column B and return the correspnonding
text in Column C (a2) to the new table.
columnA ColumnB ColumnC
apple 2 a1
apple 30 a2
apple 15 a3
apple 6 a4
apple 12 a5
apple 9 a6
banana 3 b1
banana 10 b2
orange 2 o1
orange 1 o2
cherry 10 c1
peanut 20 p1
peanut 50 p2
peanut 25 p3
tomato 2 t1
tomato l t1
t...display value in combo box
I populate a combox1.rowsource = value1. How do I display value1 in the
combo box? I can see it in the dropdown, but how do I display it? Thanks.
Do you have it bound to a field in your table?
Build a little - Test a little
"Kou Vang" wrote:
> I populate a combox1.rowsource = value1. How do I display value1 in the
> combo box? I can see it in the dropdown, but how do I display it? Thanks.
On Thu, 13 Dec 2007 09:19:02 -0800, Kou Vang wrote:
> I populate a combox1.rowsource = value1. How do I display value1 in the
> combo b...Setvalue property to default value
I'm Trying to run macro for set property to set the object to default value
related to another object in sub-subform, but I getting the following
message, “ you trying to run procedure doesn’t contain the automation object.
Can you please give the exact details of the macro. I.e. the Actions,
and relevant Arguments. Thanks, that will help us to understand.
Steve Schapel, Microsoft Access MVP
> I'm Trying to run macro for set property to set the object to default value
> related to another object in sub-subform, but I getting the followi...need help sorting text by trailing Numbers Value
I am looking for some help sorting a text field (PartNumbers) alphabetically
but correctly depending on the value of the ending few charachters if theyre
Here is some sample data
i would like it sorted like this
I looks like your values have a fixed length to the left of the numbers. If
this is true, you can use the following in the Sorting and Grouping dialog
Microsoft Access MVP
...MIN formula help
I want to know what the Max and Min numbers are using the following
formulas. The Max formula seems to work without any problem, however
it's the Min that I have a problem with.
If I only have numbers in cells A2:A8 and the lowest number is 150,
then in that cell that I have the following MIN formula in should show
me 150. Instead it's blank because I still have two remaining cells
in my range (A9 and A10).
How can I fix this. Any and all help is greatly appreciated..
MIN/MAX ignore empty cells.
>Instead it's blank because I ...copy range of unique value
i use Excel 2007 and i have a strange behavior with a particular thing
that i do.
quite often i have list of values with several times the same values.
e.g.: 2,3,8,5,4,3,9 from cell A1 to A7
when i use the function:
Data => Sort & Filter => Advanced => Copy to another location, list
range $A$1:$A$7, copy to $D$1 and checked Unique records only.
i correctly get 2,3,8,5,4,9
it means second times that 3 is found, it is removed from the new list.
i have also a good result with only alpha value and only numerical values.
my main problem is when cells have ...Adding default value to pre-existing table
I am working within a database that I created using a downloaded text file. I
have to add a column to the table that will contain the same value in each
field. Is there a way to add this default value to the preexisting table
without keyboarding it in? Thanks in advance for any help.
Create a select query on that table and field. Run it and see what it return.
Next change this query into an Update query. Put in the value that you want
into Update To.
If you don't want to overwrite any existing data, just update empty records,
put Is Null in the criteria.
Jerry Whit...Problem writing Time-Out function
Alright this one might seem a little odd. Basically when I initialize my
dialog I have to call a routine (RunSameThread) which unfortunately calls
some functions set with a library (which I don't have the source code for)
which may not exit. (Basically this call should initialize the COM port and
send's some commands to a transceiver attached to the port, then wait for a
response before returning). Now, if the transceiver is not hooked up (or
turned on) the COM port initialization will go though, but the library
function will sit in an infinite loop :(
Thus, I am trying to write a t...Make a text box expand or shrink based on size of the value
I am working on a report in design view. Is there a way to have a text box
get longer or shrink according to the value? For example, one of the headers
is "Operations" while another header is "Resource Integration."
***Since the text box has a blue fill, there will be empty space within the
text box if I make it large enough to fit the longest word. Therefore, I
want the text box to expand or shrink as necessary.
Any input is appreciated. Thanks!
Set the 'Can Grow' property to Yes.
Build a little, test a little.
"*G...needs double value in string format
I have a double variable
Dim d as double. d gets values stored as 2010031266671939.0
I need this value in string.
If I use d.tostring(), it provides in E+ format. I need this double value in
string without E+ format.
If I use d.Tostring("#.0"), it rounds off the double value & provides as
I need the value as such.
Can anyone help me
Thanks & regards,
Chella Software Private Limited | Mobile : <9944254599.> | Off: +91 452
4262000 | www.chelsoft.com
This e-mail and any files transmitted wit...test for value and return position
I am trying to accomplish two things.
1) I want to test for the existance of a value in a range of number that
varies from day to day.
2) I want to return the cell reference of the data obtained in question (1)
Any IDeas? I though of using something akin to H2:INDEX(H:H,COUNT(H:H)+1))
but I think I'm not on target here
On Fri, 10 Dec 2004 02:54:28 GMT, "Buster" <firstname.lastname@example.org> wrote:
>I am trying to accomplish two things.
>1) I want to test for the existance of a value in a range of number that
>varies from day to day.
>2) I want to return th...Backcolor value in hex
I want to get the hex value of a cell's backcolor, so I can replicate
the same color in Access. In VBA, I try to derive this by using
Hex(sheet.range(cell).interior.color). I get a hex value, but when I
try it in Access, it displays a different color. Am I grabbing the
correct property from the cell in Excel? Or, is there another way I can
derive the hex value?
Thanks in advance.
What version of Excel/Access are you using 2003 or 2007?
I'm believe you can set the color using HSL or RGB scales, which apply to
both Excel and Access (at least in 2003).
If this helps, pl...vLookup, Look up Value
I have problems when the look up value in the vLookup formula is a date, and
the cell has a differente format.
For example, I specify 03/03/10 as the look up value, but the cell has short
date format, so the real value in the cell is 03/03/2010. As a result, the
formula displays #N/A. How can I solve this?
Thanks in advance
It would help to see your VLOOKUP() formula. But I suspect it may look
and in Sheet2, column A you actually have dates. So the text you've entered
( ...Return values from a list that sum to a known value
Is it possible to return values from a list that sum to a known product, for
The returned list would be 9,1 and 7,2,1.
...Hide rows with zero value?
just wondering how I would be able to hide rows on sheets if some cells are
blank? the cells contain formulas they are only blank because `zero
values` in >tools>options>view is unchecked.
I have a sheet set up to SUMIF values from about 16 other sheets, and it
returns the values depending on 72 corresponding codes, I would like to hide
any of the 72 rows of data that are blank.
the values returned will change on a week to week and daily basis, so I
would like to be able to use the same design for each new week, hope that
try something like the following:
Sub hide...Charts and values
I have an excel column chart with a range of values. Some of the values are
so low compared with the main figures (eg. 92% against 0.07%). Are there any
suggestions to make the chart more presentable. I have tried adding the value
and series name to the column but it looks a bit messy.
Ideally I would like a little sub chart showing the lesser values. Is this
Any suggestions, ideas or comments appreciated
Try http://www.contextures.com/charts.html. This site contains links to
other sites with chart tips and techniques including stacking charts.
&quo...determine which cell a value is returned from
How to determine which cell a value is returned from. e.g.=MAX(D6:CC280)
returns 525 - How do I find the cell where it occurs?
"curiousg" <email@example.com> wrote in message
> How to determine which cell a value is returned from. e.g.=MAX(D6:CC280)
> returns 525 - How do I find the cell where it occurs?
(IF(MAX(D6:CC28...Min bal requirement
My checking account has a minimum balance requirement of $250 to waive the
service charge fees, so I want to keep the minimum in the account. Any
recommendations on how to balance to the statement? Should I deduct the $250
from the check register & try to remember this every month when I balance to
subtract the $250 from the bank's balance? Is there an easier way?
=?Utf-8?B?S2VsaUI=?= <KeliB@discussions.microsoft.com> wrote on 08 Sep
2007 in group microsoft.public.money:
> My checking account has a minimum balance requirement of $250 to
> waive the servic...Lookup based on matrix values
I am struggling to develop one of those "copy down" functions that can
produce a set of values. In this simplified example, the formula needs to
tell me the name from column A if there is a value in the remaining array
Dave 1 2 3
So the first instance of the formula would produce "Dave", the second would
produce "Bob", the third ""George", the fourth is "Dave" again, followed by
"Sarah", etc. Yes, I need i...seach function in Outlook XP
Used software: Windows XP (Dutch) or w2k (Dutch and English), Outlook
XP(Dutch), on an Exchangeserver (2002 ?).
When using the search function in Outlook XP there is no result, whether
searching on subject, sender or mail text. There is no result, all mail
disappears. Only by leaving the search field blank all mail shows up again.
The problem appears both with w2k and xp operating systems (English and
Dutch), but only using office xp (Dutch).
Outlook98 worked OK on the same server! By using webmail the problem does
(The improved search options should be on of the main advant...how do i convert Min:Sec to Min.sec for example 68:43:00 = 68.43
how do i convert Min:Sec to Min.sec for example 68:43:00 = 68.43 (68 minutes
and 43 seconds)
In article <5F13B001-9B6B-4D5D-BD81-ACAAF5799CF6@microsoft.com>, "=?
Utf-8?B?dmVsZSBQaGFudA==?=" <vele Phant@discussions.microsoft.com>
> how do i convert Min:Sec to Min.sec for example 68:43:00 = 68.43 (68 minutes
> and 43 seconds)
=MINUTE(C31)+SECOND(C31)/100 where C31 contains the time in hh:mm:ss.
Note that XL may format the cell containing the formula with a time
format. If so, change it to 'General'.
www.tushar-m...IF Function #19
I want to use an IF function to examine the contents of a cell and if
the cells contains a numeric value then do something else do something
else. How do I check if something is numeric?
sgrech's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=14501
View this thread: http://www.excelforum.com/showthread.php?threadid=466914
Use the ISNUMBER() function within your IF() function.
This will result in the value of A1...Ordering the chart by value
I have a stacked column graph which shows me how many people have been sick
over the month and for what reason. The months are on the horizontal axis
with the reason creating the stacked columns.
Is there anyway of ordering the stacked columns by values ie the greatest
used reason code is always at the bottom and the least used at the top. I am
currently struggling with it as each month the order will change and wasnt
sure if the graph can be ordered like that.
I am using an XY scatter chart with data that looks like
Name Priority Date Lic/Ow
Free 12/16/1998 Lic
Warner 1/28/1999 Lic
Buckmon 7/12/2001 Ow
Tucker 12/28/2000 Ow
The Y values are the priority dates and x values would Lic or Own. Each dataset would be referenced in vertical columns. The problem is how to change data labels so that reflect the data in the name column and the data in the Lic/Own column.
Hope you can help.
Thanks but I was able to use the XY Chart Labeler from Ap...