use a formula to reference a named range
Say I have the following dynamic named ranges (12 per year, but here are
and so on and so on back to 1999
Lets say in cell A2 is Data validation list with the years 1999-2004
Is it possible to put a formula in cell B2 that would be able to reference
the appropriate dynamic range based on the year value in A2? I did a google
search on the indirect() function, but I am unfamiliar with that function
and was not able to get any of the examples to work. I am hoping this is
possible as the only other options I see is trying to...Formula #52
I need help creating a a formula to accrue vacation time and subtract when
used. For example I need each month to be .83, but have a total of all the
time for each month so I then can subtract what has been used.
....put the number of the month you are in in cell A1 (Jan=1, Feb=2, etc.).
In cell A2, type "=A1*.83".
In cell A3, across to cell L3, type month abreviations (Jan, Feb, etc.)
In cells A4 through L4, type the time taken throughout the year.
In cell B2 type "=sum(A4:L4)"
In cell C2 type "=A2-B2" this will be time remaining at the current month
t...integrating access with excel formulas
i'm building upload proccess from excel to access,
i need to take some amount from the access and enter it into a formula
inside of the excel
for example min(amount-5,60)
during the upload access programming will exchange the amount text in
real amount and will upload the result
is it posible to do the folowing:
1.exchange the amount text in the amount from access2
2.to calculate the formula in excel and the sum will be uploaded to
3.still to leave the formula as is in the excel without revising(all
changes will be made by the programming and values in access
...AVERAGE formula with a twist
I need to average a range of cells B2:Y31. Normally, I'd use the formula
=AVERAGE(B2:Y31)... easy enough.
The twist is that I need to average that range of cells, BUT any cell that
has a value less than 1 or more than 900 cannot be used in the calculation.
These values are considered invalid for my purposes.
All cell values are referencing a cell value in another worksheet that is
part of the spreadsheet; for instance =bam!B2
How can I use a formula to automatically calculate the average of the range
without calculating the invalid values?
=AVERAGE(IF((...Missing "Save" and "Save As" functions
When I try to save a spreadsheet, I only have the option to save it as a
Workspace. The "Save" and "Save As" options aren't in the "File" menu at
all. Can anyone help?
does this happen with all files or only with a specific one?
> When I try to save a spreadsheet, I only have the option to save it
> as a Workspace. The "Save" and "Save As" options aren't in the
> "File" menu at all. Can anyone help?
It happened to all of them and is now resolv...Which formula to use ?
I've tried messing aroung with a couple of formulas in an attempt to sort
this out, however nothing seems to be resulting in what I'd like to
accomplish. I've done up an example of what I'd like to do...
I would like to be able to have a persons name entered into column A if the
number in column B matches the number that has been assigned to that person.
I would like for the formula to be the same for each entry so that I can
just copy it from cell to cell.
What I have in mind is something like "If 25678 = Mark Jones, then enter
Mark Jones in column A"
The numb...intergrating row number into a formula
Here's another one. I want to pull a value out of a certain column
based on the row that the formula is in. For example:
-The desired value is in A column (number value)
-The row() function will return the row that the formula is in
-Lets say row returns 13
-I would like to retreive the text value in A13 dynamically using row()
within the formula.....something like
-The "5133" would be the text value of the number from A13 as determined
Sorry if these are noob ques...naming formula
formula can be named. i click <ctrl+function key F3> give a name
"yearbefore" and in the reference type<=year(today())-1>
and in any cell i hit function F3 and select <yearbefore> enter twice I get
2002. But if I give the name as "lastyear" it does not work . it gives
<#name?>. what mistake am I doing?
if the formula contains a cell address it works but it seems to take the
e.g.name <lookup> and reference is <vlookup(a5,data,2,false)> it takes $A$5.
thanks and regards
AutoCorrect is cha...Nested If Functions #4
Below is what I wrote and I am missing a () I think.
What have I done wrong?
cefpe's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1357
View this thread: http://www.excelforum.com/showthread.php?threadid=26722
>Below is what I...International Language Functions
We are looking for the international eqivalents for the Excel worksheet
For Danish these are respectively
We are looking for the following languages. Can anyone help or direct us in
the right direction.
' Russian 7 (Russian Federation)
' Arabic 966 (Saudi Arabia)
' Czech 42 (Czech Republic)
' Farsi 982 (Iran)
' Greek 30 (Greece)
' Hebrew 972 (Israel)
' Hungarian 36 (Hungary)...Cell formulas that expire after a month
I have a spreadsheet that I'm using to track progress over the period
of a year. One sheet is raw data that is manually entered daily and
one sheet is monthly statistics based on that data. In the statistics
worksheet are cell formulas calculating statistics relating to each
month based on the raw data.
If I change the data in March, I don't want January's or February's
statistics to be changed.
Is there a way to do this?
On Mar 7, 11:52=A0am, PaulH <paul.h...@gmail.com> wrote:
> I have a spreadsheet that I'm using to track progress over the perio...Help with formula,
This is way above my basic spreadsheet knowledge so please if anyone can
I am trying to automate a small section of a booking form, Example
In C7 26, D7 5, E7 2008. This represents 26th May 2008
In G7 5, H7 6, I7 2008. This represents 5th June 2008
M7 to show �229, which is a weeks rental
M8 to show �229, which is a weeks rental
I am trying to get M7 and M8 to show the cost for a week or part of a full
weeks rental in these cells based on the info (dates) where the dates are
I have some cells below this where I can work out a discount for part weeks
etc.(I ...excel FX function
I am trying use the logical function to find the MIN of more than 2 numbers and then have the column heading presented in an adjactent cell from which the MIN number was found
if your values are stored in A2:X2 (with the corresponding headings in
A1:X1) use the following functions:
1. To get the lowest number:
2. To get the corresponding column Heading from row 1 use
> I am trying use the logical function to find the MIN of more than 2
> numbers and then have the colum...Formula to copy cell in Excel from on file to another
I have 19 excel files in a folder and I have created another excel
file (B) in which I am trying to set a formula that can look in Cell
"A6" which has the path with the file name of the first file (1) then
copy From the first File (1) cell "L25" an place it in Cell "Y6" of
I want the file names or file names with the path to be in file (B) so
that I can change the name easily instead of changing all formulas.
Hope I have clear explanation!
The function you'd want to use is =indirect().
But =indirect() won't work ...Format when viewing formulas
I'm trying to get two prints of the same document: one showing absolute
numbers and the other showing the formulas.
When I'm pressing ctrl+~ to show functions for my 2nd print, the format is
changing. The cells are getting too wide, so I end up getting only the left
half of the document in print preview. How do I change the document to show
functions with the format staying the same so I can print it??? Thanks.
Page Setup/Page/Fit to 1 page(s) wide.
In article <D2EAE056-FC1E-4F40-BA48-974B191C4B8D@microsoft.com>,
"bruno" <email@example.com...Excel 2003 Slow Function Argument Window
I've just installed Excel 2003 at home. When I select a function in the
Insert Function dialog, it takes FOREVER for the Function Arguments
window to appear.
The Arguments windows is animated to appear to pop up from under the
formula bar. I can count the seconds watching the animation of the box
appearing, and it's so annoying. In Excel 2000 I could have already
completed entering the arguements by the time the window comes up. None
of the other menus appear to be slow. I've tried looking for a way to
turn off menu animations both in Excel and Windows in general and
nothing seems...Formula Error #2
Using Excel - In column A I can enter 4 different cash values e.g. 30 / 13
/ 10 / 5
using the formula in column B/C/D/E =IF(A3=Rate_30,A3,"") 30 shows in the
cell B3 cells C3 / D3/ E3 for that row shows zero
However when I need another rate e.g. 50 to enter column F and changing the
above formula to =IF(A3=Rate_50,A3,"") the cell shows #NAME? - what am I
doing wrong ????
Any much appreciated
Cheers --- Mully
Rate_30 is a defined name, has nothing to do with the number 30.
You can either define a name Rate_50 (menu: Insert>Name>Define) or use...How specify number format of cell value in concatenate function?
When combining text and cell values in a concatenate function, I would like
to control the number format in the concatenate cell: comma separator,
decimal place, etc.
="this is text " & text(a1,"$000,000.00") & " due on: " & text(b1,"mm/dd/yyyy")
> When combining text and cell values in a concatenate function, I would like
> to control the number format in the concatenate cell: comma separator,
> decimal place, etc.
...Aaaargh! Functions missing from the new Beta?
This is a multi-part message in MIME format.
Ribbon/View tab/Filter Messages
- same 4 options as in Wave 3 RTW version(Show All, Show Downloaded, =
Hide Read, Hide Read or Ignored)
I...why does Equal sign not appear on the formula bar #2
next to the function button as it did in Excel 2000?? Why did they create
this function wizard and make things more cumbersome??
Only the developers know why they did away with this in Excel 2002 and 2003.
The return of this feature is on the wishlist for the next version.
Adoption and Implementation of features on the wishlist is a whole 'nother
Gord Dibben Excel MVP
On Mon, 24 Jan 2005 15:17:06 -0800, pvmonsour
>next to the function button as it did in Excel 2000?? Why did they create
>this function wizard and ma....Seriescolection(n).formula
Thought I might use arrays to collect chart series X and Y values from a mass
of data (simplified example code below)
I found that with simple integers I can load a maximum of about 140 values.
With DateTime numbers this comes down to about 16 maximum.
Is this to be expected? (Excel 2003)
Dim myChtObj As ChartObject
Set myChtObj = Sheets("chart").ChartObjects("Chart 1")
.SeriesCollection(1).Values = Array(4,5,6,...,n)
.SeriesCollection(1).XValues = Array(1,2,3,...,n)
The Series formula is limited to an absolute maxim...Multiple "IF" functions one one cell returned from different cells
OK, maybe that wasn't the best way to ask the question...
I need to create this scenario:
Question: "Lot Size?" Answered by "X" under "Small"(A3) "Medium"(A4) or
If (A3) = "X", then (J2)= 500
If (A4) = "X", then (J2) = 600
If (A5) = "X", then (J2) = 700
Obviously, these function will have to recide in the J2 box. I can get it to
work individually using the "=IF" function, but how can I insert multiple
"=IF" functions into the (J2) box?
I have tried using the "=OR"...Including cells that are highlighted in a formula
I have a strange requirement maybe, but I do a budget spreadsheet on which I
highlight items that have cleared my checking account. I would like to be
able to write a formula that sums only the cells that are highlighted a
certain color. Is this possible?
let me clear this up a little. I don't really mean "highlighted" as in I
highlighted them with a mouse. What I mean is formatted with a fill color.
sorry for the confusion.
> I have a strange requirement maybe, but I do a budget spreadsheet on which I
> highlight items that have ...... need formula...
I need formula which calculate finish day.
I have start day and number of hours for task - how to calculate
automatically finish day??
jkazan's Profile: http://forums.techarena.in/members/134110.htm
View this thread: http://forums.techarena.in/microsoft-project/1294750.htm
Why don't you let Project do the calculation? That's the point of the software....
- Andrew Lavinsky
> H...Special Pasting a work book with many sheets and formulas
I have a workbook with many sheets that all have formulas and links to other
data. I want to save the workbook as another name with all the worksheets
keeping the values only (no links or formulas). Is there a quick way to do
this for everysheet without having to special paste every sheet in the
workbook. So can I save everysheets data values at workbook level.
See this page for a code example
Regards Ron de Bruin
"lex63" <firstname.lastname@example.org> wrote in message news:ED708...