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??

=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??

- Concentate formula doesn't work on new excel.
- Auto Extend Formulas doesn't work for a particular formula
- Auto Extend Formulas doesn't work for a particular formula
- Formula won't work in different sheet
- VBA code doesn't work when formula in sheet is removed
- DATEDIF formula doesn't work on computer that uses different locale
- GetPivotData doesn't work when Field Subtotals are turned off
- FormulaR1C1 doesn't work when chart is active, why?
- Function doesn't work when used as add-in
- Workbook_open doesn't work with @Risk (3rd party add-in) installed
- Links within excel don't work when workbook is e-mailed - why ?
- Formula requires parameter in quotes, indirect doesn't work
- Formula doesn't work in Excel 2000
- This formula doesn't work anymore!
- Excel 97 formula doesn't work in Excel 2003
- Macro Doesn't Work When Cells Are Hidden
- Preview doesn't work
- Formula doesn't update when rows are inserted
- Excel 97 formula doesn't work in Excel 2003
- Autofit doesn't work when column not enough width
- Formula doesn't calculate
- Formula doesn't work in name
- VBA : any reason that my scroll doesn't work when in ex
- IF formula doesn't work with certain numbers

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?

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?

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 SubWhen 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

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

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)

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?

Function STDTF(Altitude As Double)

ThisWorkbook.Worksheets("Standard_Day").Activate

STDTC = WorksheetFunction.Forecast(Altitude, Range("c1:c371"), Range("a1:a371"))

End Function

Thanks,

Adam

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:When it is installed it doesn't work. When I uninstall it, it does work.Workbook_open() Msgbox "Workbook_open works!" End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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

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.

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?

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:

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.

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"

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

file? It is set for preview but when I click on a file it says preview not

available.

Thanks,

(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

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"

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

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

'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?

"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!