cannot copy same value twicei am sure this topic has been addressed but i cannot find anything on
a search
the problem is that the office clipboard will not copy a value which
is the same as one already copied
for example, if you are trying to copy: 2 3 4 5 5 6 7
it will copy the first instance of '5'
but when you try to copy the second instance it will just make a funny
noise and it will not copy the second '5' at all
now, obviously if you are trying to copy a long sequence of numbers,
to paste them into another location you want to copy all of these
numbers
[i use 'clipmate', an add on clip...
Value of non-numeric characters in a formulaAs pretty much an Excel newbie, why does Excel (97 and 2002) treat a
non-numeric character as having a value of 1 in a =PRODUCT formula? It
treats the same character as a 0 in a =SUM formula.
Make C1 =PRODUCT(A1,B1). Then, e.g put 5 in A1 and 3 in B1. C1 shows 15.
Fine. Now try putting 0 in B1. C1 is now 0. Fine again. Now try putting
an e or a comma in B1. C1 shows 5! Why?
Try the same thing with C1 = SUM(A1,B1). The result will be 8, 5, and 5.
TIA for the explanation. I'm sure it's there somewhere in the Help info,
but I can't see it.
--
Jeff
(Remove xxx from emai...
Select name from drop down and return associated valueHi
I have a spreadsheet where I need to return a value which is an
alphanumerical code but the users know only the name.
Example:
In worksheet A there are details of clients. If the clients
participate in a course we are required to record the course code not
the name.
Column C is where I want the value to appear. What i would like to
occur is that the user selects from a drop down list the name of the
course but on selection the value returned is the course code.
Is this possible?
Debbie
Are you saying that you want the user to select from a dropdown some
course name, but you wan...
UDF returning #VALUE! why?=con_check(0,2) ==> #VALUE!
Function con_check(con_old As Integer, con_now As Integer)
Dim i As Integer
Dim targ As Integer
Dim hit As Integer
Dim roll As Integer
Dim con_count As Integer
con_count = con_now - con_old
hit = con_old
If con_old < con_now Then
For i = 1 To con_count
hit = hit + 1
If hit < 3 Then
targ = 1 + hit
End If
If (3 < hit) And (hit < 6) Then
targ = hit + 6
End If
If hit > 5 Then
con_check = False
Exit Function
...
Counting values in arraysHow do I get from a vertical single-field array to an array of the sam
size, each original value converted to a count of that value in th
original array?
E.g.,
1 -> 3
0 -> 2
1 -> 3
0 -> 2
1 -> 3
2 -> 1
I start with a calculated array, not a cell range, so (I'm assuming
{=COUNTIF(Array,Array)} won't work.
Steve Przyborski
Boston, Mass
--
Message posted from http://www.ExcelForum.com
Two methods using an array formula and countif
Assuming that the data starts in A13. Type the following
and drag down - the range w...
need vlookup function to return the row numberI have a one column list of data (around 3,000 items) - and I am using
the vlookup function to determine if an item is in that list using
something like the formula below:
=if(iserror(vlookup(A1,D1:D3000,1,false)),"not in list","in list")
I would like to know if I can have this function return the cell
address or row number to indicate the location of the item in the list
- is this possible?
Thank you for your time and assistance
Use =match() instead.
=if(isnumber(match(a1,d1:d3000,0)),match(a1,d1:d3000,0),"No match")
=match() will either return an error (if...
Show zero valuesWhen a formula returns a zero value, the zero value is not being
displayed.
I have reviewed the following:
- conditional formatting
- zero values are checked (Tools/Options/View)
- stepped through my VB code (it inserts formulae based on a Worksheet
Change event)
- locked and hidden values unchecked with and without protection (this
should have no effect when the sheet is unprotected)
Are there any other circumstances that result in a zero value not being
displayed? Are there any other reasons for this occurrence?
Cheers
John
Check also normal cell formatting! there are formats that hide ...
Solver? Maximum number of variables?Is there a maximum number of adjustable variables that Solver can work with
at any one time ... or do I have a memory limitation?
Hi,
It's 200, have a look here
http://office.microsoft.com/en-us/excel/HP051992911033.aspx
Mike
"TK2301" wrote:
> Is there a maximum number of adjustable variables that Solver can work with
> at any one time ... or do I have a memory limitation?
>
>
Mike H wrote:
> Hi,
>
> It's 200, have a look here
>
> http://office.microsoft.com/en-us/excel/HP051992911033.aspx
>
> Mike
>...
Ct:1 Appears in Left Join for all values rather than selected valuHi,
I have a table called tblCodeBlue that has values such as AcctNumber, Unit,
and CodeBlueDate. I created a query called qryCodeBlue1 with AccoutNumber,
Unit, Month (Calculated using CodeBlueDate). I am importing my data into
another system so I need a numeric value so I also added Ct:1 for Count with
a value of 1. I also have a table called tblMonths that has Month and Unit
as fields. There are eight units in our facility and we report on data going
back to 1/1/08 so this table has eight records for each month from 1/1/08 to
12/1/10. I created a query called qryCodeBlue2...
Sum variable range of column entries in offset cellHi chaps,
I’m trying to look up a column of values from an offset cell and sum the
column values (or depending on formula used, work out the average, median or
mode of the data range in the column).
The hard bit is I need to specify (using a variable number value in another
cell) how many cells up from the bottom of the column are included in my
calculations.
Here’s a simplified example to (hopefully) try and explain more clearly:
A B C D E F G
1 5
2 2 5
3 0
4 3
5 1
6 ...
Creating ID with numbers and textHi guys,
I'm pretty new to Access but I need some help.
I need to create an ID number using first 3 letters of a name followed by 3
numbers that create a unique ID.
Is there a way of doing this in the expression builder?
Many thanks,
I don't think so. You would have to use some VBA.
In a case like this, it would be better to use two fields; one for the
letters and another for the numbers. You can create an index on the two
fields.
To get the next available number where the 3 letters are the same, you can
do something like this:
Private Function NextIdNumber() As Long
N...
I need a formula to sum column b if column a is between two datesI have an excel spreadsheet with employees time off. I need a formula that
will add column b if column a is betwee two dates. For example: if column a
is between 9/22/04 and 9/21/05 then add column b. I have tried all different
formluas but can't get this to work.
...
Increase size of pie chart within control?Is there a way to increase the size of a pie chart within its control?
I have a limited amount of space in which to display the chart, so the
overall size of the control is restricted. But within it there's quite a lot
of "wasted" white space and I would like to make the pie itself larger and
more legible. Can do, or not?
Many thanks
CW
In design mode, double-click on the chart within the control to select it
for editing. Then use the sizing handles to resize the chart area to the
size of the control; this does not have to be square. Click on the plot
area to select it ...
Duplicate value error although there is no duplicate valueI have a table where I have assigned a primary key and index for a
specific number.
The db worked for approx. 1/2 year and today I get a duplicate value
error when I try to create a specific number in the table (in this
case, the number is T-1379-1).
The table prohibits duplicate values and the number T-1380-1 can be
inserted to the table without a problem. I started at (or the last
good number was) T-1378-1.
I've checked the complete table, but couldn't find number T-1379-1.
Can anyone help?
On Sep 11, 9:46 am, Mike <michael.re...@gmail.com> wrote:
> I have a table where ...
formula not replaced by value
Friends
Some time formula will not be replaced by values
as shown below, even after hitting 'ENTER'
What may be the reason
A B C D
+BS ^ =CONCATENATE(A1,(REPT(C1,31-
LEN(A1))),B1)
SUBID 000000000013 ^
Thank you
Babu
------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/
Your cell with the formula is probably formatted
as "text". Try to change it to at least general.
(J...
New to ExcelI am trying to learn how to add, substract, multiply, and divide columns in
an Excel spreadsheet. I know how to add the contents of a column down that
column but I cannot understand the instructions of how to add columns across
the spreadsheet. I want to be able to add columns to get a result
(P+Q=R...ie OBP+SLG=OPS) as well as multiply, divide and substract. Anyone
offering assistance will be greatly appreciated.
Hi Blake
To add columns, you still need a cell reference, not just a column.
eg, In C1 put =A1+B1
to multiply =A1*B1
To Subtract =A1-B1
To Divide ...
How do you not include 0 value data on a graph?I have a chart made that is formulated so that data fills in automatically
every month. I then have a graph attached that plots the data. My problem is
that when there is no data for that month then the graph plots a continuous
line that shows 0 after the real data.
Is there anything that I can put in the formula so that if there is no value
or 0 value it won't plot it on the graph?
Hope this question makes sense!
Absolutely, use NA() if you don't want to graph the point.
--
HTH,
Barb Reinhardt
"bergie" wrote:
> I have a chart made that is formulated ...
Looking Up Values On Different Sheets
is it possible to look up values from different sheets ???
*for example: *
i have created a page called *print-out* and this is where i want th
findings to be put. i need something that will look up all values tha
say *10.00am* and copy columns A-D onto the printout sheet. But thes
are all on differnt sheets.
Sheet 1 Sheet 2 Sheet 3 Sheet 4
10.00am 10.20am 10.00am 10.00am
10.20am 10.40am 10.40am 10.20am
10.40am
do you understand ????
this if for a tournament scheduler where i need to print out al
fixtures star...
Query SQL server db with cell value parameterI want to supply criteria value using a cell on the spreadsheet to contain a
date.
Can't seem to figure out how to do this. On the Connections - Properties -
Definition dialog, "Parameters" is grayed out.
...
SUM for logical valuesUsing Excel 2002 SP3...
If I type logical values into the formula, like this: =SUM(TRUE, TRUE,
TRUE), my formula returns the value 3.
If I reference cells containing logical values, like this =SUM(A1:A3), my
formula returns 0.
Does anybody know why there is a difference?
--
tj
Try:
=A1+A2+A3
--
Regards,
RD
---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"tjtjjtjt" <tjtjjtjt@discussions.microsoft.c...
Drivers for percentage increaseI am trying to set up a driver to include in my budget profile so that my
managers can select the percentage from a driver and see on each budget line
what the outcome will be i.e
staff costs may be calculated at 150,000 but if I used my driver to
calculate an increase of 3.5% that would change the staff costs to 155,250.
Can anyone help?
Regards
Jo
I am not sure what you question is but this may help:-
In cell A 2 I have 150,000
In cell B 2 I have 3.5%
In cell C 2 I have:-
=(A2*B2)+A2
- and this returns 155,250 to cell C 2.
If my comments have helped please h...
pivot table with selection values not included in the base dataHi,
Is there a way to create a pivot table which has rows/column selection
values which are not included in the base data?
I have dynamic base data, each row consisting of ProjectName , ProcedureName
and %compliance. The ProjectsNames are selected from a dropdown list of 10
possible projects.
I have created a pivotTable from the base data, with projectName on the
y-axis and ProcedureName on the x-axis, and %compliance in the middle.
If I only have rows of base data with say 5 of the possible 10 ProjectNames,
on the pivotTable i only then have the option of choosing between these 5
pr...
What defines number or textHi folks,
I have the following �50...it is defined as a number without me doing
anything. I have $50 ...it is defined as text without me doing anything. I
have 10% sometimes (depending on the pc) it is a number and sometimes text,
similarly 10,000. Now I assume that the �50 is recognised as a number
because of my windows settings...but the rest? Trying to get the $50
recognised as currency is possible....if I delete the original $50, format
the cell as currency, and then re-enter the 50. I would love to know what
deifnes the figure as text or number, and how I can move between the two
wi...
sending empty value to optional parameterIf you have a function that accepts an optional parameter:
Function select_live_nodes(ByVal sf_node_parent As Integer, Optional
ByVal node_orderby As String =3D "my_default_value=94)
I know you can call the function with
X =3D select_live_nodes(2)
Or
X =3D select_live_nodes(2,=94another order=94)
But how do I send a varialbe as the optional parameter that might or
might not contain a value?
X =3D select_live_nodes(2,myvariable)
Even if =91myvarialbe=92 is empty the function does not resort to its
default value, "my_default_value=94
I=92ve tried sending dbn...
to increase quantity
hello everybody, i have a question: i want to use project to calculate
the delivery time of a machine component. I know the bill of material
and the time of every step (manufacturing, mounting, etc.). I don't have
problem to create a gantt for this item: but i have problem when i want
to create a gantt for a number greater than one of my item. I think that
i have to use a function that increase the duration of each step of the
process, but i don't know how. Please help me.
thank you
--
sentenza
------------------------------------------------------------------------
s...