Trying to get around the if limit.

collycrk · Newbie Joined: 02 Feb 2010 Posts: 2

Using oo-calc I am creating a spread sheet and saving it in an excel format.

This works: =IF(A2="Flat Roofing /ISO",INDIRECT("Torch"&"!A4"),"Cost sheet not created.")

This does not: =IF(A2="Flat Roofing /ISO", INDIRECT("VLOOKUP(A2,ListTables!A2:A50,2,0)"&"!A4"), "Cost sheet not created.")

I get an err:502

Thanks for your help.
_________________
Carl
Seeker of truth. Computers do not lie.

keme · Posted: Tue Feb 02, 2010 11:28 am Post subject:

The error is within the VLOOKUP(). You request the value from column 2, but your lookup range is only one column.

collycrk · Newbie Joined: 02 Feb 2010 Posts: 2

This works for one time:

=IF(A2="Flat Roofing Torch ISO",INDIRECT(VLOOKUP(A2, $ListTables.$A$2:$B$50,2,0) & ".A4"),"costsheet not created.")

As soon as A2 changes it will stop working. Even when A2 returns to the value that was there when the if statement was created. But if a space is taken out of the formula and but back in it will work for one time again.

Whats up with that?
_________________
Carl
Seeker of truth. Computers do not lie.