Formula with named ranges not working

[papermonkey]

this is the formula.
=IFERROR(SLOPE(IF(RANGE1=DATA1;IF(RANGE2=DATA2;IF(RANGE3=DATA3)));IF(RANGE1 =DATA1;IF(RANGE2=DATA2;IF(RANGE4=DATA4))));"ERROR")

1. will this formula valid?
2. when i copy this formula to another workbook it is not creating a link to the master workbook. how to create a link ?
3. the formula works but when i hit f2 inside the cell the data converts to ERROR. dont know why.

RANGE1,2,3,4 = named ranges of separate column data.
DATA1,2,3,4 = constant data criteria like year, month, employee id, salary

please help. thanks.

[Aussiebear]

Are you defining the ranges correctly?

[papermonkey]

here is the sample data, please check.
it is giving inconsistent formula after i trimmed the data. this could be because of data type mismatch?
https://drive.google.com/open?id=1cu...nFiWzyRBEfhqdm

[papermonkey]

i also ran this macro to get cell reference name replacing named range. it gives a 1004 error.

Sub ReplaceRangeNames()
Dim LR As Integer, TR As Integer
Dim Sh As Worksheet
Application.DisplayAlerts = False
Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "New"
    Sheets("New").Range("A1").ListNames
LR = Sheets("New").Range("A1").End(xlDown).Row
Sheets("New").Range("B1:B" & LR).Replace What:="=", Replacement:= _
        "", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
For Each Sh In Worksheets
If Sh.Name <> "New" Then
Sh.Activate
Sh.Cells.SpecialCells(xlCellTypeFormulas, 23).Select
    For TR = 1 To LR
Selection.Replace What:=Sheets("NEW").Range("A" & TR).Value, Replacement:= _
        Sheets("NEW").Range("B" & TR).Value, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Next TR
End If
Next Sh
Sheets("New").Delete
Application.DisplayAlerts = True
End Sub

[MartinGM]

There are so many syntax erros in your formula that I am having a hard time working out what you are trying to do.

For instance:

1. The sections of an IF statement have to be separated by commas, not semi-colons
2. An IF statement needs three sections - the logical test and what happens if the test is True or is False. The part of your formula "IF(RANGE3=DATA3)", by way of an example, only has one section
3. The SLOPE function has to have two sections (the Y values and the X values), separated by a comma.

So I don't understand how the formula can be working ?

Sorry I can't help more.

[Aussiebear]

here is the sample data, please check.

Actually it would be more helpful if you click on Go Advanced, Manage Attachments and follow the prompts to upload a sample file for us to see what you are trying to do.

[papermonkey]

Thanks everyone. This is solved.
MS-office has removed the explicit reference (workbook.sheet!) etc. etc in latest version.
instead, if you click the "data" option and click "edit links" the parent workbook is stored as the reference link.
also whena formula uses an array instead of a single value always use ctrl+shift+enter key to get the results, F2/F9 will not work.
Cant believe Steve jobs removed XL from apple but of course that was the whole idea of Bill.
(i believe XL was first introduced by Steve jobs in a stage with Bill gates). video is available in youtube.
thanks everyone.
this is one active group.

[Aflatoon]

Thanks everyone. This is solved.
MS-office has removed the explicit reference (workbook.sheet!) etc. etc in latest version.
instead, if you click the "data" option and click "edit links" the parent workbook is stored as the reference link.
also whena formula uses an array instead of a single value always use ctrl+shift+enter key to get the results, F2/F9 will not work.
Cant believe Steve jobs removed XL from apple but of course that was the whole idea of Bill.
(i believe XL was first introduced by Steve jobs in a stage with Bill gates). video is available in youtube.
thanks everyone.
this is one active group.

Respectfully, none of that is accurate. References have not changed; you always had to use Ctrl+Shift+Enter to array enter a formula; and Excel is still available on the Mac.

[mikerickson]

1. The sections of an IF statement have to be separated by commas, not semi-colons
[/COLOR]

FYI, in European versions of Excel, the semi-colon replaces the comma for argument separators.
This is to accommodate those countries that use the comma as a decimal separator, i.e. 3/2 = 1,50