Formula works in Excel 2007 but not in 2003
Hello,
I have a formula that works in Excel 2007 but not in 2003. Is there any way i can modify it to work in both? Formula is below:
{=IFERROR(INDEX('Spd Nov -13'!$A$1:$I$1000,SMALL(IF('Spd Nov -13'!$A$1:$A$1000='Daily Summary'!$I$1,ROW('Spd Nov -13'!$A$1:$A$1000)),ROW(1:1)),1),0)}
IFError formula was first introduced in Excel 2007. In Excel 2003, you will use IF(Iserror())
Is that formula suppose to work in 2007 as well? I get False back when i use it in 2007...
Try this
Confirm with Ctrl+Shift+Enter
=IFERROR(INDEX('Spd Nov -13'!$A$1:$I$1000,SMALL(IF('Spd Nov -13'!$A$1:$A$1000='Daily Summary'!$I$1,ROW('Spd Nov -13'!$A$1:$A$1000),""),ROW(1:1)),1),0)
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Formula:
Please Login or Register to view this content.
gives the same results as using iferror and can be used in 2003
the original works just fine in 2007 i couldnt see a problem with it
Last edited by martindwilson; 11-15-2013 at 06:05 PM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
I see that you return a 0 if there's an error.
What type of data does the formula normally return?
Is it text? Numbers? Could be both? Something else?
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
@Martindwilson,
That worked great! Thanks!
@Tony,
It returns both text and numbers.
If the results are mixed data types I would do this...
Put the COUNTIF in a separate cell and refer to that cell instead of repeating the COUNTIF in each cell the formula is copied to.
A1: =COUNTIF('Spd Nov -13'!$A$1:$A$1000,'Daily Summary'!$I$1)
B1 and copied down:
=IF(ROWS(B$1:B1)>A$1,0,INDEX('Spd Nov -13'!A:A,SMALL(IF('Spd Nov -13'!A$1:A$1000='Daily Summary'!$I$1,ROW('Spd Nov -13'!A$1:A$1000)),ROWS(B$1:B1))))
Still array entered.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks