Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF(a1="x",(vlookup 18K rows,2,false),(vlookup 18K,3,false)) RAM?
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF(a1="x",(vlookup 18K rows,2,false),(vlookup 18K,3,false)) RAM?
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF(a1="x",(vlookup 18K rows,2,false),(vlookup 18K,3,false)) RA
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" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF(a1="x",(vlookup 18K rows,2,false),(vlookup 18K,3,false)) RA
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF(a1="x",(vlookup 18K rows,2,false),(vlookup 18K,3,false)) RA
Hi Brian
Create 3 named ranges InsertNameDefine 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 "bchilt" wrote in message ... 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" wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF(a1="x",(vlookup 18K rows,2,false),(vlookup 18K,3,false)) RA
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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF(a1="x",(vlookup 18K rows,2,false),(vlookup 18K,3,false)) RA
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 "Roger Govier" wrote in message ... Hi Brian Create 3 named ranges InsertNameDefine 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 "bchilt" wrote in message ... 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|