Stock, Bond, Portfolio Monitoring
What is the proceedure - or formula - used to calculate the gain/loss in an
investment portfolio; so that the resulting performance figures reflect the
same gain/loss petrcentages for the Dow Industrialo Average and other such
benchmarks? Tag: in excel can not input chinese character Tag: 134560
Sumproduct Problem
Hello,
I have exported a table from Access for repairs on various types of
equipment. I want to calculate the reapirs for each unit (each has a
specific unit number) for each year of its existance.There are three
columns: ReapirDate, UnitNumber, Cost. I have named these three
ranges. I have created a second worksheet and have to boxes that the
user can fill in to narrow the search for a particular year (A2) and
unit number (A3). What I want to do is sum the repairs for that unit
and year. Here's what I did:
=SUMPRODUCT((YEAR(RepairDate)=A2)*(UnitNumber=A3)*Cost)
But this always returns a value of zero even when I know it's not
zero. My UnitNumber field was a "text" field in Access because some
of the units have a letter behind them (ie) 75A. However, I was
hoping to create something as there are over 15000 records. I have
absolutely no clue how to build a pivot table so I was hoping to
create something with a worksheet function. Any help/suggestions
would be greatly appreciated.
Thanks in Advance,
Joe Tag: in excel can not input chinese character Tag: 134559
Strange colours im windows
Whenever excel shows a window to ask me something the text appears as black
on gray and the rest of the inside of the window is white. This makes the
window look wrong. This only appears in excel 2002 and not in word or
powerpoint. If I login as another user of the same computer the problem has
disappeared. I've tried reinstalling office and changing the screen. This
really puzzles me and I hope someone can hel me with this. Tag: in excel can not input chinese character Tag: 134552
exporting to Access
Let me preface this question by saying no know virtually nothing about Excel
- I am an Access programmer - so please dumb down your answer for a newbie.
I have an Excel file supplied by my user. It started life (I think) as a
.pdf file that they somehow converted to Excel worksheet. The original file
must have been a print file because the page headers, page numbers etc are in
the cells with the data. It looks like they took a report and split it into
columns. If I try to import it to Access as an Excel file I get an error
message saying "The Microsoft Jet database engine could not find the object
"July 92"s$" (July 92 is the name of the first workbook page.) When I export
the Excel file as text and import the text file to Access, I get all the page
headings, page numbers etc. Does anyone have any idea of how I can get JUST
THE DATA from the original file from any of the formats - XL, .pdf. or .txt -
into an Access database? Is there a way to export only certain row or columns
from Excel? I'm lost.
TIA,
Lynne Tag: in excel can not input chinese character Tag: 134551
Array Problem
Hi,
I can't get my array formula to work properly. I see this problem in both
Excel 2003 and 2007.
The data fields are as follows:
A1: 1
A2: 2000
B1: GB
B2: MB
The formula is: {=SUM(IF(B1:B2="MB",A1:A2/1000,A1:A2))}
The result is: 3 (as it should be)
But if I should change the values in cell A1:A2 from numeric's to text, the
result should change to zero as text can not be added. But this is not what
happens. Instead, cell A1 is not added (as it should not be), but cell A2
is somehow treated as a numeric and is added to the result!
Example:
A1: '1
A2: '2000
(note the apostrophe thus making these cells text).
The result here is: 2 (it should be zero)
Any idea what I'm doing wrong?
--
Thanks,
Bob Tag: in excel can not input chinese character Tag: 134543
VLOOKUP challenge
Hi
I am building a standard report template where the format does not
alter. I pull information from another table into this standard
report. The trouble is that the report I pull from changes so when I
have a formula =VLOOKUP(A674,B11:AW673,11,0) and there is no
information I get a #n/a error. I would like to have a 0 or blank
returned to enable totalling to work as some cells have figures in
them whilst others have n/a which prevents
=SUM(F696:F697) etc from working.
Cheers
Brian Tag: in excel can not input chinese character Tag: 134536
easily changing a linked reference
Hi
I am building a report spreadsheet that links into and pulls
information from another spreadsheet i.e LabourWk49.xls.
I have quite a lot of links into LabourWk49.xls and wondered if there
was an easy way of wholesale updating the weeks so the links can be
changed to LabourWk50.xls. Is there a macro or something that could do
this easily. I want people to be able to change the links easily.
Much Appreciated
Brian Tag: in excel can not input chinese character Tag: 134535
printing problem
I currently have an excel 2003 workbook that I set up to keep the old
checkbook current.
set up like this
a b c d e
date to whom chk amt dep amt tot
date is formated as full date
to whom Is text
chk amount formatted as currency as well as deposit and total
the workbook is copied over to another workbook for the monthly totals.
I started another year on Jan 01 08...I now have a printing problem. I have
one single line that is transposed from page 1 to page 2 in the print
preview and prints.
it is in row 8 (which is the 4th calculated row- with the title and all-I
have a title area and headers).
for some reason this row is brought down as the first row in page 2....I
have looked at the printing setup and there is nothing special besides I do
have a header and footer on the pages. the header consists of title and
footer is of page 1 of ? and so forth...
I have tried to change the header and footer...I even removed them to no
avail. for some reason this row is brought down only for printing....
any help would be appreciated
dlars80 Tag: in excel can not input chinese character Tag: 134528
Printing questions (partII)
I have this spreadsheet with a calendar for a whole year.
Each month is on a separate tab and covers a whole page when printed.
I would like these features if they are possible.
1- have a button on each page that prints the actual month (page that
is been looked at), and the following month.
So if somebody is looking at the month of May, and clicks the button,
automatically the June, July, August, Sep... and so on must be
printed.
This means that the button has to be changed for every month, but
that's no problem. I need a macro that prints more than one sheet.
2- this one is tricky: if it is possible the prints should be with
multiple months on every printed page.
To save our trees, it would be fine that when somebody wants to print
all months starting from may, on every printed page two or more months
are printed.
This means that excel should reduce the size of the prints so more
than one tab fits on one page.
This also means that if two pages are printed, the print setup should
change the print direction from landscape to portrait..
I know, this is much, but maybe there is an solution... Tag: in excel can not input chinese character Tag: 134516
XL2007: SLOPE vs. LINEST; how can this be?
I have a formula in a cell. =SLOPE(ref1, ref2). It compultes a numeric
result with no complaint, which appears to be reasonable/valid to me.
Now, if i edit the function name ONLY and change it to LINEST (stuff inside
the () stays the same), i get #VALUE.
(The only reason i did it that way was to make absolutely my reference was
the same).
How can this be?? Anyone else ever see this? Anything to do about it? (I
need some of the "additonal regression statistics" that LINEST provides).
Thanks,
tom Tag: in excel can not input chinese character Tag: 134514
VBA: Moving data from another worksheet
Hello,
Can someone please help. I am trying to understand how VBA can be used
to get data from one sheet to another. I have some code that works but
I think it could be a bit better.
With regard to the code below, the queries I have are as follows:-
Dim XR
Why can't/shouldn't this be defined as a range?
Dim x
Should this be a variant?
XR = Sheets(WS1).Columns(FromCol)
Might be better to define a range since we have lastrow but how?
For Each x In XR
Why doesn't 'For Each x In Sheets(WS1).Columns(FromCol)' work,
since XR was set to be Sheets(WS1).Columns(FromCol) anyway!
Sheets(WS2).Cells(i, ToCol).Value = x ^ 2
Why don't these work:
YR.Cells(i, ToCol).Value = x^2
YR.Row(i).Value = x^2
What variations that references YR would work?
Thanks for all constructive advice given.
Hal
Code follows....
'---------8<---------8<---------8<---------8<---------8<---------8<---------
'Sheet1 has a list of numbers in column "A"
'----------------------------------------------------------
Sub GenData()
Call GenSquares("Sheet1", "A", "Sheet2", "D")
End Sub
'----------------------------------------------------------
Private Sub GenSquares(WS1, FromCol, WS2, ToCol)
Dim XR
Dim YR
Dim x
Dim i As Integer
Dim lastrow As Integer
lastrow = Sheets(WS1).UsedRange.Rows.Count
XR = Sheets(WS1).Columns(FromCol)
YR = Sheets(WS2).Columns(ToCol)
i = 1
For Each x In XR
If i > lastrow Then Exit For
Sheets(WS2).Cells(i, ToCol).Value = x ^ 2
i = i + 1
Next x
End Sub
'----------------------------------------------------------
'---------8<---------8<---------8<---------8<---------8<---------8<--------- Tag: in excel can not input chinese character Tag: 134513
Right Clicking a row or a cell in Excel cause Excel to crash
This problem just started over night. It will happen with any mouse
(Apple Wireless or Microsoft). It also happens when the control key
and left mouse click is used to obtain the secondary menu. Excel
crashes before the secondary menu can appear. Right Click and Control
Left Click works fine in other applications.
All worked fine 8 hours earlier Tag: in excel can not input chinese character Tag: 134509
Excel Help Menu shortcut
Can I place a shortcut icon to the Excel Help menu on my desktop?
If so, how?
(Perhaps, Excel must be opened for this to work?)
Thanks,
FLKulchar Tag: in excel can not input chinese character Tag: 134508
Beginner's question on adding up and plotting multiple time series
Hello group,
I have the feeling that the answer to this question is going to make
me feel silly, but I really can't seem to work it out using either the
Excel help file or online documentation. I must be looking in the
wrong places!
I have three time series, of three bank accounts. I can plot each
individually without problem, and I can also get one graph with three
lines. So far so good. But what I would like to do is get a graph with
one line, representing the combined amount on the three bank accounts
over time (i.e., adding up the three time series).
Simply adding up the three data series into a fourth one and then plot
it doesn't seem to work because the time points are independent (e.g.,
for one account I have transactions on 2 and 8 December, on another
one on 5 and 6 December).
And in the chart wizard I can't seem to let the time series be added
up.
Any suggestions?
Richard Tag: in excel can not input chinese character Tag: 134504
Print range question
I've made a spreadsheet with several tabs.
In this sheet i've selected a certain print range so only the
information i need gets printed.
But when i try to print the sheet, or on the print preview everything
on the sheet gets printed..
I thought that only things inside the print range (visible on the
sheet with a thin line), would be printed...
Is there a way that i can define wich range only can be printed
besides the print range option?
I have made this sheet with Excel2007 and converted it to Excel2003.
Is there something gone wrong with this conversion? Tag: in excel can not input chinese character Tag: 134496
Copy range from one workbook to another
On a monthly basis, I recieve about 30 workbooks and have to consolidate
them into a single workbook to forward to my prime contractor. This
involves copying subsections of each of these workbooks into the same
location (sheet/rows) in my consolidated workbook. I've got code working
that loops through all of the workbooks in the same folder as my
consolidated workbook, and then search those workbooks (3 sheets) for
non-zero values in a particular row/column combination. This function
FindNonZero( ) works great, returns a True/False value to indicate whether
it found the non-zero value, and also gives me the worksheet and row pointer
for the non-zero values.
The portion of my code below works great if there is only a single non-zero
value in the source workbook, but if the code finds a second non-zero value
I get a Runtime Error '1004' Application-defined or object-defined error
when the "Rng.Select" statement is executed, and my code stops running. I
don't know whether it is because the source workbook (sWbk) already has a
range selected (I have not figured out how to un-select a range
programmatically) and Excel won't allow that, or whether there is something
else wrong with my methodology. Would appreciate any assistance or other
recommendations.
Thanks, Dale
While FindNonZero(sWbk, intWorksheet, intRowPointer) = True
Debug.Print sWbk.Name, intWorksheet, intRowPointer,
sWbk.Sheets(intWorksheet).Cells(intRowPointer, 12)
strRange = "A" & (intRowPointer - 9) & ":K" & (intRowPointer - 1)
Set rng = sWbk.Worksheets(intWorksheet).Range(strRange)
rng.Select
Selection.Copy
aWbk.Sheets(intWorksheet).Activate
aWbk.Sheets(intWorksheet).Range("A" & (intRowPointer - 9)).Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Wend Tag: in excel can not input chinese character Tag: 134474
Access -> Excel large Copy Paste fails
I have an unusual situation where a user is attempting to copy a large
amount of data from Access 2003 SP2 -> Excel 2003 SP2 on a windows 2003 sp2
(i386) server. Specifically the dataset it 15,683 rows of data. I can see
the copy run as the blue status bar runs across indicating the copy process
is running for 8-10 seconds. When it finishes I paste into a blank new
spreadsheet in Excel and it runs for a some time - maybe 30-45 seconds - an
error pops up "Microsoft Office Excel cannot pasted the data". I only have
2003 server and office installed along with the latest Microsoft updates (no
other apps at all). When I try this copy/paste in the same version of
access/excel in Windows XP SP2 OS it works fine so I'm guessing there may be
some buffer parameter in the XP OS that makes that clipboard able to process
more data. I tried some variations of this and it fails on office 2007 as
well. If I use the export to excel feature in Access it works OK but in
this case it would be better operationally if I could get cut/paste to work.
Any information to solve this would be helpful. Thanks! Tag: in excel can not input chinese character Tag: 134472
Inconsistent formulas
Hi!
During development of my school project I have found a bug (in my opinion).
Writing formula 5-10^2 I am getting as expected -95, however changing order
of summation to -10^2 + 5 I am getting 105 (which is equivalent to (-10)^2 +
5 not as intended -(10^2) + 5). I think it should be unified - solving this
problem is not easy, becuase both formulas should give the same value.
Thanks
Piotrek Tag: in excel can not input chinese character Tag: 134458
Running macros or functions?
First of all i want to thank people who come here and help others.
I have posted a few questions and have many nice people help me.
Now for my question: I am confused. I just started a few days ago
learning to do some VBA. I noticed that when i create a macro in VBA i
see VBA makes the following code entries in the code window:
sub name1()
end sub
The name1 is the macro name that i assign when invoking the create
macro button in the macro dialog box. But when i change the code to,
Function name() the macro name that i created disappears? Now i want a
return value to go into a cell for use later on so i feel i need a
function and not a subroutine. The only other way might be to use a
subroutine but write a value back to a cell of my choosing, any
thoughts on this as far as best practice for my situation? You can
tell i am really new at this, sorry for any dumb questions.
Andrew Tag: in excel can not input chinese character Tag: 134454
help again
Looking for a formula where cell E1 will add cell A1 to the lowest total
from cell b1 c1 or d1, then the same with the formula but this time with
the highest value.
Thanks again to all who reply. Tag: in excel can not input chinese character Tag: 134453
Help with compile error?
I wonder if some one can tell me what is wrong with my code?
I am having problems with the first line? I looked online and i
thought my syntax was correct?
Andrew
Function Process1(Reactant As Real, Yield As Real) As Real
Dim Reactant As Real
Dim Yield As Real
Set Cresult2 = 0
'Do
Set Reactant = Workbooks("Sayaka").Sheets("Sheet1").Range("D34")
Set Yield = Workbooks("Sayaka").Sheets("Sheet1").Range("D34")
Set Aresult = ((Reactant / 100) * (1 - Yield)) * 100
Set Bresult = ((Reactant / 100) * (1 - Yield)) * 200
Set Cresult = ((Reactant / 100) * (Yield)) * 300
.......
End Function Tag: in excel can not input chinese character Tag: 134451
How to do math on time
I want to create a time card in excel (2003).
4 time fields (Time in morning, Time out lunch. Time in after lunch, Time
out end of day)
The next field (column) needs to be able to total the actually hours worked.
Needs to be able to deal with hours & minutes.
I might even want to put a button that will "stamp" the current time in one
of the above fields.
Any help here will be appreciated.
Thanks in advance
dave Tag: in excel can not input chinese character Tag: 134448
how to make this formula variable
Can anyone help with this.
For c = 1 to 4
cells(1,c).formula="=counta(a2:a10)"
next c
this example places the counta formula in a1,b1,c1 and d1.
How do I make the formula variable, so it works on the specific column.
It would be nice if I could use the same variable( c ) as the column
reference in the counta formula . Kind of stuck
Any help would be appreciated
KB
--
KWB Tag: in excel can not input chinese character Tag: 134439
Changing data orientation
I have a worksheet with 4 columns.
Column A = Product Family
Column B = SKU's
Column C = Attribute Metric
Column D = Attribute Value
Column B might have a SKU going down for 40 consecutive rows with 40
different attribute metrics e.g. manufacturer, height, width, depth,
UPC, color, finish etc. etc.
and the actual values for it in column D like Keter, 12", 36", 15",
0123456789, black, steel etc etc..
Not all SKU's have 40 metrics. Some might have just 5 to 10, while
others might have 55 to 60.
What I'm trying to do is have one row per SKU. The actual names for
the metrics will be the subsequent columns headings.
Column A = Prod Family
Column B = SKU
Column C = Manufacturer
Column D = Color
.... and so on and so forth
For each SKU the information will be tabulated by columns.
A pivot table would have been an excellent solution for my data
orientation problem but the pivot table will not allow any text
information in the data area.
I hope this explains the issue I'm facing. And, as always, any help is
appreciated. Tag: in excel can not input chinese character Tag: 134438
Need cell Color formula
Can anyone tell me of a formula I can use that will tell me if an
adjacent cell is a certain color to have the result be "True" and if
there is no color the result be "False"?
Thanks,
Steve Tag: in excel can not input chinese character Tag: 134437
cannot use auditing tools
i have an excel 2002 spreadsheet that is not protected, but the
auditing tools are unusable [faded out]. why?
Tonso Tag: in excel can not input chinese character Tag: 134431
removing comma in data string
I have a string of numbers, but they've been entered as:
357,041
332,224
341,038
etc.
Basically I'd just like to convert them to numbers so I can add,
subtract, etc. The problem is that as they are right now, they are
stored as text, and I can't figure out how to fix that (format cells >
number does nothing).
Please help. Tag: in excel can not input chinese character Tag: 134414
who last opened a file
I read on someones site that you can open notepad file and it will
show who last used a particular excel file. can anyone telll me how
its done, or where to find that article?
thanks,
tonso Tag: in excel can not input chinese character Tag: 134411
Excel 2007 move/copy sheet error
This one error in Excel 2007 makes constantly makes me return to using Excel
2003:
"Excel cannot insert the sheet into the destination workbook, because it
contains fewer rows and columns than the source workbook."
WHAT!
This is absurd. I've been through Excel 3 until now and this has never
occurred previously. This limitation severely limits how I operate DAILY
with Excel. I realize some of the workbooks I'm using were originally
created with an older version of Excel than 2007 (say v2003 or even v5.x)
but it still makes no sense.
I've had to rewrite macros (that are using Excel 5 - 2003 based templates to
import data into) and calculate which sheet file is LARGER to import(move or
copy a sheet) to a SMALLER file! This makes no sense. Note that all
templates are NON 2007 Excel based and I'm running Excel 2007 in
compatibility mode and saving that way also.
Other than this work around I've had no luck on a solution.
Prior versions of Excel handled row changes using the move/copy sheet. Max
columns was consistent between prior until Excel 2007.
Excel 5 had a 16,384row / 256column limit,
Excel 97-2003 had a 65,536row / 256column limit,
Excel 2007 has 1,048,576row / 16,384column limit.
It seems as if Excel 2007 with its 16,384 column limit should EASILY import
'smaller' sheets into 'larger' ones, especially using older 256 column
limited sheets. Excel 2007 wants you to create a blank sheet and copy the
data from the other sheet. Problem is I have many defined ranges that I
don't want to re-define. It may be convenient for Excel 2007 but how can
this be making things easier for the end user?
Has anyone else been this upset by this?
Terry B
P.S. The ribbon menu was not for me either! Tag: in excel can not input chinese character Tag: 134405
how to convert a cell value into boolean
Hi.
I'm facing up the following issue: in a sheet there's a matrix of
boolean values (TRUE, FALSE) that I have to read/write via vba.
Sometimes (not deterministically), a statement like:
if Cells(2,4).value then
' do something
else
' do something else
end if
returns a type mismatch because it reads the cell as string and not as
boolean (obviously in Cells(2,4) there is a TRUE value).
This behaviour doesn't always happen, only sometimes (non
deterministically, as previously said).
The solution that I've thought to is to force the format of the cell
as boolean, bu I don't know how.
Any ideas? or anything else?
unfortunately the application must be language independent, so I
cannot use a comparison like
if Cells(2,4).value = "TRUE" then ...
Moreover, I would like not ot use parsing function (like CBool)
because the performance get worse and worse, and this is another
issue...
thank u very much
bye
CRI Tag: in excel can not input chinese character Tag: 134404
Linking Dialog box content with cells in "regular" sheet
How to link data in Edit box (from Dialog caption) with exact cells in
"regulal" Sheets? Or is there another way to link exact content from Dialog
sheet to normal sheet? For example, if I have number 200 in Dialog, I need
that exact number 200 on another ("regular") sheet to preform calculations
with it. thnx Tag: in excel can not input chinese character Tag: 134400
Re: Excel spreadsheets over WAN
Forgot to add, I should note a contributing factor to the time it takes for
the spreadsheet to load from the remote file server is the 52 inherent
worksheets (representing 52 weeks in a year). If I create a new spreadsheet
in the same folder with only a couple of worksheets, it opens with no
noticeable delay (because it's a smaller file). So, I was still wondering if
there are any settings within Excel that changes the way the app handles
spreadsheets over a network?
"Roger Rabbit" <roger@rabbit.com> wrote in message news:...
>I have a user that tries to load a 6.24MB Excel spreadsheet that takes "an
>excessive amount of time to load". The spreadsheet in question is stored on
>a remote file server on a different subnet. I already know that, in this
>particular case, the 2Mb WAN link (MPLS circuit) connecting the two sites
>is the bottleneck, that's a no brainer, but I was wondering if there were
>any settings within Excel itself that affect the way the app handles
>spreadsheets over a network?
>
> TIA
> Tag: in excel can not input chinese character Tag: 134390
Excel spreadsheets over WAN
I have a user that tries to load a 6.24MB Excel spreadsheet that takes "an
excessive amount of time to load". The spreadsheet in question is stored on
a remote file server on a different subnet. I already know that, in this
particular case, the 2Mb WAN link (MPLS circuit) connecting the two sites is
the bottleneck, that's a no brainer, but I was wondering if there were any
settings within Excel itself that affect the way the app handles
spreadsheets over a network?
TIA Tag: in excel can not input chinese character Tag: 134389
Excel Chart Help
Hello Everyone,
I've been tasked with updating a 14 year old excel macro running on a
15 year old mac :(. I'm running into problems with the replicating
the charts.
Heres an example:
http://img99.imageshack.us/my.php?image=0103080255gp2.jpg
(Sorry about the quality, camera phone...)
Each group represents a different area of our state and each group has
its own average line. I'm having 2 problems. Grouping the data from
each area together. When I try to set each area up as a series it
groups the first entry of each area together then the second entry for
each area and so on.
Example:
http://img219.imageshack.us/img219/7544/newpicturexw5.png
Then once I get them grouped I need to make an average line for each
group. I must have been searching Google for 3 hours and I still
haven't found anything.
Any help would be great!
Thanks
-Mike Tag: in excel can not input chinese character Tag: 134385
Logical Formula
I need to check two conditions in a cell and then return the value if true or
false.
I am using the IF function, but not getting thru the second logical
condition to be applied.
Thanks in advance
Mustafa Tag: in excel can not input chinese character Tag: 134384
Start Time and End time it maybe for 1 day or it maybe for 2 days
Here is my sheet. I go out Jan 1 @21:00 and get back Jan 2 @09:00 then
the next day out @09:00 and back in @21:00. Is there a way to write a
formula that will do nothing if C2 is empty then go to the next Day
( C3 ) and count the hours for D3? at the same time if i was to go
out and back in like cell B4 on the same day. So i need to have a
formula to work for both in one cell and have it choose which one to
use. Not sure how i would put it. Should i use IF, OR, AND.
this one has me thinking. Who knows it just might be simple. Any
ideas????
A B C D
1 Date Time out Time In Hours
2 Jan-01 21:00
3 Jan-02 9:00 12:00
4 Jan-03 9:00 21:00 12:00
5 Jan-04 12:00
6 Jan-05 10:00 22:00
Thank you
Leo L'Hirondelle Tag: in excel can not input chinese character Tag: 134381
Save XLS as MHT - 'on screen' size
I got an xls file as I wanted, I then "Set Print Area" to be A1- I42. That
looks good when I say print page, so when all done, I did Save As -
Model.mht and closed Excel 2003.
When I open Model.mht, IE6 opens , BUT the info does NOT take up the
entire screen. I can only see A1-G23 and there is a white space all around
"the excel info". Maybe 1 inch at top, 2 inches on each side and 1 inch on
the bottom.
I can scroll to the right and down, but why do I have to ?
What is it about Excel save in webpage format that messes things up ?
Why doesn't the data fill the screen as much as it can ?
Ideally I'd "stretch" the "size of the table" to fill the screen , but how
?
Thanks,
Bobb Tag: in excel can not input chinese character Tag: 134371
average/trendline in chart
Hello group,
I'd like some help drawing a trendline in Excel. We have price datasets
going back five years that we want to chart using a line, with an
accompanying trendline. The trendline should be a moving average that uses
the same month for each year to determine the average. For example. If
January 2007's price is "5" and the previous four years' January values were
4, 4, 5, 5 (for an average of 4.5), then 4.5 should be the trendline value
for January 2007.
I'm only finding options in the chart wizard and tools for the standard
moving average of a given range (such as x months).
Please help!
DC G Tag: in excel can not input chinese character Tag: 134368
Microsoft Query Crashes!
Hi,
I'm using Excel 2000 on Windows 2000.
trying to create a new database query and in the status bar it says:
"waiting for data to be returned from microsoft query" and hangs
What's happening?!
Regards
Stefano Tag: in excel can not input chinese character Tag: 134367
Functions and worksheets, passing arguments to functions?
I just got a book called, Excel for Chemists. But it didnt tell me
something i want to know.
I wonder if someone out there can help me?
Here is the problem, say that i have put some data into a worksheet.
A1 cell = XValues and A2 cell = YValues.
If i have a Function that takes 2 arguments below how do i write code
to pass, XValues, Yvalues to the function Deming?
Example below:
Function Deming(XValues, Yvalues)
Dim MeanX(), MeanY()
'Get number of cells to use in calculation loop
Ncells = XValues.Count
ReDim MeanX(Ncells / 2), MeanY(Ncells / 2)
N = 0
put code here......do something...
'Define the return results as an array
Deming = Array(Slope, Intercept)
End Function
I would like to ask a related question as well. To run the programs
do name the subroutines and functions as macros? Then run the macros
by using the contextual menu and select macro/run/ macro name?
Thank you in advance,
Andrew Tag: in excel can not input chinese character Tag: 134363
Remove Formula
Recently I got excellent help on how to fix my dates which were entered
improperly. Worked like a dream. Now I need help on how to resolve the
current problem.
I have the old date column A - ddmmyyyy
I have beside, in Column B, it the proper converted date as yyyymmdd with
the formula in that column cell.
If I remove the first column (A) the second column data disappears.
If I remove the formula from the second column (B) that data disappears.
I only want to have one date column left - the second (B) which holds the
formula - but I don't want the formula any more.
Any ideas?
Many thanks
Gerry Tag: in excel can not input chinese character Tag: 134361
Font Size on Command Buttons - VBA
When I create a command button I can do all of the below except set
the FontStyle. What is the correct syntax for this?
With objActiveWkb.ActiveSheet.OLEObjects.Add("Forms.CommandButton.
1")
.Left = intLeft
.Top = intTop
.Object.Caption = strButtonCaption
.Width = intWidth
.Height = intHeight
.Object.Wordwrap = True
.Object.Font.Size = 10
' .FontStyle = "Bold"
End With Tag: in excel can not input chinese character Tag: 134360
Adding Days that are in the form of Text eg monday, tuesday
Hi i was hoping someone could help
what i have is column "A" which has a list of days in the form monday,
wednesday etc. Then i want column B to display the next day.
A B
1 Day Day+1
2 Day Day+1
3 Day Day+1
Column A is day stock will run out
Column B is the day after
I have done something similar to this before but I can't think how
Hope someone can help
Thanks For your help
Katy Tag: in excel can not input chinese character Tag: 134359
Adding up times
I have used the formula below to work out the amount of hours from a start
time to a finish time for each day.
Start - Finish - Total Hours
=TEXT(D3-C3,"h:mm:ss")
It works fine and give me the result but if I try to add the weeks hours I
get is 00:00:00.
Each sell has a formula which shows the correct times but the value shown
can not be used.
I hope this makes sense as it has got me beaten.
Any help would be appreciated.
Tia Tag: in excel can not input chinese character Tag: 134351
Count, divide and assign
This is a multi-part message in MIME format.
------=_NextPart_000_005A_01C84D42.9C1FF720
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Good day. New to posting but I review the newsgroup all the time. =
Great stuff. I have a client who buys lists. We need to be able to =
count the number of contacts on the list, divide between the number of =
sales people and then assign that number of contacts to each =
salesperson.
So for example if the spreadsheet has 200 contacts, we need for a macro, =
etc. to divide into the number of sales people, lets say 5 (maybe have =
the sales people listed on another worksheet?) and assign that number =
of (40) contacts to each sales person by putting their name in the sales =
rep field. I am fairly certain this can be done but I just don't know =
the best way to get it started.
Thanks in advance for any help you may offer.
Luci M. Packett
ACT! Certified Consultant
Stewart Technologies, Inc.
------=_NextPart_000_005A_01C84D42.9C1FF720
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2900.3243" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Good day. New to posting but I =
review the=20
newsgroup all the time. Great stuff. I have a client who =
buys=20
lists. We need to be able to count the number of contacts on the =
list,=20
divide between the number of sales people and then assign that =
number=20
of contacts to each salesperson.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>So for example if the spreadsheet has =
200 contacts,=20
we need for a macro, etc. to divide into the number of sales =
people, lets=20
say 5 (maybe have the sales people listed on another worksheet?) =
and=20
assign that number of (40) contacts to each sales person by =
putting their=20
name in the sales rep field. I am fairly certain this can be done =
but I=20
just don't know the best way to get it started.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Thanks in advance for any help you may=20
offer.</FONT></DIV><FONT face=3DArial size=3D2>
<DIV><BR><BR>Luci M. Packett</DIV>
<DIV>ACT! Certified Consultant<BR>Stewart Technologies,=20
Inc.<BR></FONT></DIV></BODY></HTML>
------=_NextPart_000_005A_01C84D42.9C1FF720-- Tag: in excel can not input chinese character Tag: 134349
VBA protection
I have a spreadsheet with the VBA protected. when i click on the file
name in the vba project window, i have to type in the password to
expand it to see the sheets and modules. i have another spreadsheet,
and have the vba protected, but the protection seems to not work.
could anyone tell me why perhaps?
Thanks,
tonso Tag: in excel can not input chinese character Tag: 134343
How Excel VBA corporate with MSN Alerts?
How Excel VBA corporate with MSN Alerts?
I want MSN Alerts to remind me when an Excel cell value more than 5.
Can I make it?
For detail:
1.aa.xls and sheets bb has a cell, C5.( in Computer A )
2.when value of C5 = p` (>5) ¡÷ MSN Alerts sent value p` to Computer B.
3.and shake my msn window (ex.cc@msn.com)
Can it done in Excel VBA design?
Is it free?
Thanks for your reading. Tag: in excel can not input chinese character Tag: 134342
saving to network drive
I am using excel 2003 from SBE.
If I use Tools-Options-Security and set a password for encryption and
read-only access then the workbook hangs when I try to save to a network
drive (W2K3).
If I remove the passwords then everything OK. Tag: in excel can not input chinese character Tag: 134339
A bit more help needed on an =SUMPRODUCT if possible?
Hi, just before christmas I was helped out on sorting a formula that would
produce a numerical value triggered by a certain text arrangement in a range
of cells - if possible i'd like to expand upon this?
If I put in an example below and the current formula i'm using
(Data)
02/01/08 22/11/2007 21/12/2007 10:27 R-44622 484253 C89215 4A2 (BG) GD (KT)
02/01/08 22/11/2007 21/12/2007 10:27 R-44622 484253 C89215 4A2 (BG) AG (KT)
02/01/08 22/11/2007 21/12/2007 10:27 R-44622 484253 C89215 4A2 (BG) SW2 (KT)
03/02/08 22/11/2007 21/12/2007 10:27 R-44622 484253 C89215 4A2 (BG) GD (KT)
(Formula)
=SUMPRODUCT(--(LEN('2008'!I3:'2008'!I260)-LEN(SUBSTITUTE('2008'!I3:'2008'!I260,"GD (KT)",""))>0))
Ok, this formula is concentrating on the text in the 7th column, and
producing a cumulative total from the whole range for how many times it
occurs...So for instance in that example the text 'GD KT' occurs twice so the
formula produces a 2.
Now, what i'm looking for is introducing an IF value to this - essentially
I'm aiming for a formula which will not only discern when a specific text
appears in column 7, but ALSO only read out IF it corresponds to a date value
in Column A....at the moment the data in column A is day-specific, which
could complicate things but I could amend this to a simple 01/08 if needs be
- in practical terms i'm trying to produce a total readout for instances of
GD (KT) and a separate monthly readout for analysis purposes...
Any ideas? Tag: in excel can not input chinese character Tag: 134326
Weird Excel Issue
When I take the following number 2481030076751701 and put it into excel, it
displays the number as 2481030076751700. I have the column formated as
number. Why is is changing the last digit, and how can I correct the
problem.
Thanks Tag: in excel can not input chinese character Tag: 134313
otherwise window anything can input chinese, but in excel can't input chinse.