Named Range Scope
Excel 2007, New, Loan Amortization (Template),
Select cell D5
Loan_Amount, Delete, Ok
New, Name: Loan_Amount, Scope: Loan Amortization Schedule, Ok, Close
It appears that I can change the scope of all names in this workbook
from global to local except
Int_Rate, Loan_Amount, Loan_Start, and Loan_Years. How come?
...AutoFilter list of values
When I activate the auto filter, a drop down lists the unique values within
the column. How could I get this list of unique values?
As far as I know this can only be done with advanced filter.
Select Data-Filter-Advanced Filter and check the Unique records only
Another way to extract the list of unique values
Suppose the data is in col A, A1 down
Put in C1: =IF(COUNTIF($A$1:A1,A1)>1,"",ROW())
Put in B1:
Select B1:C1 and copy down till the last ro...find maximum of two values in an array with same lookup value
Table containing team names and members of teams of two and their scores in a
number of competitions. Require to return the highest scorer for each team.
Can use vlookup to return one of the records for a team though not
necessarily the team member with highest score. Can't sort the array by
descending order of points scored because the highest scorere for any given
team will not be the same in every competition. Could probably think of a
way of doing this by writing some VBA but if anyone can think of some way of
avoiding that.....all ideas welcome
How is the data listed in th...highest number #2
Let's keep the problem simple.
I have one cell that uses an exchange rate from the internet.
That cell changes every minute.
What I want to do:
In an other cell, I want to keep the highest exchange rate ever.
So for example:
6:00 exchange rate = 5.00 -- highest exchange rate is 5.00
6:01 exchange rate = 4.98 -- highest exchange rate is 5.00
6:02 exchange rate = 5.02 -- highest exchange rate is 5.02
6:03 exchange rate = 5.01 -- highest exchange rate is 5.02
6:04 exchange rate = 5.03 -- highest exchange rate is 5.03
Of course nor for any minute I use a new cell, it just change the
n...How do I sum times to get a total hour value? If I add up to 24 h.
I am trying to create a timesheet that totals daily hours then totals those
hours. When the sub totals raech 24 they displayed total shows as 0 instead
of 24. Thus a 40 hour week shows as 16 (40 - 24) . Any help willl be
On Thu, 16 Dec 2004 19:29:02 -0800, "Mico" <Mico@discussions.microsoft.com>
>I am trying to create a timesheet that totals daily hours then totals those
>hours. When the sub totals raech 24 they displayed total shows as 0 instead
>of 24. Thus a 40 hour week shows as 16 (40 - 24) . Any help willl be
>appreciated...Sum (throw out highest and lowest)
I am working on a contest scoring sheet for the California State Old Time fiddlers Association and it is contest policy to throw out the highest score and the lowest score and add the remaining middle (3) judges scores. (There are generally 5 judges - don't think there would ever be more than that.)
The total of the middle three is used for future ranking points added to future scores, so "average" won't work in this application. Right now we are working this out manually - sure woudl be sweet to be able to have a formula that works it out for us. Thanks for any help.
...how can I link the status of a checkbox in excel to a field value.
I am trying to develop a form that can be filled in on the screen yet looks
decent when printed. I have managed to insert checkboxes for a true or false
question, however I can not figure out how to refer to whether the checkboxes
are checked or not through a cell function.
The reason is that I would like to refer to the checkboxes in a conditional
If you got the checkbox from the Forms toolbar, right click on it and select
Format Control|Control Tab
Assign it a nice cell link.
Then when the checkbox is checked, you'll see TRUE in that cell.
If you got the checkb...Printing value only cells
Please I have two queries:
1)I will like to be able to print out or printview only the cells o
on my worksheet that contain currency values other than 0s an
depending on the specified range without highlighting the range.
2) Is there any way by which I can copy from a sheet and paste ont
different sheets in the same workbook at the same time.
Thank you for helping out on my last query for the count betwee
Message posted from http://www.ExcelForum.com
1. See: http://www.rondebruin.nl/print.htm#Hide
2. Try selecting all sheets while holding down the SHIFT key (grouping
the...highlight range, apply calculation to data in cells and paste special to same range
I know how to select a range of cells and copy:
1. applying the calculation, which should be value of cell * 1000
2. special past values only to same selected sells
You enter 1000 in a separate blank cell and copy it then highlight the range
to be multiplied and past special-> multiply. that will multiply all the
cells by 1000 in the pasted range.
"S Himmelrich" wrote:
> I know how to select a range of cells and copy:
&g...check or uncheck a check box based on a cell value
IN EXCEL, I WANT TO HAVE A CHECK BOX CHECKED IF A DOLLAR AMOUNT IS ENTERED IN
A CELL AND UNCHECKED IF THE CELL IS ZERO
1. Please, do not use all capitals, it's as if you are shouting!
2. If the cell with the $ amount is A1 then enter =A1<>0 in B1 (or any other
empty cell) and give B1 as linked cell of the checkbox!
„RTKCPA” ezt írta:
> IN EXCEL, I WANT TO HAVE A CHECK BOX CHECKED IF A DOLLAR AMOUNT IS ENTERED IN
> A CELL AND UNCHECKED IF THE CELL IS ZERO
...highest second highest and third highest
I have one lists of number in A1:A7 and a list of words in B1:B7. I need to
find the highest, second highest and third highest value in these lists and
display the value and the word next to it in cell C1,C2, D1,D2 and E1,E2
respectively. However, there are same values for highest, second highest and
third highest in these list. If the value is the same, can I have it
For example: A1 B1
Assuming both 100 is the highest. I want both Car and Bus to be shown in
cell C1. Possible ?Or any o...Advanced Filtering top values
Basicly what I need to be able to do is to advance filter a list by
the top 25 values in column E. I have a crietra that consist of
filter by year, product and value . Year and Product are easy it is
trying to get the list to filter by the top 25 values that is hard.
What formula do I put in the critera cell that will allow me to filter
the entire list based on the top values
Excel 2007 Advanced Filter
Macro, with top 10 filter.
Alternate solution: PivotTable:
No code, no formulas needed.
...Insert Into Values Question
INSERT INTO tblFileTypeDateExport (FileName)
VALUES ('A0-' & Format(Date(),'mmddyyyy') & '.xls')
This Does not....
INSERT INTO tblFileTypeDateExport (FileName)
VALUES ('A0-' & Format(Date(),'mmddyyyy') & '.xls'), ('B0-' & Format(Date(),
Thanks for thinking about my question!
Message posted via AccessMonster.com
Never mind, got it sorted out!
Message posted via http://www.accessmonster.com
...Highest value on report
Hi, I have a report with textboxes that dlookup, davg, dmax and dmin values
from a couple of tables. I have about 5 dmax ones from different fields, so
it's like a high score style thing, though they aren't ordered in ascending
order, so is there a way to make a label saying 'HIGHEST' next to the textbox
with the highest amount. Basically I need to determine which text box on the
report has the greatest value so that it can be highlighted. Any way to do
Use the OnFormat event of the section that contains the 5 calculations. (ex.
fields T1, T2, T3, T4, T5)
...Conditional Formatting of Cells based on discontinuous range of numbers
I have a column of numbers that I want to match to another column.
In column C, I have numbers that have discontinuous values from 0 to
10,000. In Column N, I have a similar but smaller subsample of
numbers (again ranging from 0 to 10,000). I would like to make all of
the cells in column C that have a matching number anywhere in column
N, yellow. Is this possible? Conditional formatting does not seem to
work on ranges. Thanks in advance.
select the range for col C , go to format | conditional format |
condition 1 : formula is =3DNOT(ISNA(MATCH(C1,$N$1:$N$11,0))) ...Last "Numeric Value" in a column of formulas
I have formulas in column A.
I will sometimes enter a numeric value in one of the rows of column A.
How do I find the last Row in which I have entered a numeric value?
Example Column A (below) should produce a result of Row 10 because 7
is the last numeric value:
Thanks for your help...
On Mar 20, 1:14 pm, iamnu <iamn...@gmail.com> wrote:
> I have formulas in column A.
> I will sometimes enter a numeric value in one of the rows of column A.
> How do I find the last Row in which I have entered a numeric value?
> Exampl...Three Highest Values
I need to link the three highest values from a column to another worksheet. I
also need to include two labels for each value. For instance: I have an
amount of scrap parts, which is a calculated value. I need to find the three
highest offenders, and the part number and operation. I have tried everything
I can think of.
see response in worksheet.function
"chrish" <firstname.lastname@example.org> wrote in message
> I need to link the three highest values from a column to another
> ...Excel cells with formulas only will show a 0 value
I have a workbook that will only show a 0 value in the cell when I enter a
This workbook used to work just fine and for some reason now it is crazy.
I have tried changing the format of the cell multiple ways, have tried F2
and enter, and still nothing helps...
I also have tried copying a pasting the portion of the sheet that has data I
need into another worksheet and then try to make a cell = another cell's
value and it displays 0 still. I have also tried copying into an entirely
new workbook and doing the same thing... still 0 as the value.
Anyone have any idea what i...checkbooks value date
The value date is given to the transcation by the bank in order to calculate
the charges or interests.We need to track it using a specified field in GP
in order to know which decision to take related to bank deposits and
Joseph Abou Nader
You never know what power you have until you make choices in hard time
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to op...VBA sort range with certain fontcolor
Ik column K there will be a continuous range of which all cells have a
certain fontcolor, the font color code is, say, 5. The range can be 1 cell up
to 30 (or so), perhaps even zero. There are no cells in column K outside that
range that also have font color code 5.
While executing the code it is not (yet) known what the rownumbers of the
first and last cells in that range are.
I need code that will sort (descending) the following range:
from row of first cell (in column K) with font color code 5 and column A
row of last cell (in column K) with font color code 5...Sumif all Null Values
I have a column with a formula that on certain conditions populates
the cell with a null value. ("")
Is there a way to use Sumif that sums an adjacent column when the
first column is null?
1 12 50
3 4 300
I want to do a sumif for column A when value = "" then sum column B.
The result would be 300.
One complication is that column A is an "If " formula that sometimes
populates a cell with ""
Can this be done. I appreciate your help.
Am Fri, 2 Jul 2010 13...Second highest frequency
I have a series of number from A1:H1 (7,3,7,1,6,8,3,7). I need an Excel
formular to describe the phenomenon of this particular series number: the
If I want to to know the highest frequency number, the formula of MODE can
give me the answer which is 7. However, I also need an formular which can
give me answer: 3, because 3 appears two times in the series. The formular
MODE only will give me the highest frequent number, that is 7. So, I don't
know is there any formular can detect the second highest frequent number.
Message posted via ...Merge items having similar row values ?
i need some help on this one, say i have file which has the followin
so is there any way in excel in which i can have a file which
Tool l User
How will i do this ?
Any delimiter will do( i have used a comma here)
Thank s in advance
Message posted from http://www.ExcelForum.com
You didn't like last Friday's suggestion????
http://groups.google.com/groups?threadm=4089BA0B.69E6...counting rows with same values for multiple values
I have a list of 150 assets which are assigned to 20 or so depts. How can I
count the number of assets per dept. In essence counting the number of times
different values reoccur? For example
Asset1 - dept1
asset2 - dept 1
dept1 has 2 assets
dept2 has 2 assets
dept3 has 1 asset
Id like to avoid doing a COUNTIF and having to type each dept name
Jon Viehe Wrote:
> I have a list of 150 assets which are assigned to 20 or so depts. Ho
> can I
> count the number of assets per dept. In essence counting the number o
> different val...How can I include a workbook name when specifying a Range in VBA code?
How can I include a workbook name when specifying a Range in VBA code?
Dim WB As Workbook
Dim WS As Worksheet
Dim Rng As Range
Set WB = Workbooks("MyWorkBook")
Set WS = WB.Sheets("MysheetName")
Set Rng = WS.Range("A1:D100")
"keithb" <email@example.com> wrote in message
> How can I include a workbook name when specifying a Range in VBA code?
Just to add, if...