Referencing another worksheet and getting #name?

[draacor]

Hello,

I have two excel workbooks. The first excel workbook has a cell with the following formula:

=IFERROR(AVERAGE(B23),"")

The second workbook has the following formula to reference the first workbook.

=IF('Workbook1!$R$2>79.9%,'Workbook1'!$R$2,"")

I only want it to show the data if the data is greater than 79.9%. For some reason when i do this it comes up with the #NAME? error. Any suggestions?

 

[SkipVought]

Hi,

79.9% is a format. See if this works...
[tt]
=IF('Workbook1!$R$2>.799,'Workbook1'!$R$2,"")

[/tt]


Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[draacor]

doh! that didnt work i get the same error

 

[draacor]

if it helps i do have excel 2007, maybe there are extra features that can alleviate this problem.

 

[SkipVought]

How did your generate the REFERENCE in the formula?

Did you TYPE it in? No, No!

You should only type everything EXCEPT the references: for them you POINT your mouse and CLICK.

Your referece needs the SHEET NAME in the proper syntax!

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[draacor]

i did the mouse click, i didnt type in the reference, i opened up both workbooks and just clicked on the cells i wanted to reference. I have a feeling that the iferror is whats causing the #name? but there has to be a way around that.

 

[SkipVought]

"I have two excel workbooks."

Your reference, if you did point & click, would look like this...
[tt]
=[YourWorkbookName.xls]YourSheetName!CellReference
[/tt]
!!!

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[draacor]

doh i took too much out, i have some names i didnt want to post on the forums so i tried editing it to just say workbook and didnt realize i took out the formatting too. This is what it looks like:

=IF('[Workbook1.xls]Trend Analysis'!$R$2 > 79.9%,'[Workbook1.xls]Trend Analysis'!$R$2,"")

 

[SkipVought]

Listen, you have something else going on that you're not disclosing.

Here's my WORKING formula in Excel 2007...
[tt]
=IF([TestAddressImport.xlsx]Sheet1!$F$1>7.5%,[TestAddressImport.xlsx]Sheet1!$F$1,"")
[/tt]
Do ANY of your IF function work?

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[draacor]

Thank you for all of your help.