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:

>
> 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))))))))))))))
>
>
> --
> bikky
> ------------------------------------------------------------------------
> bikky's Profile: http://www.excelforum.com/member.php...o&userid=36149
> View this thread: http://www.excelforum.com/showthread...hreadid=559359
>
>

[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

"bikky" wrote:

>
> 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
>
>
> --
> bikky
> ------------------------------------------------------------------------
> bikky's Profile: http://www.excelforum.com/member.php...o&userid=36149
> View this thread: http://www.excelforum.com/showthread...hreadid=559359
>
>

[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" wrote:

>
> 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
>
> "bikky" wrote:
>
> >
> > 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
> >
> >
> > --
> > bikky
> > ------------------------------------------------------------------------
> > bikky's Profile: http://www.excelforum.com/member.php...o&userid=36149
> > View this thread: http://www.excelforum.com/showthread...hreadid=559359
> >
> >

[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

"Dav" wrote:

>
> 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
>
>
> --
> Dav
> ------------------------------------------------------------------------
> Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
> View this thread: http://www.excelforum.com/showthread...hreadid=559359
>
>

[bikky]

WOahhh

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

The Match isn't quite working yet, as i either get the correct milage 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 to have a look, NOTE the miles sheet with the list of sites isn't complete yet, its a work in progress, so i'll have to easily adjust the formulae as more "regular" trips are required. The start and end of journey 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 here would be more helpful for me to learn, and others to reference from.


THANKS V MUCH for all the help so far.
spencer

[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

"bikky" wrote:

>
> WOahhh
>
> Thanks for all the help you've supplied, i'll run with the match
> script, as i don't want fixed reference to Blank! as that is the base
> sheet to be copied to each months new worksheet.
>
> The Match isn't quite working yet, as i either get the correct milage
> 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 to
> have a look, NOTE the miles sheet with the list of sites isn't complete
> yet, its a work in progress, so i'll have to easily adjust the formulae
> as more "regular" trips are required. The start and end of journey
> 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 here
> would be more helpful for me to learn, and others to reference from.
>
>
> THANKS V MUCH for all the help so far.
> spencer
>
>
> --
> bikky
> ------------------------------------------------------------------------
> bikky's Profile: http://www.excelforum.com/member.php...o&userid=36149
> View this thread: http://www.excelforum.com/showthread...hreadid=559359
>
>

[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 get in circulation.

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

I'll correct the Monthely, the others are as per original sheet (which we're not allowed to doctor in any way other than filling out the claim for mileage). I saw mileometer on my very first form and thought it was 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'm getting 15, one col along, plus one row down, and this is for every one.

Any ideas where its gone wrong?


Thanks again

[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'm getting 15, one col along, plus one row down, and this is for every 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 know how to do it with full row/column reference, rather than fixed column/row references if anyone knows