Selecting only cells with formulas
Is there a quick way to select only those cells that have a formula i
them - I want to lock the cells a worksheet that have formulas - bu
selecting them one at time is too time consumin
mycroft777's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3114
View this thread: http://www.excelforum.com/showthread.php?threadid=51273
Edit>Goto>Special, check Formulas
"mycroft777" <firstname.lastname@example.org&g...Inserted Row has no formulas in it
When you insert a row to a sheet with formulas (that proceed down a
column, e.g. a 'running total' for example), the formula doesn't
appear in the inserted row?
I know you can copy/paste the formula manually, but since the formula
applies to each row, why doesn't the program put it there
I am using 2000, build 9.0.6926 SP-3
Here's a workaround: copy one of the rows that has the formulas. Go to the
row above which you want the insert, and select the entire row. Press Ctrl
and the + key on the keypad. This inserts a complete copy of ...Drag Formula cell to decrement the cells
I have a 20 different values starting from A1 till T1
I need to do automatically (by dragging a formula cell) that U1=T1
V1=S1, W1=R1 ........... AN=A1 (mirror of A1 till T1)
Asher Zur's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1610
View this thread: http://www.excelforum.com/showthread.php?threadid=27554
In U1 put =A1 and then just copy across to AN1. Select U1:AN1 and do Edit /
replace / replace = with %%. Select U1:AN1 and do Edit / Sort, hit options and ...What are the formula for calculating ROI all dates (Total return all dates)
From http://money.mvps.org/articles/portfolio_columns.aspx, it says
that dividends should be included in calculation of ROI all dates(Total
return all dates)
So the formula for ROI should be ((market price - purchase price)+
dividend) / purchase price.
Is this correct?
However, what I am seeing is that ROI all dates is calculated as
(market price - purchase price)/purchase price.
Is there any other parameter that includes dividends also
In microsoft.public.money, email@example.com wrote:
> From http://money.mvps.org/articles/portfolio_columns.aspx, it says
>that dividends should be i...Formulas in macros
in one of the macros, i need apply the =SUBTOTAL(9, .... ) formula ove
a specific range. i have that range stored in a range variable name
how could i do that..
shimeel's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1583
View this thread: http://www.excelforum.com/showthread.php?threadid=27419
activecell.formula="=SUBTOTAL(9," & range("A1:A100").address & ")"
Frank...Show a subform based on data in subform
I have a database for members of an organisation some of whom belong to
sub-committees (of which there are many). On the main 'Members' form there
is a 'Committees Subform shown. How can I hide the subform if the member
does not belong to any of the committees?
Your suggestions would be appreciated.
Hi Mannie - you could use the 'on current' event of the form to check if
there is any records in the sub-committee table and hide the subform if no -
If DCount("*", "SubCommittee", "[memberI...Can't get Bar chart to use my numbers
I have a set of numbers:
In this case, I have 1 response at 250,000 and 3 reponses at 50,000 etc.
What I am trying to do is chart this so that the Y axis is 0 - 250,000 (or
300,000) and the X axis is at 0, 1, 2 , 3.
What I get is the Y axis at 0 - 250,000 and the X axis at 0, 1, 2, 3, 4, 5,
6, 7, 8, 9, 10, 11, 12 (which are the cell numbers where 250000 is 1 and
1...Is there a formula to show $5,000,000 as $5M?
I am looking for an easy way to convert $M values to eliminate the 6 zeros.
You could try something like this:
unless someone has a better idea....
> I am looking for an easy way to convert $M values to eliminate the 6 zeros.
=TEXT(A1,"$ #,, ""M""")
Another option would be to keep the value in the cell, and use a similar
Win XP & Office 2003
"ExcelHelp" <...Booked resource not showing any information
Exchange 2003 with Outlook 2003
Some resources when they are being booked in Outlook show the No
Information available box. You know the white box with the /////. Other
resources show up with information just fine.
...My logo (jpg) prints as a solid black box. Prnt preview shows ok
Why does my logo, which is a .jpg file print out as a solid black box? Print
preview displays it correctly.
Could be the resolution, if it is very high this can happen.
Mary Sauer MSFT MVP
"Russ" <Russ@discussions.microsoft.com> wrote in message
> Why does my logo, which is a .jpg file print out as a solid black box? Print
> preview displays it correctly.
...Select a query showing only selective post codes relating a relevant postal area
please could you help as i would like to run a query only selecting relevant
post codes from my database. The post codes i would like to select are from
ST1 to ST4, ST6 to ST7.
Thank you spending time to help this issue
Message posted via AccessMonster.com
I assume that those are the starting three characters of the post code and
that the next character is a space. You could try a query using the criteria
Between "ST1" and "ST4 zzzzzz" OR Between "ST6" and ...Excel Formula Bar
Operating System: Mac OS X 10.6 (Snow Leopard)
I also am extremely frustrated with having the formula bar not embedded in with the other tool bars. And I have been working with it now for several months. It is not something I am 'getting over' or adapting. <br><br>I routinely use multiple monitors with spreadsheets on each side and to only have a formula bar on one side, usually blocking information, sheet tabs, or other toolbars is unacceptable. <br><br>I tool have experienced crashes that I now have learned is associated with this tool bar...Formula to determine row of hi value filtered column
Trying to see if its possible for a formula to return the row # of the
highest or lowest visible value in an autofiltered range.
rgarber50's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=11350
View this thread: http://www.excelforum.com/showthread.php?threadid=401821
...In a bar chart, how do I change the x-axis "category" labels?
I am trying to make a bar chart with customized category labels along the x
axis for each bar. I'd like them to be a series of numbers (100, 125, 160,
200...) etc. I can't get anything but numbers 1, 2, 3, 4... to appear...
If your categories are in the column just to the left of graphed
quantities, and, if you select both columns before inserting the Bar
chart, the categories will appear on the vertical axis.
...display information at html Status Bar
I am working with a com add-in. I created a custom button which allow
user to enter information and then save to database. After it is saved,
I would like to display that information in Status Bar. I have created
the html page to display the data. It works fine when pos is first
Now the problem is: how to refresh the data after user enter
information? Is something like fireevent I can use for this?
Another problem, when the form pop up, how to make sure it is always
focused? That is, user can not click pos while the form stays open?
You can have the...Formula Error #7
My formula works fine but it has the #value! error. what do I add to my
formula to rid myself of this?
It's not clear to me what you're checking for in the IF formula. You
should be able to change this to:
Mike R wrote:
> My formula works fine but it has the #value! error. what do I add to my
> formula to rid myself of this?
Excel FAQ, Tips & Book List
Replace the "" with a zero.
BTW, if your formula ...SUMPRODUCT formula help
I need help creating a SUMPRODUCT formula that will find numbers in A:A that
are in between (or equal to) the criteria in cells M2 (low value) and M3
(high value). Then I need for it to look in B:B for values in between (or
equal to) the criteria in N2 (low value) and N3 (high value). Then I need
the number of times that both occur in the same row. For example; M2/M3 and
Data in A:B =
-84.498607 33.8966...My formula is too long, What am I to do?
My formula given below needs to be modified and excel will not allow me to
add digits. Is there another way to write this formula that would be more
concise or can I extent the capacity of certain cells?
University of Washington
=SUMPRODUCT(--(DATA!$C$2:$C$1500=98061),--(DATA!$AB$2:$AB$1500>0),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98110),--(DATA!$AB$2:$AB$1500>0),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98311),--(DATA!$AB$2:$AB$1500>0),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98315),--(DATA!$AB$2:$A...How to show more than one category
In Outlook 2002 I can assign more than one category to a contact but
I only see the first one displayed in the category column in the
If I put the focus on the category cell then the additional
categories then show up.
Is this a bug?
...New messages not showing in Inbox
We use Outlook 2002 SP2. On a couple of desktops we do not
see new messages showing up unless we open an existing
message. Once we close the existing message, all new items
are there. When we try to click the Send/Receive icon, it
does not bring the new items. The Outlook client seem to
contact the Exchange server only when we open an existing
item. Any thoughts? Thank you. P.
Could it be the 'views'? Did you change anything in the 'views'? Try
searching for 'Views' in the help file...
"P" <plavallee@rcn.......com> wrote in messa...Trying to change a formula
This is the formula I am using and it works good....
I now need to extend down to cell G133 instead of G125
I have manually changed all the G125s to G133 but then it returns a zero
What am I doing wrong?
This is an array formula - did you remember to commit the formula with
CTRL-SHIFT-ENTER (CSE) when you amended it? Double-click in the cell as
if to edit it, then do CTRL-SHIFT-ENTER.
Hope this helps.
S S wrote...Overwrite a protected Formula
I need to be able to protect the formula in a cell and at the same tim
overwrite that cell. I am an estimator and for instance my materia
price may be unit price x quanity or I may need to put in a lump su
Thanks for the hel
Message posted from http://www.ExcelForum.com
you can't have both :-)
"utaheel >" <<firstname.lastname@example.org> schrieb im
> I need to be able to protect the formula in a cell and at the same
> overwrite that ...Presentation in Bar Chart
Good day all,
I hope someone can help me on this.
I have a table :
The result of the bar chart is like this :
But I want it to be like this :
Please advise how to get what I want.
Thank you in advance.
Have a nice day.
1. Click on the x-axis to select it.
2. Choose Format>Selected Axis
3. On the Scale tab, add a check mark to Categories in reverse order
4. Add a che...=Sheet2!$A$1 from string to formula
Cell A1 in Sheet1 holds the result of a concatenated
string and reads: =Sheet2!$A$1.
How can I make Excel display the value of A1 in sheet 2 in
stead of the formula =Sheet2!$A$1 (as a string)?
Can anyone help?
try the function INDIRECT, e.g.
Niels Govers wrote:
> Cell A1 in Sheet1 holds the result of a concatenated
> string and reads: =Sheet2!$A$1.
> How can I make Excel display the value of A1 in sheet 2 in
> stead of the formula =Sheet2!$A$1 (as a string)?
> Can anyone help?
You can build your ...expert with formulas needed again
I currently have this formula entered to calculate the meal allowance of
$8.00 if a value in another cell is over 5 hrs and 0 or blank if it is
I would like to go one more step. I would like it to calculate the
$8.00 if over 5 hrs, 0 or blank if under or blank hrs and $16.00 if it
is over 10 hrs. Can any one help me?
rvnwdr's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23903
View this thread: http://www.excelforum....