Your formula contains an invalid external reference to a worksheet
I'm sure this has been asked before, but I've just wasted a whole day
trying to find a solution and got nowhere!
I have a complex spreadsheet which I inherited from my predecessor.
It contains many sheets, plus macros, chart objects, text boxes, auto
shapes, etc. When I try to close the spreadsheet it objects "Your
formula contains an invalid external reference to a worksheet".
Try as I might, I just can't pin this down. I've tried the obvious,
such as searching formulae for '[', looking in named ranges and
asigned macro names to no avail. In desperation ...Formula #27
How do I enter a formula to calculate a 7% sales tax?
If A1 holds the pre-tax price then =A1*7% will compute the sales tax while
=A1*1.7 will compute the price_with_tax-included.
Now all this is mathematically correct but we work in dollars and cents (or
pound and pennies etc.), so we need to do some rounding to the nearest cent
sales tax: =ROUND(A1*7%,2)
Bernard V Liengme
Microsoft Excel MVP
remove caps from email
"MissM07" <MissM07@discussions.microsoft.com> wrote in message
news:344FF6D4-...Advanced Formula troubles
I need to do the following calculation:
but only when:
((1-((1-AE5)*10))*V14)>=0 or <=V14*1.5
If greater than or = to V14*1.5 then =V14*1.5
And if less than or = 0 then =0
Replace CALC with your calculation
Your last statement is confusing. "And if less than or = 0 then =0"...equal
to zero is mentioned in the 1st condition..So this should be 'less than' .
Jacob (MVP - Excel)
> I need to do the following calculati...Works in 2003 but not 2007
When I reference the below function in 2003 I get function displayed... now
in 2007 I get
Any idea what I need to change/set in 2007 so it will run this function?
Function formulaText(x As Range)
'returns the text equivalent of formula in upper left cell in range x
'example: =formulatext(a1) returns the formula found in cell a1 of
'example: =formulatext(a1:b5) returns the formula found in cell a1 of
formulaText = x.Cells(1, 1).Formula
It works fine for me. Are you stored in a stan...Need Hyperlink from Menu Page to work in email
I have a workbook with several tabs. First tab is a "menu" type page
(summary of other pages). Currently I have hyperlinks to all the other pages
from the menu page. What I want to be able to do is copy the menu page into
an email and enable the users to click on the existing hyperlinks (whatever
adjustments needed) to go directly to their page of the workbook. Using
Excel 2007. Please advise how to do this and what changes may be need for
both options to work.
...Using cell contents in a formula to refer to another file
On Friday, I asked a question and was given INDIRECT and/or INDIRECT.EXT as
I finally managed to make INDIRECT work but I cannot make INDIRECT.EXT work.
My problem: 120 files with identical architecture - two worksheets each. I
would like to build a new file to give me certain data to be found in each of
the 120 files.
Each file is named after a person. In the new file column A will list each
person. I would like to put in column B:
Please note that although I am English, my Ex...Please help with last formula for order form.
I am able to accomplish this with 1 column by the formulas below.
Cell H160 is the subtotal: =IF(SUM(H72:H111)>0,SUM(H72:H111),"")
Cell H166 the total: =IF(SUM(H160)>0,SUM((H160*H163)+H160),"")
Cell H163 is for Tax.
I am almost finished creating an order form. I would like to get the SUM
of 3 different columns that are separated. I am not able auto fill
strait down the column, because the information is separated in groups
with titles, and the cells are not identically sized.
I tried varations of this formula:
=IF(SUM(H72:H111)+(116:131)+(135:154)>0,SUM ((H72:H...Summary of Difference between dates in years, months, days
I need to calculate the difference between 2 dates and then total them.
Here's what I have so far:
From To Length of
01/09/2003 31/01/2010 6y 4m 30d
01/06/2000 30/11/2002 2y 5m 29d
Total of Service: ??????????
I've used the following formula to calculate the total days worked:
=DATEDIF(A4,B4,"Y")&"y "&DATEDIF(A4,B4,"ym")&"m "&DATEDIF(A4,B4,"md&...OR formula, ?????
I have Worksheet A containing a list of data:
Red, Yellow, Orange, Purple which is defined in NAME MANAGER as COLOR
Worksheet B on CELL A1, is user input data
A2 has the following formula
User input : Red = FALSE
Yellow = FALSE
Orange = True
Purple = FALSE
The OR formula should produce TRUE value on the cell for all input that is
true. However, it is not the case. Where is the formula wrong?
Try this instead:
Hope this helps.
On Feb 24, 12:19=A0am, a...Rule to forward mail to external address doesn't work
It might be an XCON issue but I didn't find the corresponding group so I
post my questions here.
I set up a rule that when an email arrives if my name is in the "to" or "cc"
field then forward the email to another external SMTP address for example an
hotmail address. however this rule never works through.
It's a Exchange 2003 SP1 on Windows 2003 SP1
I have no problem to manually forward the email to that external SMTP
If forward to an mailbox in our Exchange Org, it works;
I turned on diagnostic for Transport, no error or warning logged;
I tracked mes...Server Performance report today
It appears that all SBS 2k3 R2 reports are broken this morning. It may have
something to do with the large number of updates that were sync'd overnight.
The normal tricks to get the report working, aren't.
Les Connor [SBS MVP]
"Les Connor [SBS MVP]" <les.connor@DEL.cfive.ca> wrote in message
> It appears that all SBS 2k3 R2 reports are broken this morning. It may
> have something to do with the large number of updates that were sync'd
> ove...IF formula not working
Can anyone tell me why this IF formula isn't working?
It's returning "B" every time. D5, E5, and F5 are all derived from
formulas. Is that the problem? I appreciate any help!
"MelissaS&qu...Number of items per day in chart.
Hello all, I have a list of dates and times ( listed as 1/18/2006 1:30:10
AM in cell) that indicate a sale that took place. What I am trying to do is
create a chart from this list that indicate the number of sales per day. I
would like the chart to have the days of the month accross the bottom and a
bar indicating how many sales per that day. Can anybody help me with this?
Whenever i try and use the bar chart it shows me a cumulative total
(includes the previous sales from previous days).
Also, I am using Excel 2003.
Any help would be appreciated,
Let's assume you data...simple countif formula
Column B in my spreadsheet consists of 10 rows with one letter in each cell.
I would like a formula to count cells B1,B3,B5,B7 and B9 only if the value
in each of those cells is "H". I have tried a simple formula like this
=countif(B1,B3,B5,B7,B9,"H") but it does not work.
Thanks for your help with this formula.
"Brian" <Brian@discussions.microsoft.com> wrote in message
> Column B in ...Working on Staffing grid
I'm working on a staffing grid to determine the number of staff neede
depending on the number of clients.
Licensed staff = 1:5 ratio
Assistant = 1:8 ratio
What is the formula so if I enter the number of clients it gives m
two separate values (one for Licenced, 1 for assistant)into tw
Simple to do but I'm new to this
cardingtr's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2702
View this thread: http://www.excelforum.com/showthread.php?threadid=...Formula Help (to many expresions)
Could one of you give me a hand with this...
I'm trying to put a formula in a spreadsheet that has too many
expressions in it. I understand there is a limit to the number of
equations that can be in a formula but there must be a way around the
Or maybe another way to write the formula?
What I am trying to say in the formula is that if...
If X is less than 09 then B1 = what's in cell C2
If X is less than 25 then B1 = what's in cell C3
If X is less than 51 then B1 = what's in cell C4
The expression I have written looks like this...
=IF(X<10,"N/A",IF(X<...calculating the work week
Is there a way to calculate the workweek in a particular year, given the
year, month, day, and the day number in that year such as 115. I thought
maybe using strftime would work, but it always returns the current time,
date, and workweek.
This is what I tried:
CString CUtilities::ConvertDaysToWeekOfYear(CString year, CString month,
CString day, CString yeardays)
tmp.Format("Days of Year: %s\nMonth: %s\nDay: %s\nYear:
struct tm *newtime;
time(&aclo...Why does copy and paste between eMails not work?
Copy no longer copies to the clipboard. Is there a setting I have deleted
somewhere? Using Windows 7 and Office 2007 proffessional.
Does it work if you restart outlook?
Do you have any addins installed that could be erasing the clipboard? I've
seen this happen with addins and contacts, but not email. to test, open the
new message form before copying so you just need to copy and switch to the
other message. (http://www.slipstick.com/Contacts/clipboard.htm)
Diane Poremsky [MVP - Outlook]
Outlook Tips: http://www.outlook-tips.net/
Outlook & Exchange Solutions Cent...Unable to access exchange server with entourage (however other methods do work)
When I try to connect to our exchange server with entourage, all i get
is the following message:
The server can't be found. Be sure the mail server information is
entered correctly in the Account Manager, and that your DNS settings in
the Network Control Panel are correct.
The strangest part is that i can access our exchange server through
webmail (no problem) or through apple's mail.app (no problem) either.
Can someone help?
On Thu, 28 Oct 2004 14:16:12 -0500, Laurent Lamblin wrote
(in article <2004102821161216807%hush@skynetbe>):
> The strangest part ...no mail showing in inbox older than today
With a fresh install of XP SP2 and Office 2003 SP2 on my desktop I have
nothing showing up in my inbox later than this morning. This was a default
install and I did not make any changes to the settings. My laptop with a
fresh Office 2003 install shows the mail in my inbox.
Where should I look for a setting to make this work? I am using the Messages
view without a preview pane on both machines. In an earlier message in the
forum someons suggested to another user to enable tyhe all mail view. I do
not see a view with that name listed on any menu.
Click View | Arrange By | Current View ...Outlook and Hotmail -- now works again! For some not all?
Received from MS:
<<Recently, Hotmail� announced that ... Hotmail will no longer
allow new e-mail accounts to be accessed via Microsoft� Office
Outlook� and Outlook Express.
<<We are pleased to inform you that because you are an existing and
valued customer, at this time your current Hotmail and MSN
account(s) are exempt from this restriction and you will be able to
continue enjoying access to those accounts from Outlook or Outlook
Express. However, any new Hotmail or MSN accounts you create will
not be accessible via Outlook or Outlook Express.>>
....an "...How to generate list of items with same status over three or more successive days
I would like to ask the community to help me with the following case.
We do have a vehicle tracking business and need to monitor vehicle
status every day.
We are looking for a way to get a list on a daily basis of vehicles
that has their status not changed over three days.
Columns of the feeder:
Date VehicleNr Status (consider A, B, ..., F)
The idea is to add the feeder on a new sheet, so comparison will be
over more sheets.
Columns of generated list of vehicles with same status over more then
List generation on the same sheet that is refreshed daily.
The li...Combo Boxes stop working in Access 2003 when you install Office 2
After installation of Office 2003 SP3 a combo box in an existing database no
longer displays the contents of the list.
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
"burnzie_13" <email@example.com> wrote in message
> After installation of Office 2003 SP3 a combo box in an existing database
> longer displays the contents of the list.
...macro to action formula
sir, this is my first qst. I have a workbook with sheets for each date x so
almost 30sheets x and I am using cpy data macro to copy all these date to one
sheet x whenever I run the macro this sheet is newly created with the latest
date x I want to use sum and sumif formulas to calculate this sheet data x
can u adv a macro to do this
pvkkutty new to discussion group however a freequent reader of discussion
It would be better to just have ONE sheet with all and use data>filter
to see for each day desired.
If desired, send your file to my address ...How do I get powerpoint to update data daily from excel formula?
I have a presentation designed for an office lobby and cannot figure out how
to have an excel formula embedded into the slideshow change without shutting
down the presentation and restarting it. The formula displays the number of
days without a safety mishap. I thought by making the powerpoint show a
playlist it might update...it didn't work. Any ideas would be appreciated?
Brent, for an embedded object you will have to go in and change it manually
to get it to change unless you mean a linked object which operates