Can anyone see a problem with this?

[bikky]

I've currently got 2 sheets and i'm comparing 2 cells in one worksheet
to 2 cells in another, if they match then the output is the 3rd cell,
or another if statement if false. The if statements work if i
terminate the fomula on number 9, if i add 10 the formula fails, if i
do all the clicking on cells to let excel add the rest of the formula
for 10 it also fails.

I've put each nested if/and on a separate line for ease of reading, the
double space between 9 and 10 signifies the point where it stops
working.

Any help/info is much appreciated as its driving me batty.

Thanks


=IF(AND(Blank!B18=Miles!$B$3,Blank!C18=Miles!$C$3),Miles!$D$3,

IF(AND(Blank!B18=Miles!$B$4,Blank!C18=Miles!$C$4),Miles!$D$4,

IF(AND(Blank!B18=Miles!$B$5,Blank!C18=Miles!$C$5),Miles!$D$5,

IF(AND(Blank!B18=Miles!$B$6,Blank!C18=Miles!$C$6),Miles!$D$6,

IF(AND(Blank!B18=Miles!$B$7,Blank!C18=Miles!$C$7),Miles!$D$7,

IF(AND(Blank!B18=Miles!$B$8,Blank!C18=Miles!$C$8),Miles!$D$8,

IF(AND(Blank!B18=Miles!$B$9,Blank!C18=Miles!$C$9),Miles!$D$9,



IF(AND(Blank!B18=Miles!$B$10,Blank!C18=Miles!$C$10),Miles!$D$10,

IF(AND(Blank!B18=Miles!$B$11,Blank!C18=Miles!$C$11),Miles!$D$11,

IF(AND(Blank!B18=Miles!$B$12,Blank!C18=Miles!$C$12),Miles!$D$12,

IF(AND(Blank!B18=Miles!$B$13,Blank!C18=Miles!$C$13),Miles!$D$13,

IF(AND(Blank!B18=Miles!$B$14,Blank!C18=Miles!$C$14),Miles!$D$14,

IF(AND(Blank!B18=Miles!$B$15,Blank!C18=Miles!$C$15),Miles!$D$15,

IF(AND(Blank!B18=Miles!$B$16,Blank!C18=Miles!$C$16),Miles!$D$16,0))))))))))))))

 

[Marcelo]

hi,

you have broken the if limit, excel just support 7 if's in a formula

hth
regards from Brazil
Marcelo

"bikky" escreveu:

 

[Casey]

Bikky,
The limit for nesting IF formulas is 7. After that it quits working.
Type "Excel specifications and limits" into Help and you will see
additional limitations along with this one.

Nested levels of functions 7

Describe what you are trying to accomplish and someone might have an
alternate method, but the route your going won't work.

HTH

 

[bikky]

Thanks, i think it might have to be vb.

Its basically a milage form to submit for work, i have a sheet with a
named column for a validation drop down list for to and from, then also
on that sheet with the validation list is the miles accrued between
sites. eg.

sitea siteb 10
sitea sitec 12
sitea sited 27
siteb sitec 19
siteb sited 12
sitec sited 6

so
if cell 1 = miles!cell1 and cell2 = miles!cell2 then cell3,
else if cell 1=miles!cell4 and cell2 = miles!cell5 then cell6,
else......

Problem is with so many sites it would be a large if, and i didn't know
what the limit was. I need the local cell reference to be dynamic so it
can be copied from a blank template in the workbook to a new worksheet
for each month, however the lookup is to a fixed cell called miles.

Any help on this would be great as we're all sick of handwriting these
every month.

Thanks

 

[Dav]

It is because you areonly allowed 7 levels of nesting in excel, thats
where you formula fails

However you could use another formula

=IF(ISERROR(MATCH(blank!b18,Miles!B3:b16,0)=MATCH(blank!c18,Miles!c3:c16,0)),0,OFFSET(miles!d2,MATCH(blank!b18,Miles!b3:b16,0),0))

That should work if I have typed it correctly

Regards

Dav

 

[Tom Hutchins]

Here's a really ugly, inefficient formula which does what you asked:

=IF(ISERROR(VLOOKUP(Blank!$B$18,Miles!$B$3:$D$16,3,FALSE)),0,IF(ISERROR(VLOOKUP(Blank!$C$18,Miles!$C$3:$D$16,2,FALSE)),0,IF(VLOOKUP(Blank!$B$18,Miles!$B$3:$D$16,3,FALSE)=VLOOKUP(Blank!$C$18,Miles!$C$3:$D$16,2,FALSE),VLOOKUP(Blank!$C$18,Miles!$C$3:$D$16,2,FALSE),0)))

And here's an easier method:

1. On the MILES sheet, enter this formula in the first free column (I'll
assume it's column E):

=IF(AND(Miles!B3=Blank!B18,Miles!C3=Blank!C18),Miles!D3,0)

Copy this formula down for as many rows as needed (through row 16 in your
original post).

2. In the cell where your IF formula was , enter:

=SUM(Miles!E:E)

If the BLANK sheet formulas are in a different column than E, change E:E to
that column.

Hope this helps,

Hutch

 

[Tom Hutchins]

Oops! The BLANK sheet reference needs to be absolute:

=IF(AND(Miles!B3=Blank!$B$18,Miles!C3=Blank!$C$18),Miles!D3,0)

I like Dav's formula. My first thought was to use Match, but then I thought
of Vlookup. Should have gone with the first impulse.

Hutch

 

[Tom Hutchins]

Dav,

I tried your formula and it didn't work. If column B matches, it returns the
column D value regardless if column C matches or not. FALSE is not an error.
I rewrote it as follows, and it works:

=IF(MATCH(Blank!B18,Miles!B3:B16,0)=MATCH(Blank!C18,Miles!C3:C16,0),OFFSET(Miles!D2,MATCH(Blank!B18,Miles!B3:B16,0),0),0)

Hutch

 

[bikky]

WOahhh

Thanks for all the help you've supplied, i'll run with the matc
script, as i don't want fixed reference to Blank! as that is the bas
sheet to be copied to each months new worksheet.

The Match isn't quite working yet, as i either get the correct milag
for the first entry in the match lookup, or 0 or NA.

If your wanting a copy of the doc i can post to a website for you's t
have a look, NOTE the miles sheet with the list of sites isn't complet
yet, its a work in progress, so i'll have to easily adjust the formula
as more "regular" trips are required. The start and end of journe
also needs freetext option for "sporadic" journey's

infact check www.lan-uk.derwentside.net/milage.xls (it is virus free
my own hosted server)

rather than posting back the spreadsheet, pasting the formula in her
would be more helpful for me to learn, and others to reference from.


THANKS V MUCH for all the help so far.
spence

 

[Tom Hutchins]

I looked at your file. I'm embarassed to say that the dummy data I created
Friday didn't have any duplicate values, and that the solutions offered won't
work if there are duplicates.

However, here's an easy solution that WILL work:

1. On the MILES sheet, insert a new column between C & D (it becomes your
new column D, and the miles to be returned are now column E). In D3, enter:

=B3&C3

Copy this formula down for as many rows as have data on the MILES sheet. We
are creating a single concatenated field against which we can easily do a
Vlookup.

2. On the BLANK sheet, enter the following formula in D18:


=IF(ISERROR(VLOOKUP(B18&C18,Miles!D:E,2,FALSE)),"",VLOOKUP(B18&C18,Miles!D:E,2,FALSE))

Copy this formula down through all the rows on your form. This formula
concatenates the From and To sites, does a Vlookup with it on the MILES
sheet, and returns the mileage. If the Vlookup fails (can't find a match),
nothing is displayed.

You also have a few typos:
milage should be mileage
monthely should be monthly
mileometer should be odometer

Hope this helps,

Hutch

 

[Dav]

The easy way is to change you data to like a mileage table in an atlas

eg
A1 A B C D
A 0 12 7 9
B 12 0 15 6
C 7 15 0 16
D 9 6 16 0

with the mileage in the cells

if A B C D are the places
Then you just use an offset formula if the table has cell a1 where i
have coloured ir red offset
($a$1,match(G1,a2:a5,0),match(h1,b1:e1,0)) would return the value

where h1 is the starting place and g1 the finish, or the other way
round it does not really matter!

An index function does a similar thing to the offset and match so that
is another option. the table approach means you have to type less as
well
N/A is returned if there is no match

Regards

Dav

 

[bikky]

Thanks V much Tom,

That worked perfectly. Now just to populate the Mileage table and ge
in circulation.

Then work on dav's idea of the matrix table.

I'll correct the Monthely, the others are as per original sheet (whic
we're not allowed to doctor in any way other than filling out the clai
for mileage). I saw mileometer on my very first form and thought it wa
wrong.

any THANKS all, and i'd Kiss you if i could reach that far...

Keep an eye out for another one i'm working on ;

 

[bikky]

DAV,

Pick your brains a bit more on matrix idea please.....

using formula
=OFFSET(Matrix!$A$1,MATCH(B18,Matrix!1:1,0),MATCH(C18,Matrix!A:A,0))


is giving me 1 row and 1 col. out so eg

a1 a b c d
a 0 12 7 9
b 12 0 15 6
c 7 15 0 16
d 9 6 16 0


and the lookup is "a b" which should give the result 12, but i'
getting 15, one col along, plus one row down, and this is for ever
one.

Any ideas where its gone wrong?


Thanks agai

 

[bikky]

DAV,

Pick your brains a bit more on matrix idea please.....

using formula
=OFFSET(Matrix!$A$1,MATCH(B18,Matrix!1:1,0),MATCH(C18,Matrix!A:A,0))


is giving me 1 row and 1 col. out so eg

a1 a b c d
a 0 12 7 9
b 12 0 15 6
c 7 15 0 16
d 9 6 16 0


and the lookup is "a b" which should give the result 12, but i'
getting 15, one col along, plus one row down, and this is for ever
one.

Any ideas where its gone wrong?


Thanks again

for the time being i've used
=OFFSET(Matrix!$A$1,MATCH(B18,Matrix!a1:a50,0),MATCH(C18,Matrix!b1:Az1,0))

this gives me 50 sites in each direction, but still would like to kno
how to do it with full row/column reference, rather than fixe
column/row references if anyone know