I got this response (which worked)for a question I had on a formula and not
to sound silly but what does the double quotation mark mean in this formula?
It is a default action, if no other condition is met, it returns an empty
(remove xxx from email address if mailing direct)
"las" <firstname.lastname@example.org> wrote in message
> I got this response (which worked)for a qu...If statement with lookup array... any advice?
I am trying to automate a spreadsheet. The basic formula is this... Any
suggestions or corrections would be appreciated.
=if(Logical_test, lookup_array, lookup_array)
I have also tried
=IF(...cell formula not updating right after insert row
I have a formula in a column as follows - the details of what it does are for
the most part unimportant (this one is at row 50):
=IF(D50<>"",D50-SUM(G50:AA50), IF(C50<>"", E49 +C50-SUM(G50:AA50),""))
I frequently end up inserting rows and copying the above formula to the right
column for the new row. What happens though is that this formula and all those
following get messed up as so:
=IF(D51<>"",D51-SUM(G51:AA51), IF(C51<>"", E49 +C51-SUM(G51:AA51),""))
Everything got adjusted except for...nested formula help needed
This is what the help menu tells me.
"Nest no more than seven functions You can enter, or nest, no more
than seven levels of functions within a function. Learn about creating
I was curious if anyone could tell me how to write a IF statement with
more than seven functions? Is it even possible? Is there a way to
combine functions to give the same effect? I am trying to write one
with approximately 25. Any help would be greatly appreciated. I have
tried just about everything and am very stumped.
~~ ...Sheet names used in formulas
I have a question about using sheet names in a formula. I would like the
sheet name to go up by one page for each row I put the formula in...(ie)
13A reads (="S40'!$W$5)... I need to have row 14 read (='S41'!$W$5)
There are many many worksheets...260 in all that need this on them. Each
worksheet is about 300 rows long. How can I do this without having to change
the sheet name by hand???
Any help is greatly appreciated!!!
Here's one way:
Which evaluates to:
As you drag copy down:
=S40!...Copying result of formula into another worksheet???
I am having problems copying a formula result into another wksht if the
formula is not used...hard to explain...
I want to transfer D10 to wksht Data E12, however if there is no data in
A1:A5, I want E12 to remain blank.
It looks like it is reading the formula, so it places a "0" or a "-"....
Tools -> Options -> View
Turn off zero values should get you part of the way.
> I am having problems copying a formula result into another wksht if the
> formula is not used...hard to explain...
> D10=S...Best ways to search an array/collection for an element
I'm creating a small library of utilities for VBA programming, and
right now I'm completing my ArrayManagement and CollectionManagement
module. I was considering different solutions for the classical case
of searchin an array or collection for a specified elements. So far
I've considered using Worksheetfunction.VLOOKUP,
Worksheetfunction.Match (needs some care to take into account the
"element not found" case) for the arrays. I also developed an handy
binary search function for sorted arrays, but it works only for 1D
numeric arrays (sorted in either no...formula for updating 1 spreadhseet from another
I have two spreadsheets one for "current accounts" and one for "past due accounts". The "current" spreadsheet list all our receivables from this year even if they are still outstanding, the "past due" spreadsheet list only those that are 30 days or more past due. What I currently have to do is if a vendor pays an invoice update the "current" spreadsheet and then if its a past due go an update the "past due spreadhseet". My question: Is there away to automaticly have the "past due" spreadhseet update itself if I enter a payme...protecting formulas
I am trying to find a way to protect formulas on a spreadsheet whil
still allowing users to put in new data. Is there a way to do this?
am using excel 200
Message posted from http://www.ExcelForum.com
unlock the cells where you want input and then protect the sheet with a
Format | Cells... | Protection tab | Locked = false (not ticked)
"Cherilyn >" <<Cherilyn.email@example.com> wrote in message
> I am trying to find a way to protect formulas on a spreadsheet while
...Excel formula problem #2
What I am doing is programatically creating the HTML/XML structure o
an Excel 2000 document. I am almost done, however I am having problem
with some excel formulas.
I have no access to the actual column names (A, B, C,...etc.) I onl
have integer vales. So, basically I am trying to map these intege
vales to the actual column names.
So, for example, if I want to check to see if a particular text valu
is there in a range (A1, A3), the following works:
MATCH("MYTEXT", A1:A3, 0)
What I try to do is the following:
MATCH("MYTEXT...Linear Versus Moving Average
I am describing a trend in monthly Immunization totals to hospital
management. Immunizations are up 17% over last year's total at this time.
However, a simple linear trendline points downward because of seasonal work
patterns - very high in the beginning, then lower, then gradually bending
upward in the last several months. A moving average "trend" merely
approximates this year's work pattern - so, why display it at all? Yet, if I
were to choose to display a trendline, a moving average approximates the
actual peformance of this department. Is it useful to includ...How do I copy a cell + it's formula from one workbook to another?
For reasons i won't go into, i have to transfer hundreds of cells from one
workbook to another. each one of these cells contains a formula
the new workbook is an identical...IF Formulas with Ranges
I'm trying to create an IF formula where the workbook will recognize if
there is data added to a column D range (Sessions) and then apply a
formula to the last cell of column F range (Aimline).
Hence, if Sessions (column D) which now has the numbers 1-27 in it gets
28 entered below, then the cell in the same row but column F will take
the number 28 and apply the formula above it =$F$8+($A$12*D35).
Can anyone help?
made an assumption that D35 should relate to D28
(remove nothere from email address...VBA Formula Problem
I am using the code below and get an error with the formula statement. I
need help. TIA
For Each c In Range("LName")
c.Formula = Upper(Left("D2", Find(",", "D2", 1) - 1))
the formula needs to be a string. D2 should not be in quotes either:
c.Formula = "=Upper(Left(D2, Find("","", D2, 1) - 1))"
Note that this will set the entire range to read from D2. If you
want it instead to read from column...excel array formula
I want to use array formula's to reduce a very large spreadsheet. VBA
and macros are not to be used, due to the webpublication of this
Is there a way to reduce a simulation run with repetitive formula's,
while at the same time the intermediate results are still available.
I came this far:
Run Formula_1 Formula_2 Formula_3
1 =con1 + D40*con2
2 =con1 + D41*con2
3 =con1 + D42*con2
4 =con1 + D43*con2
5 =con1 + D44*con2
6 =con1 + D45*con2
7 =con1 + D46*con2
8 =con1 + D47*con2
9 =con1 + D48*con2
10 =con1 + D49*con2 =con3*D50 =SUM(con3*D41:D50+F40:F49)
Is there a way...help with a unique formula question
Ok. I have a workbook, in this workbook there is a worksheet called:
on the main worksheet I have a import button that I press,
which fetches a daily roster file from the network and it imports the
files into the workbook into a sheet called:
So there are two sheets: main and master.xls (with the .xls extention)
Before I go on to explain what I mean by help with a formula, let me
explain what the master.xls sheet has:
A1:D1 headers, data starts at A2 and below.
Daparment Name, Date, Employee Name, HR status code
So for example, lets use these values, A2:D2
...Finding Location of Maximum Value in 2D Array
I am using MS Office ExCel 2003(11.6560.6568) SP2 and have a single
sheet with a 2D array of numbers roughly 202x202 in size. Could
someone tell me the quickest way to locate the maximum value in the
array? I tried =WhereMax("Sheet1") but just got #NAME? as the result.
Any assistance would be greatly appreciated,
Dim r As Range
Set r = Selection
v = r.Cells(1, 1).Value
For Each r In Selection
If r.Value > v Then
v = r.Value
s = r.Address
MsgBox ("maximum value " & v & " found in cell &...Formulas #22
how do i add a compounding number in a formula?
150+25+25 OR 150+50=200
200+25+25+25 OR 200+75=275
i think i need the formula to compound 2 things
the number i start(A)with and also the number i add to (A)
Copy A1 down as far as desired.
In article <firstname.lastname@example.org>,
email@example.com (Dianna) wrote:
> how do i add a compounding number in a formula?
> example: 125,150,200,275,375,500
> 150+25+25 OR 150+50=200
> 200+25+25+25 ...how can i change a value in a cell from a formula in another cell?
A formula can only update the value in the cell that holds it.
On Tue, 5 Jul 2005 05:00:01 -0700, "davidhub"
In the cell that you wish to change (e.g. A2) enter a formula that refers to
the cell with the formula (e.g. A1)
For example, in cell A2 enter:
If having a formula in A2 is not acceptable, then you will need to use a VBA
solution. Using an event macro, you can check the contents of A1 and write an
appropriate number into A2.
To enter the VB routine, right click on the workshee...Need help on a formula
I need a formula that will take the following information from cell A3
and populate it in A4.
Here is the information in A3 - jones123456
I need a formula in A4 that will take the info in A3 and create an
email address that has the last name AND the last 3 digits of the
number, plus the domain.... so that the data will read in A4 -
I have the first part of the formula like I want it to populate the
last name =IF(A3=0,"",A3). But then I don't know how to get the last
3 digits of the number AND The domain name added to the last name in
A4. Here is what I ...Query formulas (dates in formulas to be changed only once)
I have several queries that contain several hard-coded formulas. If
possible, I would prefer that the formulas be referenced in a control or
setup table. Does that make sense? Instead of going through my queries each
month and updating hardcoded dates, I would like the dates to be changed only
once and have all the formulas reference this source.
It makes sense to do this. How you do it depends on what the formulas are?
For instance if you are just trying to get the data for a prior month, you
can change the formula to get the prior month based on the system ...Link VBA
I am working on an VBA application that needs to send an array as an
argument to an external libray written in C. The library uses the array
as a vector of parameters, and then returns a certain value.
The link is working well with a single argument (a double, an integer,
etc), by reference or by value. But when I want to send an array (a
vector of double for instance), it fails.
Does anyone know how to do this? First, is it possible?
The only way I have succeeded in passing or returning arrays is
abandoning the VBA declarations and writing an xll, ...Alternative to array formula to get answer?
I have an Excel database of around 5,000 rows. One of the fields
contains a date. I colate the data on a separate sheet into
categories for a specific date by using the following array fomula:
B78 contains the date required and column Z contains the revenues I am
wanting to add together for that particular date, in order to get a
Is there any other way of adding together the revenues of a number of
records for a particular date, other than using the array formul...Entering Formula
I'm using Excel 2007
When I'm entering a formula into a cell, the text of this formula if longer
than the cell covers the cell to the right and then I cannot click on this
cell to automatically put that reference in the formula. The same happens
when you are in a table, but the formula is centered on the cell, expanding
left and right. The names of the cells in a table are very long
'Table4[[This Row],[Column Name]' and thus is you select a few cells the
formula description is very long and you cannot 'click' on the cells to the
left or right.
Not sure if this can be done. A continuous roster of 3 panels (teams)
working 7 days, 7 nights, then 7 off, the three panels overlap to ensure
coverage.. is there a function that will assist with this?
Not really but there are those that have made these
"KAT" <KAT@discussions.microsoft.com> wrote in message
> Not sure if this can be done. A continuous roster...