Cell Value on Toolbar
Is it possible to show the value of a cell on a custom toolbar. Cell
location will not change, but cell content will.
HilcrRWise's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=11699
View this thread: http://www.excelforum.com/showthread.php?threadid=494807
Const WS_RANGE As String = "B10"
Private Sub Worksheet_Activate()
Dim oCtl As CommandBarControl
On Error Resume Next
Set oCtl = Application.CommandBars.FindControl(Tag:=&qu...Adding third lookup value to index/match
I understand how Index/Match works with double lookups... But I cant
quite get the third lookup down.
Looking to add a third lookup value and range to this formula (range
=INDEX(table, MATCH(x,range x,0), MATCH(y,range y,1))
Data is laid out like so... (Vertical column range is x)
A B C D E F G H
1 range yyyyyyyyyyyyyy ->
2 range zzzzzzzzzzzzzz ->
How does the 3rd range relate?
Need more specific details.
Microsoft Excel MVP
"J.W. Aldridge" <firstname.lastname@example.org> wrote in mess...Subquery returned more than 1 Value in Manufacturing
Working in the Edit MO Status window. Can search by MO number, but if
try to search by any ranges (ex: date, site, etc), get error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Subquery returned more
than 1 value. This is not permitted when the subquery follows =, !=,
<, <+, >, >+, or when the subquery is used as an expression.
We are running GP v. 9.0. Any ideas?
...datetime to datetime offset
We are in the process of upgrading our systems entirely. Making huge changes
to the hardware and software.
One change is going from SQL Server 2000 to 2008.
2008 has the new datetimeoffset data type, which for us could be a God-send.
In reading online however, it seems that the date time is always passed in
as a string. Is there no function that allows us to pass in a datetime and
an offset or do we actually have to do as I have seen all over the the
Internet and convert our dateTime values to a string, then concatonate the
offset and pass it in that way?
If we DON'T ...Need PivotChart x axis to cross Y axis at minimum Y axis value
I see an option to have x cross y at max y, but how can I say min y?
On Thu, 16 Aug 2007, in microsoft.public.excel.charting,
Aaron <Aaron@discussions.microsoft.com> said:
>I see an option to have x cross y at max y, but how can I say min y?
Jon Peltier taught me this one when I grumbled about the absence of a
"min y" check box. As it turns out, setting the x crossing to some
absurdly low figure like -999, far below the y axis minimum, does not
make the x axis disappear, or ignore the figure and stay at the default
zero. Instead it makes the x axis cross at, not below ...Comparing DateTime ?
i want to compare 2 DateTime. I use mktime and difftime for comparing.
If I compare 2 DateTime of same year it works fine but if both dates
are from different year , I get a wrong result.
any idea ?
>i want to compare 2 DateTime. I use mktime and difftime for comparing.
>If I compare 2 DateTime of same year it works fine but if both dates
>are from different year , I get a wrong result.
Wrong - how?
Give us a short code snippet example of what you're doing, the values
involved, and the results you're getting.
I get the date and time in the format ex : 10:45:4...VLOOKUP Value in Another Formula
Help, I've almost given up on this.
I'm using VLOOKUP in a certain cell. This formula returns a value from
another worksheet. I have this part down pat.
What I want to do is use the value returned from the cell having the
VLOOKUP formula in another cell so I can label the other cell a certain
way. My VLOOKUP formula is:
=IF(ISNA(VLOOKUP($G$18,'DJC2 ARN DATA'!$A$5:$U$3005,8,FALSE))," ",VLOOKUP
($G$18,'DJC2 ARN DATA'!$A$5:$U$3005,8,FALSE))
Let's say that this VLOOKUP formula is in cell G21.
I want to put "PRIMARY NUMBER" in cell G16 if t...Join query
I have a table that has the list of parts that go on a shop order to create
a part. On the table "Requirement_Details" I am selecting the Order, and
the Current Due Date. The table Transaction_History tells me the dates
something was Issued to the Order by the Transaction Code "I".
My problem is that I am looking for the last day that something was issued
to the shop order so if I select the MAX(dbo.Transaction_History.TNXDTE_15)
as below and nothing has been issued yet the query does not return Null or
for this field. I just don't get any records.
...Reference to cell with text is returning #VALUE
I have a cell that references a different cell that that consists of H9:J9
that is formated for and contains text.
The referenceing cell shows #VALUE instead of the actual text that is in the
Any ideas as to why this is happening? Any help would be appreciated.
What does your formula look like?
A simple link formula can only reference a single cell. Like this:
You can't reference a range of cells like this:
That is why you're getting the error.
Microsoft Excel MVP
"Jorist" <Jorist@discussions.microsoft.com> ...#Value
I need to add 2 cells (E24+E44), how can I achieve this
without getting #Value when zero value?
Check your data. The only way you should be getting
#Value is if a number is actually being interpreted as
text instead of a value. The data in either E24 or E44
has to be text instead of a value.
>I need to add 2 cells (E24+E44), how can I achieve this
>without getting #Value when zero value?
...unknown xslt function "format-dateTime"
Iam using Beta2 of VS2005 and getting the error "Error 4 'format-dateTime()'
is an unknown XSLT function" at the following line:
<xsl:value-of select="format-dateTime($pubDate,'[FNn] [D] [MNn] [Y]
[h]:[m01] [PN]')" />
how can I get around this?
> > Iam using Beta2 of VS2005 and getting the error "Error 4
> > is an unknown XSLT function" at the following line:
> format-dateTime() is XPath2.0/XSLT2.0/XQuery1.0 function. VS2005 only
> supports XPath1.0/XSLT 1.0. Use ms:format-date() exten...re: updating values
that works, but i'll need to add a lot of hidden feilds (20+/-)...
Is there another way (perhaps more efficient -if not as simple?)
("there's more than one way to skin a cat")
"Daryl S" <DarylS@discussions.microsoft.com> wrote in message
> Mark -
> Add the field [PresetOption] to the form. You can set the .visible
> to FALSE so the user won't see it. Then the code...xsd restriction on dateTime based on other dateTime
i'm trying to define an xml schema for validation with a restriction on
dateTime element based on another dateTime Element.
in English: end (dateTime) should be greater than or equal to begin
Is it possible to define such restrictions in XSD?
Joris van Lier
Joris van Lier wrote:
> i'm trying to define an xml schema for validation with a restriction on
> dateTime element based on another dateTime Element.
> in English: end (dateTime) should be greater than or equal to begin
> Is it possible to define such rest...Getting values from hyperlink
Does someone has an answer for the question below?
I have a cell (say A1) with the formula:
In the next cell, I would like to see the value that the cell pointed by the
hyperlink has. I tried:
But Excel gives a #REF error in the INDIRECT function.
Does someone have any idea on how can I solve this?
Thanks a lot
=indirect() won't work with closed workbooks.
Harlan Grove wrote a User Defined Function that can pull from a closed
http://groups.google.co.uk/groups?selm=ASAuc.4700%24H4.12%40w...log chart minor gridline values at tick marks
I have an XY scatter graph with log10 scales on both axes. How can I display
the minor gridline values at the axes tick marks? Currently, the axes
display only the power of 10 values (.1, 1, 10, etc...)
Excel does not allow for minor tick labels. However, you can add a dummy
XY series along each axis, with a point at each value where you want a
label. Hide the points (no line or marker) and add a data label that
shows the value. Position the label appropriately.
Peltier Technical Services, Inc.
ExcellerZero...Paste Values in linked Cells Only
I have a worksheet within a workbook that contains 1500 rows of data over 14
columns (the 12 months, plus to summary columns) - called the "Year to Date"
sheet. The YTD sheet has links to other worksheets within the same workbook.
This workbook is used as a roll forward workbook. The YTD sheet does not
change, but all other worksheets in the workbook get updated with the current
months data. In preparing for a new month, I have written a macro that
cleans up a lot of the data from the last month. The problem is that I want
the YTD worksheet...C# DateTime -> XML DateTime
Is there a method available to convert a C# DateTime format to XML DateTime
Have you looked at the XmlConvert class?
This posting is provided "AS IS" with no warranties, and confers no rights.
"Guy Dillen" <email@example.com> wrote in message
> Is there a method available to convert a C# DateTime format to XML
yes i have looked at this lass, but does this also work in the direction
"SQ...OnCtlColorDlg & return NULL
The MS documentation about WM_CTLCOLORDLG says:
"If an application processes this message, it must return the handle of
a brush. The system uses the brush to paint the background of the
The documentation about "CWnd::OnCtlColor" says something quite
similar. But then when the wizzard generates OnCtlColor the included
"TODO: Return a different brush if the default is not desired".
Now I wonder if really a brush MUST(!) be returned. Returning NULL
works. Nevertheless, that doesn't mean it's valid to do so.
Finally, if I can re...How do I prevent values from been multiplied by two or three?
I created a query to add values from two different tables. However, some of
the results are been multiplied by two and some of them by three. Is there
anything I need to do on the query itself to solve this calculation issue?
> I created a query to add values from two different tables. However,
> some of the results are been multiplied by two and some of them by
> three. Is there anything I need to do on the query itself to solve
> this calculation issue?
Show us the sql statement (switch your query to SQL View) an...Data Migration
I'm trying to migrate Sales History information into CRM. Invoices and
Invoice Lines are migrating and calculating the Extended value correctly. I'm
having a problem with Credit Notes. Credit Note Lines (where Price per unit <
0) are not migrating. I'm getting a "CrmIncidentUnknownError" in the
migration log for these lines.
The same error occurs for lines where Price per Unit = 0.
I've modified all the relevant value fields on the forms to allow minimum
values of -100,000,000.
I'm using Existing products and over-riding the Price each time.
I previously ...Value does not fill down (copy) properly.
I need to combine last name and first name fields, and am using this formula:
The B column is the last name, and C column is the first name.
My first glitch is, when I enter that formula, the forumula RESULT does not
display in the cell, only the formula itself.
My second glitch is, when I try to copy that formula down the rest of the
column of data, it should reflect the descending row numbers (ie., B3 & C3,
then B4 & C4, etc.). But only the C column increases accordingly, the B
column remains B2 and does not increase.
What, praytell, have I don...Timestamp value of GINA window presentation
I am currently troubleshooting slow logon times for a client with computer
infrastructure spread across the globe. Before I start invesagating I want to
create a baseline for logon times.
I have modified the clients logon script to record when it starts and when
it ends. I am using the computers tick count to record how long the computer
has been on, but realize that there is a space of time between the user
authenicating to the computer (control-alt-delete) and when the logon script
starts, if the customer turns his computer on and then leave for an extended
period of time I ...Remove the Formula but leave the value alone?
How easy a macro can be developed to remove the formula but leave the value
alone for a group of cells when a special condition matches like my example
I started with the formula below but realized that I can’t get the result I
wanted as to leave the value alone for F4 in the IF formula when the month
moving forward. The F4 value originally returned from the Vlookup result.
H4: =IF(H2=B1,VLOOKUP($C2,'Sale...NULL DATE
This works fine if there is a date in the textbox, but if the textbox is
empty or null, I get an error. This is part of an SQL statement. What can
I do if it's Null so that it still works.
Change you line to
This will return a zero legnth string instead of a null value, and will
allow your code to process it.
Note that if at any point you are comparing that field to Null, it will
fail, and you will have to compar...Null values and calculations
I have a null value that appears as a zero in a report as the control is
"=nZ([PRS TAT],0)". Yet, I am unable to deduct this zero value from a second
value. The field just comes out blank. Is this null zero a true zero or can
is not be used for calculations. If not, any suggestions on what I can use
What is the other calculation? Nz() returns a variant data type so you may
need to wrap it in Val()
Microsoft Access MVP
"Karina M ;)" wrote:
> I have a null value that appears as a zero in a report as the cont...