Free Microsoft Excel 2013 Quick Reference

Formula Doesn’t Work When Workbook Opens

I have defined the name “NAV” for the following formula:
=INDIRECT("NAV" & INT((COLUMN() -1) / 14) + 1 - (COLUMN() = 141))
and it works just fine. That’s not my problem.

I have a worksheet with 10 embedded charts lined up horizontally on the sheet. There are some formulas below each chart that provide data related to the chart above it. These formulas are the same for each chart. Under each chart I have a cell with the defined name “NAVx”, where “x” is one of the numbers 1 through 10 corresponding to the chart above it. So I have a series of cells names NAV1, NAV2, NAV3, etc. each containing todays stock price for the chart above it.

To simplify writing the formulas for each chart, whenever I would normally use the name of one of the “NAVx” cells I use the name “NAV” (not “NAV1”, “NAV2”, etc.). The formula with the defined name “NAV” knows how to compute which chart it is referring to by knowing its column position on the sheet. Knowing the column position, it can then append the suffix 1, 2, 3, etc.as appropriate to the literal “NAV” to create the cell name “NAV1”, “NAV2”, “NAV3”, (whichever is appropriate). It then is used in the formula to extract the value of the price cell and substitute it into the formula that referenced “NAV”.

For example, if I wanted to compute the current value of the stock knowing that 8000 shares are owned, I can write the formula:
= 8000 * NAV
After the substitution, the formula becomes:
= 8000 * INDIRECT("NAV" & INT((COLUMN() -1) / 14) + 1 - (COLUMN() = 141))

Here’s my problem. At times when I open the workbook, wherever I’ve used the defined name “NAV” I get a #NAME? error. I can make the error go away by doing the following sequence:
1. Select the cell containing the defined name “NAV”, which make the cell’s formula show in the formula bar.
2. Click my cursor to the left of the formula in the formula bar. This causes a green arrow to appear to the left of the formula bar.
3. Click on the green arrow.
When I click on the green arrow, the formula executes properly, the #NAME? error goes away, and the formula value appears on the worksheet as it’s supposed to do. I have no idea what clicking on the green arrow does, but it clears the problem until the next time I open the workbook.

I never know when I open this workbook whether or not I’m going to get the #NAME? error…sometimes it works perfectly and sometimes it doesn’t. I have had a similar problem in another workbook with a different defined name, so I don’t think it’s the name. I KNOW the formula works, so that’s not likely to be the problem. I have used this technique with about 6 to 10 named formulas that never give me a problem, so why does this particular one fail??


Post your answer or comment

comments powered by Disqus
Hello, this formula worked properly on google spreedshot, and also on excel 2003, but now when i have new excel this formula doesn't work, can anyone helps?



I'm using Excel 2003 and in a particular worksheet, the Auto Extend Formulas
doesn't work on a particular formula. I've turned on the Auto Extend option
and it does work on another formula in the same worksheet. The problematic
formula is:

=WEEKNUM(DATEVALUE(TEXT(A165,"dd/mm")),2)-37+52

Can someone kindly help explain why there's a problem here?

I'm using Excel 2003 and in a particular worksheet, the Auto Extend Formulas
doesn't work on a particular formula. I've turned on the Auto Extend option
and it does work on another formula in the same worksheet. The problematic
formula is:

=WEEKNUM(DATEVALUE(TEXT(A165,"dd/mm")),2)-37+52

Can someone kindly help explain why there's a problem here?

Okay the bottom formula doesn't work when I paste it into sheet 2... i edit the ranges to pull from sheet 1 and keep everything the same... don't know what I'm doing wrong.

I had to remove a lot of pictures in the xls file in order to attach it, (since the original is about 5.3mb) but it worked out ..

When you move your arrow over the titles in column A a picture shows up thanks to a code. When you change the status in column G, the color changes together with the title in column A, as it should.

Private Sub
Worksheet_Calculate()
Dim icolor As Integer
Dim c As Range

    For Each c In Range("G2:G250")
       
        Select Case c.Value
            Case "Watching"
                icolor = 3
            Case "Completed"
                icolor = 5
            Case "Planning to Watch"
                icolor = 4
            Case "On-Hold"
                icolor = 9
            Case "Dropped"
                icolor = 16
            Case Else
                'Whatever
        End Select

        c.Font.ColorIndex = icolor
        c.Offset(, -6).Font.ColorIndex = icolor
      Next c
End Sub
When you enter a new status at G190 till G250 it still works, as it should. But when you remove the formulas at D204:219 it doesn't work anymore. What does those formulas have to do with the code seen above? I need those formulas, so it doesn't really matter. But I was wondering if it could still work without those.

Marco

I have an excel file that I regularly have to share with a different organization. Windows in my organization is set up to be "French (Switzerland)" under "Regional and Language settings", Windows in the other organization is set up as "English (US)".

One of the things we are sharing through Excel are dates of birth and age of our cases. We are using Excel 2002, they are using Excel 2007.

A1: 1.1.2001
B1: =DATEDIF(A1;TODAY();"y")

This work perfectly when I open my file on my computer, but when I send the file to my colleagues in the other organization and they open the files on their computer, B1 returns an error #VALUE. I.e. the file that I sent has a function that works or doesn't work depending on which computer it is being opened.

When I go to "Format Cells" I can see that the format for A1 is "date", "*14.03.2001" and the locale "French (Switzerland)". I thought the asterix in front of the date would actually prevent any problems from occurring, but obviously not.

When I manually changed the date on my colleagues computer to the US format, the formula worked.

How can we work around this? Simply changing the locale of A1 to "English (US)" is not really a workaround since we have to exchange data with more than one organization from different countries, so there is no knowing what set-up they have on their machines.

Thank you very,
any help is very much appreciated,

Timo

I have a Pivot table with 5 different types of Cost and want to get Subtotals for each type to the side of my Pivot table . However, I don't group or subtotal the types so that the Cost types are all together in the Pivot table and the cost types can appear throughout & on different rows.

Formula is =GETPIVOTDATA("Cost",$A$5,"Type","New Parts","Per",K$6)
where the Field "Type" is what I want & has its subtotals turned off. (& I want to keep it that way so the spreadsheet doesn't become obtrusive)

However, the GETPIVOTDATA formula doesn't seem to work when subtotals are turned off for a field and not grouped. Is there a workaround?

Version:Excel 2003
http://www.4shared.com/document/1SlP...thru_07-2.html
See YELLOW CELLS.
(and yes, when I finally get the formula correct, I'll change it & include an ISERROR function to eliminate errors)

Hi, I have a strange thing which happens to my code.
This is the structure.

With ThisWorkbook

...

...

With Worksheets("Sheet1")

..

Worksheets("Sheet2").Range("F" & myColCheck & ":" & "N" & myColCheck).FormulaR1C1 = Worksheets("Sheet2").Range("F11:N11").FormulaR1C1

End With
...
End With
So in Sheet2 I create dynamically some values with the formulas that are in row 11 of Sheet2.

With those formulas i create a graph, passing to the graph the range(not in vba but just with the right click,select range...) of the values in Sheet2.

It happens that if i'm on sheet2 i have no problems at all.

Instead if i'm watching the graph when it's time to copy the new formula into a new row in sheet2 it doesn't work. and instead of the values that i should get with the copy of the formula, i have a '-'

Why?

I tried getting the HTML maker to post my worksheet but work filters blocked it so i appologize. I have a worksheet with 2 columns on it. I need to create a function that will reference these two column with the Forecast command. The first column is a listing of altitudes from -1000 to 20000 and the second column is a temperature corresponding to these altitudes. The current function works fine to use in the xls file it was created in, but when i save it as an add-in and use it on another workbook i get #VALUE. Any ideas why this doesn't work?

Function STDTF(Altitude As Double)
ThisWorkbook.Worksheets("Standard_Day").Activate
STDTC = WorksheetFunction.Forecast(Altitude, Range("c1:c371"), Range("a1:a371"))
End Function

Thanks,

Adam

Hi there,

I have a trial version of @Risk (www.palisade.com) installed. With the add-in installed and loaded, the Workbook_open function doesn't work. I have tested it with

	VB:
	
 Workbook_open() 
     
    Msgbox "Workbook_open works!" 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
When it is installed it doesn't work. When I uninstall it, it does work.
I have placed it in the ThisWorkbook module and I haven't copied and pasted it from anywhere. The EnableEvents property is set to true.
When the Add-in is loaded and I go into the module and press F5 it works.

One thing to note is that the Add-in has a pop up every time you open a workbook which asks you whether you wish to purchase the add-in. It requires you to push a button. I do intend to buy it but I would like to make my code run with the trial copy as the clients will wish to try my workbook and the software out before purchasing.

Any help would be greatly appreciated.

many thanks,

Rob

I want to e-mail an excel workbook and have links within the document work
when the recipient opens the file. The links are mainly within the workbook
itself as navigational aids. The links don't work when my recipients open
the workbook.

I've been given a formula to use (embedded in an add-in that is password-protected). One of the parameters REQUIRES quotes.

The formula goes like this: =MYFORMULA(1,2,3,"ABC")

If I put ABC in a cell (say C5) and use the formula =MYFORMULA(1,2,3,INDIRECT(C5)), the formula doesn't work.

If I put "ABC" in cell C5, the indirect function still work make the function work.

Even if I have ABC in cell C5 and use =MYFORMULA(1,2,3,""""&C5&"""") or =MYFORMULA(1,2,3,""""&INDIRECT(C5)&""""), these won't work.

I wish I could just change the UDF behind this, but that isn't possible.

Any suggestions?

Hi,

I have the following formula that works in Excel 2003 but doesn't work in Excel 2000. Is there something else I could use. I think it is the LEN part.

Code:


Hi guys,

People on this board helped me before Christmas come up with the following formula, and it worked like a dream. I've now gone back to my spreadsheet, tried it with some new data (in same layout) and it doesn't work!! Just gives zeros. Any ideas?

=SUMPRODUCT(--(Idle!$B$1:$B$1000=G$7),--(Idle!$A$1:$A$1000=$E11),Idle!$C$1:$C$1000)

Cheers,

Mel.

I recently switched from Excel 97 to Excel 2003.

When I open one of my spreadsheets created with Excel 97, all the the
cells in columns B display #NAME?. There is also a green triangle in
the upper left corner of each cell.

The formula in each of the cells of column B is a variation on
=QuarterNum(D642). This appears in cell B642. Column D has dates in
it, and column B has quarter numbers. The formula in cell B642 is
supposed to compute the quarter number based on the date in cell D642.

This works when I open the file with Excel 97.

How may I fix the file so it will work in Excel 2003?

Will in Seattle
a.k.a. "Clueless"

Can anyone explain this to me? Fairly new to this...when I hide columns C-H, my macro doesn't work. Is there a workaround for this??? I sure would appreciate anyone's help with this...thanks

My macro is:
Sub CARApprove()
    
    Dim strToFind As String
    Dim rngFound As Range
    Dim strFirst As String
    
    If MsgBox(" Are You Sure You Are Ready To Approve The CAR?", vbYesNo) <> vbYes Then Exit Sub
    

    strToFind = Range("C7").Value2
    Set rngFound = Range("C9:C34").Find(strToFind, , xlValues)
    
    If Not rngFound Is Nothing Then
        strFirst = rngFound.Address
        Do While Not rngFound Is Nothing
            Cells(rngFound.Row, "H").Value = 50
            Cells(rngFound.Row, "I").Value = Cells(rngFound.Row, "B").Value
            Set rngFound = Range("C9:C34").Find(strToFind, rngFound, xlValues)
            If rngFound.Address = strFirst Then Exit Do
        Loop
    
    Recip = [$D$3].Value
    Set OutlookApp = CreateObject("Outlook.Application")
    Set Mess = OutlookApp.CreateItem(olMailItem)
    With Mess
        .Subject = "Your CAR " & Range("A5") & " Has Been Approved By " &
Application.UserName
        .Body = "Your CAR " & Range("A5") & " Has Been Approved By " &
Application.UserName
        .Recipients.Add Recip
        .Send
    End With
    
    End If
End Sub


Does anyone know what to do when preview doesn't work when you are opening a
file? It is set for preview but when I click on a file it says preview not
available.
Thanks,

I have a formula that is simply adding cells E8:E184. When I insert rows
(begining with E185) and populate those rows with data, the formula doesn't
pick these up. In all of my other spreadsheets, the formula automatically
updates with the inserted rows, but this one doesn't want to do this. Anyone
have any ideas?

Thanks,
arreferee

I recently switched from Excel 97 to Excel 2003.

When I open one of my spreadsheets created with Excel 97, all the the
cells in columns B display #NAME?. There is also a green triangle in
the upper left corner of each cell.

The formula in each of the cells of column B is a variation on
=QuarterNum(D642). This appears in cell B642. Column D has dates in
it, and column B has quarter numbers. The formula in cell B642 is
supposed to compute the quarter number based on the date in cell D642.

This works when I open the file with Excel 97.

How may I fix the file so it will work in Excel 2003?

Will in Seattle
a.k.a. "Clueless"

Hi !
I have modify the original "AdjustRowHeight" for merged cells to apply to
all cells in a specific table range. Sometimes, when column width are not
enought large, the EntireRow.AutoFit command doesn't work. Does anyone have a
solution for this kind of problem ?
Is it possible to have a macro that replace the Autofit line ?
What I could do is to enlarge each table column (+1), make autofit and then,
resize column width to original value.

Excel seems to required a little space after the end of the last character.

Thank you.

Here is my program.
Sub AdjustRowHeight()
Dim myRange As Range
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range
Dim ActiveCellWidth As Single, PossNewRowHeight As Single
Dim j As Long
Dim myCell As Cells

'1- Verify if there is a table in the ActiveSheet
myTable = Array("table1_1", "table1_2", "table2_1", "table2_2", "table2_3",
"table3_1", "table3_2")
On Error Resume Next
i = -1
Do Until Not myRange Is Nothing Or i = 7
i = i + 1
Set myRange = ActiveSheet.Range(myTable(i)) 'Local Range Only
Loop
If i = 7 Then Exit Sub

'2- Adjust Row Height
myRange.EntireRow.AutoFit = True
For Each cell In myRange
MergedCellRgWidth = 0
If cell.MergeCells And cell = cell.MergeArea.Cells(1) Then 'Treatment
for first merge cell only
With cell.MergeArea
If .Rows.Count = 1 And .WrapText = True Then
Application.ScreenUpdating = False
CurrentRowHeight = .RowHeight
ActiveCellWidth = cell.ColumnWidth
For Each CurrCell In cell.MergeArea
MergedCellRgWidth = CurrCell.ColumnWidth +
MergedCellRgWidth
Next
.MergeCells = False
.Cells(1).ColumnWidth = MergedCellRgWidth
.EntireRow.AutoFit
PossNewRowHeight = .RowHeight
.Cells(1).ColumnWidth = ActiveCellWidth
.MergeCells = True
.RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
CurrentRowHeight, PossNewRowHeight)
End If
End With
End If
Next cell
End Sub
--
Alex St-Pierre

The formula in cells R7 and S7 don't calculate. When I fill in the numbers in cell J7 and K7 manually it works, but when I use a link to cell T3 and T4 it won't work. Hope someone can help me out.

I have checked the format of the sheet. Doesn't work when all the cells have the same format...
Formula's I am using are not the most difficult ones:

ABS(J7-K7+100)
IF(C7=0;"";SUM.IF($AA$3:$AA$28;N7;$AB$3:$AB$28))
IF(J7>K7;SUM.IF(W:W;L7;X:X);SUM.IF(W:W;L7;Y:Y))
ROUND(1/(MACHT(10;(-W7)/400)+1);3)

Solution must be simple, right? I must be overseeing something.
I am using the Dutch Excel 2007, but I tried it with the English one and it wasn't working either.

ForDoesntWork.xlsx
ForDoesntWork.xls
FormulaDoesntWork.gif

I have 2 names:
'Range2Count' RefersTo : "=OFFSET(L!$C$7,2,0,ROWS(L!A:A)-ROW(L!$C$7)-1,1)"
'myCount' RefersTo : "=COUNTA(L!Range2Count)"
that work fine, but when I combine them into 1 name:
it doesn't work.

Why?

The scorll works fine in excel and other programs, but while i'm working in excel vba, it doesn't work. Any ideas why or how to fix it? Thanks guys!

I have 2 columns, both summed. If the sums equal, the 3rd column is to say
"ok". If they don't, it says "check bal". We have been doing this for
months. Today I entered "197.14" in both columns. The sum total is the
same. The IF column says "check bal". I can put in "197" and it works,
"197.12" works, "197.13" & "197.15" work. "197.10" also doesn't work. We
finally added 197.14 to a total in another cell & it worked fine. We
reformatted & made sure everything was numbers & that there was no rounding
in the total numbers. Any ideas? Thanks!


No luck finding an answer? You could always try Google.