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)
...How can a hyperlink be opened by pressing Enter.
In Excel 2003 after inserting a hyperlink to open a picture file stored in
the local drive the user needs to be able to open the file by pressing Enter
on the cell that contains the hyperlink rather than by clicking on it with
the mouse. This feature is enable in Excel 2000 but it the not work in 2003.
(the file type or location does not matter). In short, how can a hyperlink be
opened by pressing Enter in Excel 2003.
Thank you in advance for your help,
I think that this went away in xl2002.
You could send a message to email@example.com asking for this to come back.
Carol...HELP!!! Can't Apply Payment for Posted Invoice while Unposted in C
One user was entering in a transaction in the Sales Transaction Entry Window.
She had not saved or put the transaction in a batch. The power went out,
and she started up the computer when the power come back.
She could not see the transaction in the Sales Transaction Entry for that
Customer. She could see the transaction in the Collection Main window for
that Customer but in the Unposted Sales.
I tried to view the transaction from the SmartList but it is says it has
been posted. So the user goes ahead and post thru the GL from the Series
She still can not view t...enabling and desabling autofiler on protect sheet
how can i give the option in the VBA command to dynamicly enable or
desable autofilter on a row when the cells are protected for any
...why i can not run excel2000 in windows XP?
i have installed office2000 in windows XP correctly, but i
can not open it. after the splash window, there are nothing
please tell me why i met that mistake and how to resolve it.
thank you for your help
The following MSKB article may help:
OFF2000: Program Quits Immediately After Starting When SR-1/SR-1a Update
> i have installed office2000 in windows XP correctly, but i
> can not open it. after the splash window, there are nothing
> please tell me why i met that mistake ...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...Can I Make Calendar Overlay my Default View?
I have my personal calendar and a shared public calendar. I have
found how to go to the calendar shortcut, select the two calendars,
and then overlay them. However, if I go to the folder view that
overlay view goes away. Also, if I restart Outlook, I have to go
through all of the work to reset that view up again.
I found the "save calendar choice" freeware application mentioned in
this forum elsewhere but this did not seem to really do the trick.
For one thing, it only saves the view choice as side-by-side.
Really, what I want to do is save my default calendar view to always
sho...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. ...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.
...how do make a row non numbered?
Trying to make a row not have a numbered value on the left side.
I don't know where you're goinng with this, but....
Those numbers are used for reference purposes by excel.
If you'd like to display something different, maybe this will work for you:
Use Col_A to display the "row numbers" you'd prefer.
Then, Tools|Options|View tab|Uncheck: Row & Column Headers
If you need to display the Column Headers, you'll could put them in Row_1.
Does that help?
"Removing number value from a row" wrote:
> Tryi...3d reference
Is it possible to suppress the "0" that is returned when referencing a blank
cell in another worksheet? I have used an IF function to ensure blank cells
in the past, but that requires a good bit of labor for even a medium sized
worksheet. I tried various cell formats, but continue to get "0"s in the
You could use a custom number format or even Format|conditional formatting.
But those will hide the cells that are returning an actual 0.
I'd do that extra work:
=if('sheet 99'!a1="","",'sheet 99'!a1)
==...Add more than one series to a pivot chart using VB MS Access continued...
I'm trying to programmatically create a stacked bar pivot chart.
Using "Programming Microsoft Office Access 2003" by Rick Dobson, I've
created the chart. However, it doesn't distinguish between the
different values for the series. Does anyone have any suggestions on
how to create a chart using one column containing three values for the
Essentially, this is a continuation of a previous post:
Any help is greatly appre...How can comunicate annoucement more agile?
is it any form to replicate the winpoup utility with annoucemen of Micrososft
...Db template that can handle making book indices?
Does anyone know if there is a template that can be used to create
printed book indices? The indices are those pages usually found at
the back of books that allow you to find information in any book
quickly and easily.
The difficulty is that some books, as an example even cookbooks, don't
have a comprehensive enough index so it would be nice to create one's
own esp. for books we use quite frequently where we'd like to create a
booklet we tuck into the book so that when we go to do research, we
can find anything.
As an example, I'd like to use a cookbook because it...Unread messages don't exist
My Outlook 2003 Unread Messages folder states that I have 11 unread
messages in my inbox. I select the Inbox folder from the Unread
Messages folder and right click and choose Mark Messages as Read. I'm
then presented withe the following info:
Cannot mark the items read or unread. The most likely reasons are:
You don't have permissions to modify the items
If I hi-light one of the messages and choose delete, I get the
The item could not be deleted. It was either moved or already deleted,
or access was denied.
I cannot read the message either.
I have run scanpst on...Tweaking cells
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.
>...can not forward or reply to messages
I was able to send, forward and reply to messages up til today. For some
reason I can not forward or reply to messages today. They are greyed out. I
am using Microsoft outlook 2007. Any idea how to fix this?
And you cannot create new messages either? Probably the same applies to
creating new documents in any of the other Office applications too. In that
case it means that you haven't activated your copy of Office yet. You can do
so in Outlook via Help-> Activate Product...
Robert Sparnaaij [MVP-Outlook]
Coauthor, Configuring Microsoft Outlook 2003
http://www.howto...Setting up Solver reference in a protected VBAproject
I want to distribute a Excel 2007 workbookk that contains several macros. I
need to protect the VBAproject so that the users cannot modify the code.
However, in order to use some of the macros the user needs to enable the
Solver Add-in, and for that purpose, he needs to have access to the
Is there a way to setup the Solver Add-in while maintaining the VBA project
protected (and without disclosing the password)?
Any ideas would be appreciated.
...Can desktop be switched in InitInstance()?
I'd like my app to run in a non-default desktop. I thought I could create a
new desktop and switch to it in InitInstance() and then restore the old
desktop in ExitInstance().
The code in InitInstance() is as follows:
m_hDeskThreadSav = ::GetThreadDesktop(GetCurrentThreadId());
m_hDeskInputSav = ::OpenInputDesktop(0, FALSE, DESKTOP_SWITCHDESKTOP);
m_hDesktop = ::CreateDesktop(L"MyDesktop", NULL, NULL, 0, GENERIC_ALL,
When app runs I get a new desktop but the a...Where can I find Exchange EventID Descriptions #2
I need to create software which analyse MSExchange event
log on the fact of occurence critical Exchange Server
events. But can't find more or less full description of MS
Exchange Server 5.5/2000 events. I've tried to find it in
MSDN, Exchange SDK - nothing.
Where can I find full description of MS Exchange Server
events ID's (eventlog).
have a look at www.eventid.net they have a great event id resource and event
"Igor" <firstname.lastname@example.org> wrote in message
> I need to cr...Can I run proprietary Windows Software on Remote Desktop?
Operating System: Mac OS X 10.4 (Tiger)
Processor: Power PC
I am using a PowerPC G5 Mac Mini. I am taking a course that requires a minimum of Windows XP or Vista. Can I install the course software on Remote Desktop so that I can use it to take this class, or is this just for using MS Office programs?
Also, what is the cost of this service?
On 3/24/08 12:03 PM, in article email@example.comR9absDaxw,
"firstname.lastname@example.org" <email@example.com> wrote:
> Operating System: Mac OS X 10.4 (Tiger)
> Processor: Power PC
> I am using a PowerPC G5 Mac ...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...how to reverse a series batch posting in the GL only
we have a batch from invoice batches. The GL numbers are wrong..
we want to reverse the GL journal entries only. how can we do this. We
will put in a new Journal entry for the correct numbers.
don't want any of the details in the invoices touched...
The backout feature in GL will work only on transactions originated in GL.
You can always create a summary correcting entries in GL for the entire
invoice batch instead of creating JE per invoice document.
Hope this helps!
Dennis Araullo, MACS
Microsoft Certified Techn...Can't see row numbers or left scroll arrow in Excel spreadsheet
One of my excel spreadsheets won't display the row numbers (half of column
"A" also can't be seen); left scroll arrow is not on screen. Other
spreadsheets are OK. What to do?
Try Ctrl+F10 to maximize the workbook?
"Jim Freund" wrote:
> One of my excel spreadsheets won't display the row numbers (half of column
> "A" also can't be seen); left scroll arrow is not on screen. Other
> spreadsheets are OK. What to do?