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

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
>
>

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


--
Casey


------------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=559359

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

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

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
>
>

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
> >
> >

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
>
>

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

--
bikk
-----------------------------------------------------------------------
bikky's Profile: http://www.excelforum.com/member.php...fo&userid=3614
View this thread: http://www.excelforum.com/showthread.php?threadid=55935

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
>
>