point to one number on each sheet that will be in many formulas
I have many worksheets in one file. I have formulas on each sheet that point
to data on the first summary sheet. The only change is which line number to
get data from. I want to have one cell on each sheet with the corresponding
line number from the summary sheet, and have all the formulas on each sheet
combine or point or whatever it takes to do it - to that line number.
=INDIRECT(ADDRESS(A2,2,,,"Sheet1")) will return the value on Sheet1 in column
2 and the row specified by the value in cell A2. If you want to keep blanks
from turning into 0's, you could em...Excel: Can I display a chart when the mouse is over a cell?
I have a worksheet and i need to be able to have a range of cells from
another worksheet display when the mouse pointer is over a certain cell on
the first worksheet. Is there a way to do this?
If the range of cells is static and the contents of those cells don't
change, you could create a comment for the cells in the first worksheet
and put the values of other other worksheet in the comment.
Other than that I cannot think of any other way, not even with VBA
since XL doesn't raise any event when the cursor is over a particular
www.tushar-mehta...err: copied formula has name that already exists, but I don't think does
Thanks to all that have given help and that give in the
I have a macro in a workbook that copies a worksheet from
another workbook into the macro running one (code at end
of this message).
When I run the macro, I get an error that says:
"A formula or sheet you want to move or copy contains the
name 'blah', which already exists on the destination
worksheet. Do you want to use this version of the name? To
use the name as defined in the destination sheet, click
Yes. To rename the range ..."...and so on...
I checked with Edit>Find for 'blah' but it can&...Showing forumlae instead of results
I have a large set of sheets (Excel 2000) that I need to verify a bunch of formulae. Is there any
way to have Excel show the formula in a cell instead of the results of the formula? I seem to
remember a formula view in some program I used at one time, but I can't find anything similar in
Excel 2000. The way it is now, I have to use the arrow keys and scroll down each column and read
each formula in the formula bar which is very time consuming, where if I could switch the display to
show the actual formulas, I could spot the bad ones at a glance.
Any help appreciated, thanks in advance.
Co...Using Defined Names in a Formula
I'm trying to use a named Range in a Sumproduct Formula however what i want
to do is return the total of all the cells which contain values not in the
So similiar to this Formula :
But I need to alter it so it only sums the cells F2:F311 that dont contain
the values in STDGL for cells H2:H311 or in STD for cells I2:I311
that make sense ?
If you want to sum all the numbers in B1:B10 that are not present in a
range named TheName, use ...When moving one (or more) messages from one folder to another, outlook instead brings them into a new e-mail as attachments
This is outlook 2000 running on a win-98 system.
This doesn't happen all the time, but when it does happen it will keep
happening for the rest of the session.
When I highlight one (or more) messages and select "move" and then
specify a target folder, what will happen is instead of moving the
messages, outlook will open a new message composition window and throw
all the selected messages into the new message as attachments.
Anyone ever seen this before?
...text value of a cell
how can I return the text value of a cell useing"sumif" formula
Please share with us where ideas like that come from.
Sumif is, as its name indicates, supposed to return a sum. Sums are almost
HTH. Best wishes Harald
"formula1" <email@example.com> skrev i melding
> how can I return the text value of a cell useing"sumif" formula
If you mean that you wish to total number values that are text formatted,
where Sumif() doesn't recognize them as numbers, and ...Hide Value of Cell
I have a cell (c) with this formula: =(a2/b2)-1.005. I
want to hide the #DIV/0! that appears in the cell when
there is no data in a2 or b2. Is this possible? Thanks
in advance for your help.
In article <firstname.lastname@example.org>,
"Mark" <email@example.com> wrote:
> I have a cell (c) with this formula: =(a2/b2)-1.005. I
> want to hide the #DIV/0! that appears in the cell when
> there is no data in a2 or b2. Is this possible? Thanks ...Display As field causing problems with New Message to Contact func
I am new to Outlook 2007. I am trying to use the New Message to Contact tool
but I keep getting a message stating that the contact does not have a valid
email address even though the address is indeed valid. I learned that if I
delete the contents of the Display As field and allow Outlook to fill the
field automatically, the problem is resolved. I do not want to have to do
this for each and every contact in order to use this feature. Are you aware
of a fix for this problem?
jessbcr <firstname.lastname@example.org> wrote:
> I am new to Outlook 2007. I am trying to use t...VLOOKUP Nesting Formulas
I've been trying to write a vlookup formula that has to see tw
different numbers before it will say TRUE or FALSE.
I write the following for it to look in one column:
How do I write it if I have information in cell a1 AND b1? Both hav
to be there before I want it to say TRUE.
ajpowers's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=940
View this thread: http://www.excelforum.com/showthread.php?threadid=27096
<"Both have to be t...formula for headings in columns
OK, say I have 24 columns which represents 24 months.
The last column contains data from the latest available
month-year. Say Jul 04.
I like to be able to use formulae in all of these 24
column headings so that when I type the date of latest
data (say in column Z1), that all columns headings will be
automatically displaying the correct month-year.
For Example if I type Jul 04 in cell Z1, I want
Column24.Row 1 to say Jul 04
column 23, Row 1 to say Jun o4
Column 1 would say Jul 03
What formula do you use for this?
A1: =DATE(YEAR($Z$1),MONTH($Z$1)-(...Resetting cells to zero value.
I have lots of cells with numbers. Can I place a button that will reset all
cells to zero?
Right now, when I want to start over, I go to each cell and change it to
zero. I have 250 cells.
Thank you if you can help with this.
Select the 250 cells you want to reset to 0.
Hit alt-f11 to get to the VBE
hit ctrl-g to see the immediate window
Selection.name = "ClearThemAll"
and hit enter
Now back to excel select any cell.
Start recording a macro.
(to fill all the selected cells with 0)
stop recording the macro (or s...Trying to understand a formula
Someone in my organization has put together what I consider to be a very
well thought out spreadsheet to track inventory. When I attempt to analyze
it "from the inside out", I ran across a formula that I don't understand and
was unable to find in "Help".
Obviously, this formula counts, item by item, the rows within the range that
is in the formula. But can anyone tell me where I can find more information
on this specific formula:
....and the number that appears in this cell is 58, which is the total of
integers between 3 and 62.
The real...SUMPRODUCT on multiple values
I attempted to use the multiple criteria as in:
but the result returns #VALUE!
Also, assuming I can get this to work, does this also work for <> (Not equal
to)? So the formula would be:
here is one I have been using a long time. Works just fine
"FrankTimJr" <FrankTimJr@disc...Scatter Charts
I am using an XY scatter chart with data that looks like
Name Priority Date Lic/Ow
Free 12/16/1998 Lic
Warner 1/28/1999 Lic
Buckmon 7/12/2001 Ow
Tucker 12/28/2000 Ow
The Y values are the priority dates and x values would Lic or Own. Each dataset would be referenced in vertical columns. The problem is how to change data labels so that reflect the data in the name column and the data in the Lic/Own column.
Hope you can help.
Thanks but I was able to use the XY Chart Labeler from Ap...Preselect value in combo box
I have a combo box that is populated by a look-up table. This look-up
table only has two columns id and name. Only the name column is
displayed by this combo box and the user uses this combo box to select
Now to my question:
Usually it's one particular name of this list that is selected and
saved over and over again. To make things more convenient for the
user, Is it possible to "preselect" or "prefill" this combo box with
this particular name from the list? if so, how to do that?
Thanks for your input.
To be more exact, the user selects the "name" ...Files is Locked by Valued Customer EXCEL
I have a situation where we have several people who may edit an Excel file.
When the boss needs to use it, he tries to open it and gets "File is locked
for editing by valued customer"
We understand about no multiple edits, but the issue is, since the box does
not say so, we do not knw who has it open.
Is there a way to fix that?
I went to one users system to make sure the registered owner had his name in
That still did not fix it.
In Tools->Options->General, the username is likely "valued customer".
Get your office staff to create unique MS O...How to return a number instead of true/false with an if function?
eg =IF(A1=B1,1,2) will return a 1 if TRUE, and a 2 if FALSE
Replace xxx with hotmail
> Please help
Always elaborate on your issue in the message area (that big white space).
The subject line's just that, meant to be brief keynotes on your issue. It's
NOT your query. Elaborate by posting your formula or your formula attempts
and by describing details of what you have (your sheet set-up, sample data)
and what you want to do/happen
Anyway, here's 2 simple IF examples to groove you in its use ...You cannot display this record while another user is editing it.
I had deleted a cash reciepts batch. I went to repost the batch, and when I
tried to post to customer invoice's invoice's were gone. They had already
been processed by pymnt00000036780. When I try to pull up this record, it
get the above error message. If you look in customer inquiry to see record it
looks like open invoice, pymnt000000036780 is there, but when you look at
that, it tells you that record does not exsist. Did not post through to GL
and does not exist anylonger( because it was deleted) How to I delete this
payment record so I can post the payment and post a batc...Apple Computer offers low value
Same dumb people who have deserted PC world & defected to Macs, because they
installed some free game that piggybacked malware which cost them $$$
Hundreds and a weeklost, well those people will have Macs disappoint them
sooner or later, with vast numbers coming back home -- to the PC world...
Whether or not they'll be willing to accept it first, reality will keep
hitting them in the face that Windows 7 (or upcoming 8) is a much better
value and no longer a pain like Vista was for them (I personally think
XP/Vista were awesome OSs..but I'm very knowledgeable & kno...user parameter
I want to create a query that will alow a user to search only one drug or
more drugs depending on user's needs. For instance, a user can see activity
for only asprin or he can see activity for asprin and zantac.
select drug, username, date
where drug = @drug or drug = @drug or drug = @drug or drug = @drug;
Is there an easier way to do this?
Thanks in advance.
Message posted via AccessMonster.com
It all starts with the data ...
Please provide more specific information about what the u...Formula: What am I Doing Wrong
In the first example I want to know how many instances of A there are
in column B multiplied by the number in Column A. Answer would be 8.
But if I want to find out how many instances of $6.00 there are
instead of A, I'm stumped. I get either 0, or 48.
Must be something simple.
It is better to use
I assume you mean
(there's no email, no snai...Using "or" instead of "and" in workflow
I am trying to say:
If Account:Address 1: State/Province does not equal [NY] or [MA] or [CT],
blah blah blah.
But it seems the condition will only say "and". I would have also thought I
could use "not in" but it only allows one entry.
If Account:Address 1: State/Province not in [NY]. Can't add any more entries
...Hide Cells with Zero Value in Excel 2007 while using line chart ty
I am using Excel 2007 and have a situation with the charting functionality.
For example - I am trying to set up automatic update chart update for Jan 08
– Dec 08 data i.e. user just enters data each month and the graph trend shows
up each month. I have done this a lot of times but seem to have a unique
situation with excel 07 this time when I am using the line chart type to show
The date cells that have some kind of formulas used i.e. if function or
Iserror function etc. I tried to test the chart by entering data for January
it works fine with bar graph only displ...Count formula?
I have some cells that i need to do a count on.
I can do the count formla but i can not do a specific count.
I have some cells and I only want it to do a count on the cells that has a
number above 200.
Can someone help?
use the countif formula. you can look it up in help.
>I have some cells that i need to do a count on.
>I can do the count formla but i can not do a specific
>I have some cells and I only want it to do a count on the
cells that has a
>number above 200.