Copying an array formula...
 

Hello,

EXCEL 2003 --

I have the following formula:

={index('SHEET1'!$A$1:$AK$12000,MATCH(C2&"",'SHEET 1'!$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.

={index('SHEET1'!$A$1:$AK$12000,MATCH(C2&"",'SHEET 1'!$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 ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

"Dennis G." wrote:

Hello,

EXCEL 2003 --

I have the following formula:

={index('SHEET1'!$A$1:$AK$12000,MATCH(C2&"",'SHEET 1'!$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.

Max,

They're on separate sheets...

Dennis G.

"Dennis G." wrote:

Hello,

EXCEL 2003 --

I have the following formula:

={index('SHEET1'!$A$1:$AK$12000,MATCH(C2&"",'SHEET 1'!$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.

"Dennis G." wrote:
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&"",'SHEET 1'!$D$1:$D$12000&'SHEET1'!$E$1:$E$12000,0),30)}

and if so, the cells referred to in these following lines:
This s located in cell b2 and in cell c2:j2 I have the same formula with a
different number at the end.

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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Hi!

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

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

This site may have something to help:

http://www.decisionmodels.com/

Biff

-----Original Message-----
"Dennis G." wrote:
Max,
They're on separate sheets...

sorry, still don't get you, Dennis

This formula below is presumably in say, Sheet2??:
=3D{index('SHEET1'!$A$1:$AK$12000,MATCH(C2&"",'SHE ET1'!
$D$1:$D$12000&'SHEET1'!$E$1:$E$12000,0),30)}

and if so, the cells referred to in these following=20
lines: =20
This s located in cell b2 and in cell c2:j2 I have=20
the same formula with a=20
different number at the end.

are also in Sheet2, yes?=20

If so, then ..=20
Going by the above lines, for the formula in cell C2,=20
won't you be hit with a circular ref error ?

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

--
Rgds
Max
xl 97
---
GMT+8, 1=C2=B0 22' N 103=C2=B0 45' E
xdemechanik <atyahoo<dotcom
----

.

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.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

"Biff" wrote:

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

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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Hi Max!

Here's a link to a version of CDO that's not too bad. In=20
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=20
groups.

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

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

Biff

-----Original Message-----
Just a little OT, Biff, apologies=20
Believe you are accessing microsoft.public.excel via=20
CDO ?=20
Could you post the URL to access this newsgroup?=20
The CDO access to this newsgroup seems elusive
Thanks
--
Rgds
Max
xl 97
---
GMT+8, 1=C2=B0 22' N 103=C2=B0 45' E
xdemechanik <atyahoo<dotcom
----
=20

.

Thanks, Biff !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

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" wrote:

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.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

"Biff" wrote:

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

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(Sheet 2!C:C,MATCH($B2,Sheet2!$A:
$A,0)))

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

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

G2:
=IF(ISNA(MATCH($B2,Sheet2!$A:$A,0)),"",INDEX(Sheet 2!D: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 <atyahoo<dotcom
----
"Dennis G." wrote in message
...
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.

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:
=3DIF(ISNA(MATCH($B2,Sheet2!$A:$A,0)),"",INDEX(Sh eet2!
C:C,MATCH($B2,Sheet2!$A:
$A,0)))

E2:
=3DIF(ISNA(MATCH($B2,Sheet2!$A:$A,0)),"",INDEX(Sh eet2!
B:B,MATCH($B2,Sheet2!$A:
$A,0))*C2)

F2:
=3DIF(ISNA(MATCH($B2,Sheet2!$A:$A,0)),"",INDEX(Sh eet2!
E:E,MATCH($B2,Sheet2!$A:
$A,0)))

G2:
=3DIF(ISNA(MATCH($B2,Sheet2!$A:$A,0)),"",INDEX(Sh eet2!
D:D,MATCH($B2,Sheet2!$A:
$A,0)))

[ For G2, leave it to you to complete the tax=20
computation, if that's what is
wanted in col G. The formula in G2 just retrieves the tax=20
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=B0 22' N 103=B0 45' E
xdemechanik <atyahoo<dotcom
----
"Dennis G." wrote in=20
message
...
Max,

Sorry just getting back to you... The underlying logic=20
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] =20
[Tax]
Davis Mango 15
Leslie Orange 24
etc...

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

Thanks
Dennis G.


.

" wrote:
Thanks... It liked it better.

Glad to hear that !
Thanks for the feedback
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----