Copying an array formula...

[Guest]

Hello,

EXCEL 2003 --

I have the following formula:

={index('SHEET1'!$A$1:$AK$12000,MATCH(C2&"",'SHEET1'!$D$1:$D$12000&'SHEET1'!$E$1:$E$12000,0),30)}

This s located in cell b2 and in cell c2:j2 I have the same formula with a
different number at the end.

Issue:

I need to copy these cells B2:J2 to the following range
B4:J12500 -- However, when I do this the computer stop responding and excel
seems to crash. I've done this for the past 3 days leaving the computer
running for a maximum of 4 hours.

Help. Is there anyway to do this quicker.

Thanks
Dennis G.

 

[Guest]

={index('SHEET1'!$A$1:$AK$12000,MATCH(C2&"",'SHEET1'!$D$1:$D$12000&'SHEET1'!$E$1:$E$12000,0),30)}

This s located in cell b2 and in cell c2:j2 I have the same formula with a
different number at the end.

Going by the above lines, for the formula in cell C2,
won't you be hit with a circular ref error ?

... a little tough <g> to interp the underlying logic
behind the formulas and the intent ..

 

[Guest]

Max,

They're on separate sheets...

Dennis G.

 

[Guest]

Max,
They're on separate sheets...

sorry, still don't get you, Dennis

This formula below is presumably in say, Sheet2??:

={index('SHEET1'!$A$1:$AK$12000,MATCH(C2&"",'SHEET1'!$D$1:$D$12000&'SHEET1'!$E$1:$E$12000,0),30)}

and if so, the cells referred to in these following lines:
are also in Sheet2, yes?

If so, then ..

Going by the above lines, for the formula in cell C2,
won't you be hit with a circular ref error ?

Perhaps you could paste the actual formulas
you have in Sheet2's B2, C2 and D2

 

[Biff]

Hi!

There does appear to be a circular ref issue but that
shouldn't cause Excel to crash although that may be a
contributing factor!

By my calculations, you have 112,473 *robust* array
formulas just for this one operation. I think you're
having a serious system resource issue.

This site may have something to help:

http://www.decisionmodels.com/

Biff

 

[Guest]

Guess I was still floundering <g> trying to grasp the underlying logic behind
the OP's intent from the descript given. And thinking maybe there exists a
better way (another formula ?) to achieve the same intent w/o getting into
sys resource issues.

 

[Guest]

Just a little OT, Biff, apologies
Believe you are accessing microsoft.public.excel via CDO ?
Could you post the URL to access this newsgroup?
The CDO access to this newsgroup seems elusive
Thanks

 

[Biff]

Hi Max!

Here's a link to a version of CDO that's not too bad. In
fact, I prefer it over a newsreader:

http://communities.microsoft.com/newsgroups/default.asp

Scroll down about 1/3 of the way to get the English Excel
groups.

This link is to a different version of CDO. It's
absolutely horrible and the designer should be fired:

http://support.microsoft.com/newsgroups/

Biff

 

[Guest]

Thanks, Biff !

 

[Guest]

Max,

Sorry just getting back to you... The underlying logic is:

I have on sheet2 some data -- say:
Product Cost Pack Tax Weight
Orange 1.50 6 .7 2lbs
Mango 3.00 12 .85 4lbs

On Sheet1 I have some data (12,000 rows)
User Product Quantity [Pack] [Cost] [Weight] [Tax]
Davis Mango 15
Leslie Orange 24
etc...

I use the function above to return the values that I need... I hope this
helps.

Thanks
Dennis G.

 

[Max]

One way to try ..

Assuming this reference table is in Sheet2,
cols A to G, data from row2 down

Product Cost Pack Tax Weight
Orange 1.5 6 0.7 2lbs
Mango 3 12 0.85 4lbs

And in Sheet1, in cols A to G, you have:

User Product Quantity [Pack] [Cost] [Weight] [Tax]
Davis Mango 15
Leslie Orange 24

where Pack, Cost, Weight and Tax are in cols D to G

Try these formulas in:

D2:
=IF(ISNA(MATCH($B2,Sheet2!$A:$A,0)),"",INDEX(Sheet2!C:C,MATCH($B2,Sheet2!$A:
$A,0)))

E2:
=IF(ISNA(MATCH($B2,Sheet2!$A:$A,0)),"",INDEX(Sheet2!B:B,MATCH($B2,Sheet2!$A:
$A,0))*C2)

F2:
=IF(ISNA(MATCH($B2,Sheet2!$A:$A,0)),"",INDEX(Sheet2!E:E,MATCH($B2,Sheet2!$A:
$A,0)))

G2:
=IF(ISNA(MATCH($B2,Sheet2!$A:$A,0)),"",INDEX(Sheet2!D,MATCH($B2,Sheet2!$A:
$A,0)))

[ For G2, leave it to you to complete the tax computation, if that's what is
wanted in col G. The formula in G2 just retrieves the tax figs from Sheet2 ]

Select D2:G2,
fill down as many rows as there is data in cols A to C

Adapt to suit ..

 

[Guest]

Thanks... It liked it better.

-----Original Message-----
One way to try ..

Assuming this reference table is in Sheet2,
cols A to G, data from row2 down

Product Cost Pack Tax Weight
Orange 1.5 6 0.7 2lbs
Mango 3 12 0.85 4lbs

And in Sheet1, in cols A to G, you have:

User Product Quantity [Pack] [Cost] [Weight] [Tax]
Davis Mango 15
Leslie Orange 24

where Pack, Cost, Weight and Tax are in cols D to G

Try these formulas in:

D2:
=IF(ISNA(MATCH($B2,Sheet2!$A:$A,0)),"",INDEX(Sheet2! C:C,MATCH($B2,Sheet2!$A:

$A,0)))

E2:
=IF(ISNA(MATCH($B2,Sheet2!$A:$A,0)),"",INDEX(Sheet2! B:B,MATCH($B2,Sheet2!$A:
$A,0))*C2)

F2:
=IF(ISNA(MATCH($B2,Sheet2!$A:$A,0)),"",INDEX(Sheet2! E:E,MATCH($B2,Sheet2!$A:
$A,0)))

G2:
=IF(ISNA(MATCH($B2,Sheet2!$A:$A,0)),"",INDEX(Sheet2!

D,MATCH($B2,Sheet2!$A:
$A,0)))

[ For G2, leave it to you to complete the tax computation, if that's what is
wanted in col G. The formula in G2 just retrieves the tax figs from Sheet2 ]

Select D2:G2,
fill down as many rows as there is data in cols A to C

Adapt to suit ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

Max,

Sorry just getting back to you... The underlying logic is:

I have on sheet2 some data -- say:
Product Cost Pack Tax Weight
Orange 1.50 6 .7 2lbs
Mango 3.00 12 .85 4lbs

On Sheet1 I have some data (12,000 rows)
User Product Quantity [Pack] [Cost] [Weight] [Tax]
Davis Mango 15
Leslie Orange 24
etc...

I use the function above to return the values that I need... I hope this
helps.

Thanks
Dennis G.

.

 

[Guest]

Thanks... It liked it better.

Glad to hear that !
Thanks for the feedback