IF(a1="x",(vlookup 18K rows,2,false),(vlookup 18K,3,false)) RAM?

[Guest]

I'm trying to use an IF statement to help me with determining which vlookup
formula should be used. The problem is I'm getting an error message that
says "Excel cannot complete this task with available resources. Choose less
data or close other applications". I have closed everything possible. I
still get the error. Originally, this document was using the same 18k source
without any problems. It's only when I introduced the IF statement that I'm
now getting the error.

Thanks.

 

[Pete]

Why don't you amend the formula to read:

=IF(a1="x",VLOOKUP 18k rows,2,FALSE),"")

and copy down? Then filter that column for (Blanks) and while the
filter is on enter in the topmost visible cell the other half of your
formula:

=VLOOKUP(18k rows,3,FALSE)

Copy and paste this formula down into the blank cells visible under the
filter, then remove the filter.

Alternatively in one column (assume X):

=IF(A1="x",2,3) and copy down. Then in Y:

=VLOOKUP(18k rows, X1,FALSE)

and copy down. If resources are tight, you might like to fix the values
in X (and in other columns) first.

Hope this helps.

Pete

 

[Guest]

Hi Pete,

Thanks for the response. This won't solve my problem. The X value changes
between "Local Currency" and "USD". As a result, I have a source document
with values for both Local and USD.

The report allows users to choose between the two and the Vlookup formulas
pull in the correct number. That's the reason for the IF statement.

Here's the actual formula in all it's glory.

NOTE: $T$5 can change between USD and Local Currency.

IF($T$5="USD",(VLOOKUP($A14,'[Jan Sport Perf USD.xls]4 Sport
Performance'!$J$7:$K$18900,2,FALSE)/1000),(VLOOKUP($A14,'[Jan Sport Perf
USD.xls]4 Sport Performance'!$H$7:$I$18900,2,FALSE))/1000)

Thanks again,

Brian

 

[Pete]

I see that you are referencing a different file - can you copy the
reference sheet into the workbook that contains the lookup formula?
That way the formula will be a lot shorter and less complex. The sheet
could be protected and hidden if you don't want Users to tamper with
it.

If you are stuck with it as a separate file, you might like to define
named ranges within the reference file, i.e."T_1" referring to
$J$7:$K$18900 and "T_2" referring to $H$7:$I$18900 - again, your
formula will be shorter.

I assume that you are copying the formula down a number of rows, so
obviously the formula itself will take up memory, as well as the value
it produces, so anything to reduce the length of the formula will help
your cause.

Might help this time ...

Pete

 

[Roger Govier]

Hi Brian

Create 3 named ranges
Insert>Name>Define
Name TableUSD Refers to '[Jan Sport Perf USD.xls]4 Sport
Performance'!$J$7:$K$18900
Name TableOther Refers to '[Jan Sport Perf USD.xls]4 Sport
Performance'!$H$7:$I$18900
Name TableToUse Refers to =IF($T$5="USD",TableUSD,TableOther)

Then your formula is simply
VLOOKUP($A14,tableToUse,2,0)/1000

--
Regards

Roger Govier

Hi Pete,

Thanks for the response. This won't solve my problem. The X value
changes
between "Local Currency" and "USD". As a result, I have a source
document
with values for both Local and USD.

The report allows users to choose between the two and the Vlookup
formulas
pull in the correct number. That's the reason for the IF statement.

Here's the actual formula in all it's glory.

NOTE: $T$5 can change between USD and Local Currency.

IF($T$5="USD",(VLOOKUP($A14,'[Jan Sport Perf USD.xls]4 Sport
Performance'!$J$7:$K$18900,2,FALSE)/1000),(VLOOKUP($A14,'[Jan Sport
Perf
USD.xls]4 Sport Performance'!$H$7:$I$18900,2,FALSE))/1000)

Thanks again,

Brian

Why don't you amend the formula to read:

=IF(a1="x",VLOOKUP 18k rows,2,FALSE),"")

and copy down? Then filter that column for (Blanks) and while the
filter is on enter in the topmost visible cell the other half of your
formula:

=VLOOKUP(18k rows,3,FALSE)

Copy and paste this formula down into the blank cells visible under
the
filter, then remove the filter.

Alternatively in one column (assume X):

=IF(A1="x",2,3) and copy down. Then in Y:

=VLOOKUP(18k rows, X1,FALSE)

and copy down. If resources are tight, you might like to fix the
values
in X (and in other columns) first.

Hope this helps.

Pete

 

[Harlan Grove]

bchilt wrote...
....

Here's the actual formula in all it's glory.

NOTE: $T$5 can change between USD and Local Currency.

IF($T$5="USD",(VLOOKUP($A14,'[Jan Sport Perf USD.xls]4 Sport
Performance'!$J$7:$K$18900,2,FALSE)/1000),(VLOOKUP($A14,'[Jan Sport Perf
USD.xls]4 Sport Performance'!$H$7:$I$18900,2,FALSE))/1000)

....

You should be able to rewrite this as

=VLOOKUP($A14,IF($T$5="USD",
'[Jan Sport Perf USD.xls]4 Sport Performance'!$J$7:$K$18900,
'[Jan Sport Perf USD.xls]4 Sport Performance'!$H$7:$I$18900),
2,FALSE)/1000

And as long as the file Jan Sport Perf USD.xls is open in memory in the
same Excel instance, you shouldn't have problems. If you do, then it's
a mystery to me. However, if you close this file, all bets are off.
Excel dereferences multiple cell ranges in closed workbooks as arrays,
and it seems to cache such arrays in memory. It's possible that Excel
reserves only a small amount of RAM to store such arrays.

So is this formula a problem when the other file is open or just when
it's closed?

 

[Roger Govier]

Hi Brian

I sometimes take the methodology explained in my previous posting one
stage further and use named ranges to behave like a "mini UDF".

For example, your lookup is of cell A14 in the relevant table.
If the formula to return that value were being entered in say cell D14
then you could set up another defined Name called DOLLARVALUE as
=VLOOKUP(OFFSET($D14,0,-3),TableToUse,2,0)/1000&" "&$T$5
Just change the offset, relative to where you want the value returned,
compared with A14

In D5 you would just then enter
=DOLLARVALUE
and it would return something like 1.35 USD or 6.73 Local Currency

Dependant upon the whole layout of your sheet where you are returning
the values, you may be able to use the more preferable non-volatile
formula
=VLOOKUP(INDEX(A:A,MATCH($D14,A:A,0)),tableToUse,2,0)&" "&Sheet2!$T$5
You may need to limit the ranges from the whole column A:A to a range
appropriate to your situation.


--
Regards

Roger Govier

Hi Brian

Create 3 named ranges
Insert>Name>Define
Name TableUSD Refers to '[Jan Sport Perf USD.xls]4 Sport
Performance'!$J$7:$K$18900
Name TableOther Refers to '[Jan Sport Perf USD.xls]4 Sport
Performance'!$H$7:$I$18900
Name TableToUse Refers to =IF($T$5="USD",TableUSD,TableOther)

Then your formula is simply
VLOOKUP($A14,tableToUse,2,0)/1000

--
Regards

Roger Govier

Hi Pete,

Thanks for the response. This won't solve my problem. The X value
changes
between "Local Currency" and "USD". As a result, I have a source
document
with values for both Local and USD.

The report allows users to choose between the two and the Vlookup
formulas
pull in the correct number. That's the reason for the IF statement.

Here's the actual formula in all it's glory.

NOTE: $T$5 can change between USD and Local Currency.

IF($T$5="USD",(VLOOKUP($A14,'[Jan Sport Perf USD.xls]4 Sport
Performance'!$J$7:$K$18900,2,FALSE)/1000),(VLOOKUP($A14,'[Jan Sport
Perf
USD.xls]4 Sport Performance'!$H$7:$I$18900,2,FALSE))/1000)

Thanks again,

Brian

Why don't you amend the formula to read:

=IF(a1="x",VLOOKUP 18k rows,2,FALSE),"")

and copy down? Then filter that column for (Blanks) and while the
filter is on enter in the topmost visible cell the other half of
your
formula:

=VLOOKUP(18k rows,3,FALSE)

Copy and paste this formula down into the blank cells visible under
the
filter, then remove the filter.

Alternatively in one column (assume X):

=IF(A1="x",2,3) and copy down. Then in Y:

=VLOOKUP(18k rows, X1,FALSE)

and copy down. If resources are tight, you might like to fix the
values
in X (and in other columns) first.

Hope this helps.

Pete