Lookup and Sum in same cell
SHNAME ENTDATE SYMBOL QTY RATE COMM
C78 1/24/2005 CSCO 1100 0.018 $20
C78 1/28/2005 CSCO -5439 0.018 $100
C78 2/23/2005 CSCO 50 0.017 $1
C78 4/5/2005 CSCO -7892 0.018 $140
C78 4/13/2005 CSCO 3786 0.018 $69
I am trying to create a lookup formula that will search by SHNAME, find
each entry and sum the COMM column in one cell:
This worksheet is thousands of rows long with multiple rows for each
Thanks in advance
Potatosalad...Cell Border Line Width
Is it possible to specify the cell border line width using Excel 2002?
The "Border" tab in the "Format Cells" dialog box allows me to choose
predefined line widths but I need a width other than what is offered.
Is there a way to do this?
Not with the borders property...you are restricted to xlHairline,
xlThin, xlMedium, xlThick".......
You could *possibly* fake it with the AddLines method of the worksheet
but I wouldn't really want to try.....;o)
...First post, need help guys!
Hi to all,
I pasted together the below attachment.
Can anyone help me with it. I need to know how to start a second grap
55% of the way up the original graphs axis.
PS. I cant get the pic bigger but can mail if you leave a reply on th
Moxy1980's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2807
View this thread: http://www.excelforum.com/showthread.php?threadid=47583
The second set of dat...Locked cells in IE
I need to open an Excel document from a web page. Active X controls are
restricted and I cant pass the document as a parameter in a shortcut to the
Excel executable because it needs to be generic (and would not e in this
The problem Im having is that the Excel document contains locked cells that
need to remain locked. When the document opens within the internet explorer
interface the locked cells are not visible and the spreadsheet looks a bit
Does anyone know another way to open the document from a web page or how to
make the cells visible?
Many many thanks...UPPER CASE formatting a cell
I receive excel data from a number of sources to be combined together in a
single sheet. I would like to format a column of cells to be all upper case
but not use the =UPPER() function. I'd like to format a cell to force any
lower case char to upper case automatically when data is entered. Custom
formatting as >CCCC doesn't work. Can this be done?
Conversely, how can I force cell A1 to be UPPER CASE by using =UPPER(A2),
then remove the reference to A2 when printing the result? I don't want the
cell A2 to print.
This I forget how to do.
you'll need VBA for this. ...Excel 2007 convert into tab delimeter text file with extra tab spa
Sub: I have excel 2007 trying to save as tab delimeted text file having an
I have saved my excel 2007 as separate Tab delimited Text files. I see extra
tabs stored in the tab delimited text files.
But when I do the same with excel 2003 it works fine there is not extra tabs
stored in my tab delimited text file.
My excel 2007 conveted to tab delimited text file looks like as shown
below(Extra two tabs at the end of the each record).
Appreciate your help in advance.
Rega...find last cell in column
Apart from using VBA, is there any way to write a formula that will find the
last used cell in a column of 15 cells? In the first set below, it would be
..388, in the second set it would be .133. Any help would be appreciated.
If I have cells that may remain blank, how do I get them to be blank instead
of showing a 0? These cells are set for text, not numbers, so I don't
understand why a number would show there by default.
do you have formulas in these cells and if yes what kind of formulas?
> If I have cells that may remain blank, how do I get them to be blank
> instead of showing a 0? These cells are set for text, not numbers, so
> I don't understand why a number would show there by default.
>...Critique a function to convert time, please
I have a bunch of data representing time intervals that come from a
database application. The intervals vary from a few seconds to several
years. The database (MySQL) stores time intervals as fixed point
numbers in units of seconds.
Reading the values in seconds is unnatural (a year is ~31.5E6
seconds), so I wrote a little UDF to convert the seconds to more
natural units. The conversion is:
<60 seconds Seconds
<60 minutes Minutes
<24 hours Hours
<99 days Days
I chose 99 days as the threshold betwe...Text cell complains about date
I have a column containing ratios like 3/4, 3/0, 5/2, etc.
I have them all formatted as text.
All of the cells that have a zero as the second number (3/0) get a
little green triangle and a warning that I have entered a "date string
with only 2 digits for the year".
How is "3/0" a date string with 2 digits for the year?
And, why is it complaining about a text field?
More importantly, how do I get Excel to stop being so "helpful" and
leave my damned text data alone -- unexamined?
Remove the warning in Options, Tools>Options>Error Checking in 2003...Extract First Name From String
How can I extract the First Name only from the String Below? I only want to
pick up 'Veronica'
I can get the Last Name by using CHARINDEX(',',[Patient Name])-1
[Patient Name] = LUNA,VERONICA E
Message posted via SQLMonster.com
SELECT LEFT(@Value, CHARINDEX(',', @Value)-1),
"Brian Conner via SQLMonster.com" <u47161@uwe> wrote in message
> How can I extract the First Name only from the String Be...in Excel 2000 how do I convert text to numbers?
I imported files from Wells Fargo and I want to convert them from text to
numbers...so far nothing is working! thanks
Format all as General.
Copy an empty cell(also formatted as General).
Select range of numbers. Edit>Paste Special>Add>OK>Esc.
One other method.........
Select range of numbers and Data>Text to Columns>Finish
Gord Dibben Excel MVP
On Sun, 27 Feb 2005 11:53:06 -0800, Jo Dee <Jo Dee@discussions.microsoft.com>
>I imported files from Wells Fargo and I want to convert them from text to
>numbers...so far nothi...Cursor jump to first field
In A2007 I have a form with TabCtl with pages.
In each page I have one subform with field and comboboxes.
After choose an option in theese combos show us textboxes.
If to accede the field I need to use the scrollbar, when write in textbox
the cursor jump to yhe 1st field in this sub form.
Consequently we need to use the scrollbar again to to go next field and like
How is possible the cursor to stay inside of the last field and to avoid to
jump and to turn to scrollbar?
Thanks in advance.
...Change the name of an auto save
I am trying to write a macro that will save the file to a specific cell
reference. The reference is a specific name that is automatically generated
by another macro function.
Any suggestions on how I could do this?
IS thsi what you want?
sFilename = Format(Worksheets("Sheet1").Range("A1").Value)
ans = MsgBox("Save file as " & sFilename)
If ans = vbOK Then
If this information was helpful, please indicate this by clicking ''Y...Apply a mutiple to a range of cells?
I have a range of cells spanning multiple columns and rows. I would
like, in my fantasy, to just hightlight all of them and say "multiply
by x" where x is an integer. Now, I know I can't do that because my
microphone doesn't work, so, other then a macro, is there a way to do
In an unused cell enter the numnber that you want to multiply by - it can be
an integer or a floating point number - then highlight the range to you want
to mutiply and then right-click and select Paste Special > Multiply > Ok
Don't forget to say "Multipy by x"...Delete Name Box!
I know how to create a Name Box for a range, but how do you delete
it?! It is like I forgot it is done!
Insert - Name - Define.
Select Range, Delete.
*Remember to click "yes" if this post helped you!*
> I know how to create a Name Box for a range, but how do you delete
> it?! It is like I forgot it is done!
...change cells contents from vertical to horizontal
I need to copy from one workseet the values in cells that are arranged
vertically into another worksheet and the values need to be arranged
horizontally. Can this be done? If so, how?
Select the data to be copied>Copy>Select target cell>Right
If this post helps click Yes
> I need to copy from one workseet the values in cells that are arranged
> vertically into another worksheet and the values need to be arranged
> horizontally. Can this be done? ...Convert Excel records to text documents in arbitary formats
A newbie here (groan). I need a tool to take data in excel tables and
generate text documents with the data rearranged in new forms, eg to
look somewhat like mailing labels (but with more data items, like
email address). Is there a tool that will take Excel data and generate
documents in arbitrary formats?
Also, is there a tool to edit column data, eg. the Name field I have
is all uppercase, and I wanted to cap the first and make the others
lower case (eg: JONES -> Jones), a pain in the ass if done manually.
Generally, an editing tool for column data.
thanks in advance
Don't kno...Chinese name sorting in Outlook
Would anyone familiar with Chinese tell me how I might be
able to sort my address book correctly. Currently, it's a
mess and abc with the chinese names creating little to no
order. My old p[alm pilot and palm desktop sorted it
properly with english first and then chinese based on
characters and the pinyin system, but now that I converted
it to Outlook- everything is a mess. Any help would be
...Message box warns for duplicate use of named ranges when copying s
I created an Exel/VBA2003 application in which a copy of a worksheet is added
when the user pushes some button on the main form. In the worksheet which is
copied, I use a few named ranges. This works fine in 2003.
In 2007, however, a message box pops up that warns for duplicate use of the
named range in the new sheet. Since there are 5 ranges, it is quite annoying
to cancel these message boxes every time.
Can I programmatically prevent these msg boxes from popping up ?
Found one answer on pcreview (gracias a Hector Miguel): add
Application.DisplayAlerts = False a...Chart name
Here is an easy one, I guess, for those who know the answer:
Charts on a worksheet are named Chart1, Chart2, etc. as they ar
created, and this is the name you must refer to when manupulating the
Is it possible to look up the name of a chart, and if affirmative is i
possible to change it?
nsv's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2650
View this thread: http://www.excelforum.com/showthread.php?threadid=55725
Hold the Shift key whilst selecting the chartobje...Creating new series requires div cell val by itself . How to do t.
I am trying to rebase a time series which requires dividing the first value
in each series by itself. I keep getting circular error messages regardless
of how I format the calculation. Is ther a way around this issue?
When you divide a number(and time is a number) by itself you will get 1.
Is that what you want?
You could use a couple of helper columns.
Assume data in column A.
In B1 enter =A1.
In C1 enter =A1/B1.
Gord Dibben Excel MVP
On Mon, 21 Mar 2005 12:35:08 -0800, "Texastom"
>I am trying to rebase a time ...convert text to number format
I have a column of numbers that have the comment "The number in this cell is
formatted as text or preceded by an apostrophe". I'd like to convert all
these these values to numbers so I can get a summation. By mousing over the
cell I can get a menu that allows me to convert the numbers one at a time,
but how can I convert them all at once?
I've tried converting with the Format/Cells and Edit/Copy/PasteSpecial/values.
Thanks in advance,
I found the answer:
In an empty cell, enter the number 1.
Select the cell, and on the Edit menu, click Copy.
Select the...Speed Search in pick list by typing only first few letters
Is there any facility to pick from the list by typing only first few words (2
or 3) and the curser goes to the matched or nearby entry in the list arranged
alphabetically ? This is similar to locating our topic in the Help index.
In the pick list feature, I have to manually move the curser to the desire
entry. If the list is long, it is not practicable to scroll down the list.
A combobox from the control toolbox toolbar has this ability.
> Is there any facility to pick from the list by typing only first few words (2
> or 3) and the curser goes to ...sum cells
I am trying to total employee schedule so some cell have D/O day off, R/O
requested off, so when I try to total all associates by the day I get #VALUE!
in cell and total cell. Is there a way to total only numbers and ignore
Why not fix the formula that causes the error in the first place?
What is the formula that does this:
>when I try to total all associates by the day I get #VALUE!
"SteveD" <SteveD@discussions.microsoft.com> wrote in message