I have downloaded this function, but cannot seem to make it work. I need to
sum up 13 sheets using a vlookup reference (cell B13). This is what I used:

=SUM(VLOOKUP(B13,THREED(EUR:VGS!$B$12:$V$120),6,0) )

EUR:VGS are the sheet names. Each sheet in between has a different name. Is
my naming convention not going to work? Right now it only returns the value
from the first sheet (EUR) only.

Thanks
CM

Try...

=SUMPRODUCT(--(THREED(EUR:VGS!$B$12:$B$120)=B13),THREED(EUR:VGS! $G$12:$G$
120))

Hope this helps!

In article ,
CM wrote:

I have downloaded this function, but cannot seem to make it work. I need to
sum up 13 sheets using a vlookup reference (cell B13). This is what I used:

=SUM(VLOOKUP(B13,THREED(EUR:VGS!$B$12:$V$120),6,0) )

EUR:VGS are the sheet names. Each sheet in between has a different name. Is
my naming convention not going to work? Right now it only returns the value
from the first sheet (EUR) only.

Thanks
CM

Sorry about my super late reply, wasn't at work for the past week, but I
wanted to thank you for your response.

Perfection, sir!

My Excel skills have gone from lowly intern to near-mad-ninja (relative to
my officemates) thanks to everyone on this forum! I owe you all a beer.


"Domenic" wrote:

Try...

=SUMPRODUCT(--(THREED(EUR:VGS!$B$12:$B$120)=B13),THREED(EUR:VGS! $G$12:$G$
120))

Hope this helps!

In article ,
CM wrote:

I have downloaded this function, but cannot seem to make it work. I need to
sum up 13 sheets using a vlookup reference (cell B13). This is what I used:

=SUM(VLOOKUP(B13,THREED(EUR:VGS!$B$12:$V$120),6,0) )

EUR:VGS are the sheet names. Each sheet in between has a different name. Is
my naming convention not going to work? Right now it only returns the value
from the first sheet (EUR) only.

Thanks
CM

Hi
I have similar requirement and it did not work for me, can you please fix
the error in my formula
=SUMPRODUCT(--(THREED('Jan ''08:May ''08'!$K$6:$K$8)=K12),THREED('Jan
''08:May ''08'!$L$6:$L$8))
this is only for testing before i use on required big range.
thanks in advance


"Domenic" wrote:

Try...

=SUMPRODUCT(--(THREED(EUR:VGS!$B$12:$B$120)=B13),THREED(EUR:VGS! $G$12:$G$
120))

Hope this helps!

In article ,
CM wrote:

I have downloaded this function, but cannot seem to make it work. I need to
sum up 13 sheets using a vlookup reference (cell B13). This is what I used:

=SUM(VLOOKUP(B13,THREED(EUR:VGS!$B$12:$V$120),6,0) )

EUR:VGS are the sheet names. Each sheet in between has a different name. Is
my naming convention not going to work? Right now it only returns the value
from the first sheet (EUR) only.

Thanks
CM

Dominic

I am having reall issues with Threed.

Im trying to incorporate it into a sumproduct as shown in your last
response. The formula is

=SUMPRODUCT(--(THREED(Start:End!A3)=A6),--(THREED(Start:End!A6:A9)=$B6),THREED(Start:End!C6: D9))

However the first two Threed arrays look at text rather than figures. For
example Start:end!A3 looks at two worksheets with the words Exeter in
worksheet 1 and Barnsley in worksheet 2. However within the formula above the
threed function returns

{0;"Exe ";"Barn ";0}

which when I put in the condition =A6 of "Exeter", the formula returns False.

Is this a limitation of Threed or am I doing something wrong?

Cheers

Matt

"Domenic" wrote:

Try...

=SUMPRODUCT(--(THREED(EUR:VGS!$B$12:$B$120)=B13),THREED(EUR:VGS! $G$12:$G$
120))

Hope this helps!

In article ,
CM wrote:

I have downloaded this function, but cannot seem to make it work. I need to
sum up 13 sheets using a vlookup reference (cell B13). This is what I used:

=SUM(VLOOKUP(B13,THREED(EUR:VGS!$B$12:$V$120),6,0) )

EUR:VGS are the sheet names. Each sheet in between has a different name. Is
my naming convention not going to work? Right now it only returns the value
from the first sheet (EUR) only.

Thanks
CM

The ranges for each argument need to be the same size. For which range
does the first condition need to be met?

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article ,
MattEd101 wrote:

Dominic

I am having reall issues with Threed.

Im trying to incorporate it into a sumproduct as shown in your last
response. The formula is

=SUMPRODUCT(--(THREED(Start:End!A3)=A6),--(THREED(Start:End!A6:A9)=$B6),THREED
(Start:End!C6:D9))

However the first two Threed arrays look at text rather than figures. For
example Start:end!A3 looks at two worksheets with the words Exeter in
worksheet 1 and Barnsley in worksheet 2. However within the formula above the
threed function returns

{0;"Exe ";"Barn ";0}

which when I put in the condition =A6 of "Exeter", the formula returns False.

Is this a limitation of Threed or am I doing something wrong?

Cheers

Matt

I realised I hadn't used ranges of the same size this morning, works a treat
now

Cheers for the response

You will probably see me on here again at some point.

Matt

"Domenic" wrote:

The ranges for each argument need to be the same size. For which range
does the first condition need to be met?

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article ,
MattEd101 wrote:

Dominic

I am having reall issues with Threed.

Im trying to incorporate it into a sumproduct as shown in your last
response. The formula is

=SUMPRODUCT(--(THREED(Start:End!A3)=A6),--(THREED(Start:End!A6:A9)=$B6),THREED
(Start:End!C6:D9))

However the first two Threed arrays look at text rather than figures. For
example Start:end!A3 looks at two worksheets with the words Exeter in
worksheet 1 and Barnsley in worksheet 2. However within the formula above the
threed function returns

{0;"Exe ";"Barn ";0}

which when I put in the condition =A6 of "Exeter", the formula returns False.

Is this a limitation of Threed or am I doing something wrong?

Cheers

Matt

I say works a treat

my new formula turned out to be

=SUMPRODUCT(--(THREED(start:End!$A$9:$A$13)=$B9),--(THREED(start:End!$B$9:$B$13)=$A9),THREED(start:En d!D$9:D$13))

Which worked. This was only a test however so I tried to replicate with the
following (which has 3 conditions)

=SUMPRODUCT(--(THREED(Start:End!$B$154:$B$180)=$E129),--(THREED(Start:End!$C$154:$C$180)=$D129),--(THREED(Start:End!$C$154:$C$180)=$F129),THREED(Sta rt:End!$H$154:$H$180))

But each time I press F9 on the Threed arrays, the result does not refer to
the data in the colums specified.

E.g C154:C180 should look at an array of numbers, but when you press F9, it
actually returns an array of place names which is what B154:B180 should be
looking at! I thought anchoring the array would help but apparently not





"MattEd101" wrote:

I realised I hadn't used ranges of the same size this morning, works a treat
now

Cheers for the response

You will probably see me on here again at some point.

Matt

"Domenic" wrote:

The ranges for each argument need to be the same size. For which range
does the first condition need to be met?

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article ,
MattEd101 wrote:

Dominic

I am having reall issues with Threed.

Im trying to incorporate it into a sumproduct as shown in your last
response. The formula is

=SUMPRODUCT(--(THREED(Start:End!A3)=A6),--(THREED(Start:End!A6:A9)=$B6),THREED
(Start:End!C6:D9))

However the first two Threed arrays look at text rather than figures. For
example Start:end!A3 looks at two worksheets with the words Exeter in
worksheet 1 and Barnsley in worksheet 2. However within the formula above the
threed function returns

{0;"Exe ";"Barn ";0}

which when I put in the condition =A6 of "Exeter", the formula returns False.

Is this a limitation of Threed or am I doing something wrong?

Cheers

Matt

Is there a specific order you have to put the conditions in? With one
condition it works fine. With two it seems to throw it out completely and
starts looking at arrays that yo havent even specified in the formula

Each time I press F9 on the Threed array, the date it refers to changes

"MattEd101" wrote:

I say works a treat

my new formula turned out to be

=SUMPRODUCT(--(THREED(start:End!$A$9:$A$13)=$B9),--(THREED(start:End!$B$9:$B$13)=$A9),THREED(start:En d!D$9:D$13))

Which worked. This was only a test however so I tried to replicate with the
following (which has 3 conditions)

=SUMPRODUCT(--(THREED(Start:End!$B$154:$B$180)=$E129),--(THREED(Start:End!$C$154:$C$180)=$D129),--(THREED(Start:End!$C$154:$C$180)=$F129),THREED(Sta rt:End!$H$154:$H$180))

But each time I press F9 on the Threed arrays, the result does not refer to
the data in the colums specified.

E.g C154:C180 should look at an array of numbers, but when you press F9, it
actually returns an array of place names which is what B154:B180 should be
looking at! I thought anchoring the array would help but apparently not





"MattEd101" wrote:

I realised I hadn't used ranges of the same size this morning, works a treat
now

Cheers for the response

You will probably see me on here again at some point.

Matt

"Domenic" wrote:

The ranges for each argument need to be the same size. For which range
does the first condition need to be met?

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article ,
MattEd101 wrote:

Dominic

I am having reall issues with Threed.

Im trying to incorporate it into a sumproduct as shown in your last
response. The formula is

=SUMPRODUCT(--(THREED(Start:End!A3)=A6),--(THREED(Start:End!A6:A9)=$B6),THREED
(Start:End!C6:D9))

However the first two Threed arrays look at text rather than figures. For
example Start:end!A3 looks at two worksheets with the words Exeter in
worksheet 1 and Barnsley in worksheet 2. However within the formula above the
threed function returns

{0;"Exe ";"Barn ";0}

which when I put in the condition =A6 of "Exeter", the formula returns False.

Is this a limitation of Threed or am I doing something wrong?

Cheers

Matt

=SUMPRODUCT(--(THREED(Start:End!$B$154:$B$180)=$E129),--(THREED(Start:End!$C$1
54:$C$180)=$D129),--(THREED(Start:End!$C$154:$C$180)=$F129),THREED(Sta rt:End!$
H$154:$H$180))

In the above formula, the second and third arguments of SUMPRODUCT both
refer to Column C, for which two different criteria has to met. Can you
confirm which columns need to be referenced, and the condition that
applies to each?

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article ,
MattEd101 wrote:

I say works a treat

my new formula turned out to be

=SUMPRODUCT(--(THREED(start:End!$A$9:$A$13)=$B9),--(THREED(start:End!$B$9:$B$1
3)=$A9),THREED(start:End!D$9:D$13))

Which worked. This was only a test however so I tried to replicate with the
following (which has 3 conditions)

=SUMPRODUCT(--(THREED(Start:End!$B$154:$B$180)=$E129),--(THREED(Start:End!$C$1
54:$C$180)=$D129),--(THREED(Start:End!$C$154:$C$180)=$F129),THREED(Sta rt:End!$
H$154:$H$180))

But each time I press F9 on the Threed arrays, the result does not refer to
the data in the colums specified.

E.g C154:C180 should look at an array of numbers, but when you press F9, it
actually returns an array of place names which is what B154:B180 should be
looking at! I thought anchoring the array would help but apparently not





"MattEd101" wrote:

I realised I hadn't used ranges of the same size this morning, works a
treat
now

Cheers for the response

You will probably see me on here again at some point.

Matt

"Domenic" wrote:

The ranges for each argument need to be the same size. For which range
does the first condition need to be met?

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article ,
MattEd101 wrote:

Dominic

I am having reall issues with Threed.

Im trying to incorporate it into a sumproduct as shown in your last
response. The formula is

=SUMPRODUCT(--(THREED(Start:End!A3)=A6),--(THREED(Start:End!A6:A9)=$B6),
THREED
(Start:End!C6:D9))

However the first two Threed arrays look at text rather than figures.
For
example Start:end!A3 looks at two worksheets with the words Exeter in
worksheet 1 and Barnsley in worksheet 2. However within the formula
above the
threed function returns

{0;"Exe ";"Barn ";0}

which when I put in the condition =A6 of "Exeter", the formula returns
False.

Is this a limitation of Threed or am I doing something wrong?

Cheers

Matt

Column B has a place name in e.g. Exeter on one worksheet or Barnsley in
another. E129 is being the condition

Column C has a series of numbers 1- 27. F129 and D129 are the conditions
e.g. 22 and 5

So what I am trying to achieve is if Column B contains Exeter then look at
that specific worksheet withing the range of worksheets and sumproduct two
lines of cash flows based on the two rows where the numbers 22 and 5 are in
Column C

Hope you can understand that

"Domenic" wrote:

=SUMPRODUCT(--(THREED(Start:End!$B$154:$B$180)=$E129),--(THREED(Start:End!$C$1
54:$C$180)=$D129),--(THREED(Start:End!$C$154:$C$180)=$F129),THREED(Sta rt:End!$
H$154:$H$180))

In the above formula, the second and third arguments of SUMPRODUCT both
refer to Column C, for which two different criteria has to met. Can you
confirm which columns need to be referenced, and the condition that
applies to each?

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article ,
MattEd101 wrote:

I say works a treat

my new formula turned out to be

=SUMPRODUCT(--(THREED(start:End!$A$9:$A$13)=$B9),--(THREED(start:End!$B$9:$B$1
3)=$A9),THREED(start:End!D$9:D$13))

Which worked. This was only a test however so I tried to replicate with the
following (which has 3 conditions)

=SUMPRODUCT(--(THREED(Start:End!$B$154:$B$180)=$E129),--(THREED(Start:End!$C$1
54:$C$180)=$D129),--(THREED(Start:End!$C$154:$C$180)=$F129),THREED(Sta rt:End!$
H$154:$H$180))

But each time I press F9 on the Threed arrays, the result does not refer to
the data in the colums specified.

E.g C154:C180 should look at an array of numbers, but when you press F9, it
actually returns an array of place names which is what B154:B180 should be
looking at! I thought anchoring the array would help but apparently not





"MattEd101" wrote:

I realised I hadn't used ranges of the same size this morning, works a
treat
now

Cheers for the response

You will probably see me on here again at some point.

Matt

"Domenic" wrote:

The ranges for each argument need to be the same size. For which range
does the first condition need to be met?

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article ,
MattEd101 wrote:

Dominic

I am having reall issues with Threed.

Im trying to incorporate it into a sumproduct as shown in your last
response. The formula is

=SUMPRODUCT(--(THREED(Start:End!A3)=A6),--(THREED(Start:End!A6:A9)=$B6),
THREED
(Start:End!C6:D9))

However the first two Threed arrays look at text rather than figures.
For
example Start:end!A3 looks at two worksheets with the words Exeter in
worksheet 1 and Barnsley in worksheet 2. However within the formula
above the
threed function returns

{0;"Exe ";"Barn ";0}

which when I put in the condition =A6 of "Exeter", the formula returns
False.

Is this a limitation of Threed or am I doing something wrong?

Cheers

Matt

Unclear... Can you provide a sample of data (about 10 rows) for 3
sheets, describe how the calculation needs to take place, and provide
the actual expected results based on the sample data?

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article ,
MattEd101 wrote:

Column B has a place name in e.g. Exeter on one worksheet or Barnsley in
another. E129 is being the condition

Column C has a series of numbers 1- 27. F129 and D129 are the conditions
e.g. 22 and 5

So what I am trying to achieve is if Column B contains Exeter then look at
that specific worksheet withing the range of worksheets and sumproduct two
lines of cash flows based on the two rows where the numbers 22 and 5 are in
Column C

Hope you can understand that

"Domenic" wrote:

=SUMPRODUCT(--(THREED(Start:End!$B$154:$B$180)=$E129),--(THREED(Start:End!
$C$1
54:$C$180)=$D129),--(THREED(Start:End!$C$154:$C$180)=$F129),THREED(Sta rt:E
nd!$
H$154:$H$180))

In the above formula, the second and third arguments of SUMPRODUCT both
refer to Column C, for which two different criteria has to met. Can you
confirm which columns need to be referenced, and the condition that
applies to each?

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article ,
MattEd101 wrote:

I say works a treat

my new formula turned out to be

=SUMPRODUCT(--(THREED(start:End!$A$9:$A$13)=$B9),--(THREED(start:End!$B$9:
$B$1
3)=$A9),THREED(start:End!D$9:D$13))

Which worked. This was only a test however so I tried to replicate with
the
following (which has 3 conditions)

=SUMPRODUCT(--(THREED(Start:End!$B$154:$B$180)=$E129),--(THREED(Start:End!
$C$1
54:$C$180)=$D129),--(THREED(Start:End!$C$154:$C$180)=$F129),THREED(Sta rt:E
nd!$
H$154:$H$180))

But each time I press F9 on the Threed arrays, the result does not refer
to
the data in the colums specified.

E.g C154:C180 should look at an array of numbers, but when you press F9,
it
actually returns an array of place names which is what B154:B180 should
be
looking at! I thought anchoring the array would help but apparently not





"MattEd101" wrote:

I realised I hadn't used ranges of the same size this morning, works a
treat
now

Cheers for the response

You will probably see me on here again at some point.

Matt

"Domenic" wrote:

The ranges for each argument need to be the same size. For which
range
does the first condition need to be met?

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article ,
MattEd101 wrote:

Dominic

I am having reall issues with Threed.

Im trying to incorporate it into a sumproduct as shown in your last
response. The formula is

=SUMPRODUCT(--(THREED(Start:End!A3)=A6),--(THREED(Start:End!A6:A9)=$
B6),
THREED
(Start:End!C6:D9))

However the first two Threed arrays look at text rather than
figures.
For
example Start:end!A3 looks at two worksheets with the words Exeter
in
worksheet 1 and Barnsley in worksheet 2. However within the formula
above the
threed function returns

{0;"Exe ";"Barn ";0}

which when I put in the condition =A6 of "Exeter", the formula
returns
False.

Is this a limitation of Threed or am I doing something wrong?

Cheers

Matt

Thanks for all your help, I've managed to get two conditions working fine
now, I think three just confuses it somewhat. I managed to structure it so as
not to require three conditions in one line of coding.

Matt

"Domenic" wrote:

Unclear... Can you provide a sample of data (about 10 rows) for 3
sheets, describe how the calculation needs to take place, and provide
the actual expected results based on the sample data?

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article ,
MattEd101 wrote:

Column B has a place name in e.g. Exeter on one worksheet or Barnsley in
another. E129 is being the condition

Column C has a series of numbers 1- 27. F129 and D129 are the conditions
e.g. 22 and 5

So what I am trying to achieve is if Column B contains Exeter then look at
that specific worksheet withing the range of worksheets and sumproduct two
lines of cash flows based on the two rows where the numbers 22 and 5 are in
Column C

Hope you can understand that

"Domenic" wrote:

=SUMPRODUCT(--(THREED(Start:End!$B$154:$B$180)=$E129),--(THREED(Start:End!
$C$1
54:$C$180)=$D129),--(THREED(Start:End!$C$154:$C$180)=$F129),THREED(Sta rt:E
nd!$
H$154:$H$180))

In the above formula, the second and third arguments of SUMPRODUCT both
refer to Column C, for which two different criteria has to met. Can you
confirm which columns need to be referenced, and the condition that
applies to each?

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article ,
MattEd101 wrote:

I say works a treat

my new formula turned out to be

=SUMPRODUCT(--(THREED(start:End!$A$9:$A$13)=$B9),--(THREED(start:End!$B$9:
$B$1
3)=$A9),THREED(start:End!D$9:D$13))

Which worked. This was only a test however so I tried to replicate with
the
following (which has 3 conditions)

=SUMPRODUCT(--(THREED(Start:End!$B$154:$B$180)=$E129),--(THREED(Start:End!
$C$1
54:$C$180)=$D129),--(THREED(Start:End!$C$154:$C$180)=$F129),THREED(Sta rt:E
nd!$
H$154:$H$180))

But each time I press F9 on the Threed arrays, the result does not refer
to
the data in the colums specified.

E.g C154:C180 should look at an array of numbers, but when you press F9,
it
actually returns an array of place names which is what B154:B180 should
be
looking at! I thought anchoring the array would help but apparently not





"MattEd101" wrote:

I realised I hadn't used ranges of the same size this morning, works a
treat
now

Cheers for the response

You will probably see me on here again at some point.

Matt

"Domenic" wrote:

The ranges for each argument need to be the same size. For which
range
does the first condition need to be met?

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article ,
MattEd101 wrote:

Dominic

I am having reall issues with Threed.

Im trying to incorporate it into a sumproduct as shown in your last
response. The formula is

=SUMPRODUCT(--(THREED(Start:End!A3)=A6),--(THREED(Start:End!A6:A9)=$
B6),
THREED
(Start:End!C6:D9))

However the first two Threed arrays look at text rather than
figures.
For
example Start:end!A3 looks at two worksheets with the words Exeter
in
worksheet 1 and Barnsley in worksheet 2. However within the formula
above the
threed function returns

{0;"Exe ";"Barn ";0}

which when I put in the condition =A6 of "Exeter", the formula
returns
False.

Is this a limitation of Threed or am I doing something wrong?

Cheers

Matt

Hello again

Are you able to help with VBA code.

I want to add a new row above where in Column C it says "DO NOT DELETE"

My coding below doesnt quite work

Sub Project_Insert()

For Each c In Range(c, c).Cells()

If c.Text = "DO NOT DELETE" Then

c.Offset = (-1)

ActiveCell.EntireRow.Select

Range.Insert (xlShiftDown)

End If

Next c

End Sub

Any help would be appreciated

"MattEd101" wrote:

Thanks for all your help, I've managed to get two conditions working fine
now, I think three just confuses it somewhat. I managed to structure it so as
not to require three conditions in one line of coding.

Matt

"Domenic" wrote:

Unclear... Can you provide a sample of data (about 10 rows) for 3
sheets, describe how the calculation needs to take place, and provide
the actual expected results based on the sample data?

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article ,
MattEd101 wrote:

Column B has a place name in e.g. Exeter on one worksheet or Barnsley in
another. E129 is being the condition

Column C has a series of numbers 1- 27. F129 and D129 are the conditions
e.g. 22 and 5

So what I am trying to achieve is if Column B contains Exeter then look at
that specific worksheet withing the range of worksheets and sumproduct two
lines of cash flows based on the two rows where the numbers 22 and 5 are in
Column C

Hope you can understand that

"Domenic" wrote:

=SUMPRODUCT(--(THREED(Start:End!$B$154:$B$180)=$E129),--(THREED(Start:End!
$C$1
54:$C$180)=$D129),--(THREED(Start:End!$C$154:$C$180)=$F129),THREED(Sta rt:E
nd!$
H$154:$H$180))

In the above formula, the second and third arguments of SUMPRODUCT both
refer to Column C, for which two different criteria has to met. Can you
confirm which columns need to be referenced, and the condition that
applies to each?

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article ,
MattEd101 wrote:

I say works a treat

my new formula turned out to be

=SUMPRODUCT(--(THREED(start:End!$A$9:$A$13)=$B9),--(THREED(start:End!$B$9:
$B$1
3)=$A9),THREED(start:End!D$9:D$13))

Which worked. This was only a test however so I tried to replicate with
the
following (which has 3 conditions)

=SUMPRODUCT(--(THREED(Start:End!$B$154:$B$180)=$E129),--(THREED(Start:End!
$C$1
54:$C$180)=$D129),--(THREED(Start:End!$C$154:$C$180)=$F129),THREED(Sta rt:E
nd!$
H$154:$H$180))

But each time I press F9 on the Threed arrays, the result does not refer
to
the data in the colums specified.

E.g C154:C180 should look at an array of numbers, but when you press F9,
it
actually returns an array of place names which is what B154:B180 should
be
looking at! I thought anchoring the array would help but apparently not





"MattEd101" wrote:

I realised I hadn't used ranges of the same size this morning, works a
treat
now

Cheers for the response

You will probably see me on here again at some point.

Matt

"Domenic" wrote:

The ranges for each argument need to be the same size. For which
range
does the first condition need to be met?

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article ,
MattEd101 wrote:

Dominic

I am having reall issues with Threed.

Im trying to incorporate it into a sumproduct as shown in your last
response. The formula is

=SUMPRODUCT(--(THREED(Start:End!A3)=A6),--(THREED(Start:End!A6:A9)=$
B6),
THREED
(Start:End!C6:D9))

However the first two Threed arrays look at text rather than
figures.
For
example Start:end!A3 looks at two worksheets with the words Exeter
in
worksheet 1 and Barnsley in worksheet 2. However within the formula
above the
threed function returns

{0;"Exe ";"Barn ";0}

which when I put in the condition =A6 of "Exeter", the formula
returns
False.

Is this a limitation of Threed or am I doing something wrong?

Cheers

Matt

For VBA, I'll have to defer to others...

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article ,
MattEd101 wrote:

Hello again

Are you able to help with VBA code.

I want to add a new row above where in Column C it says "DO NOT DELETE"

My coding below doesnt quite work

Sub Project_Insert()

For Each c In Range(c, c).Cells()

If c.Text = "DO NOT DELETE" Then

c.Offset = (-1)

ActiveCell.EntireRow.Select

Range.Insert (xlShiftDown)

End If

Next c

End Sub

Any help would be appreciated

"MattEd101" wrote:

Thanks for all your help, I've managed to get two conditions working fine
now, I think three just confuses it somewhat. I managed to structure it so
as
not to require three conditions in one line of coding.

Matt

"Domenic" wrote:

Unclear... Can you provide a sample of data (about 10 rows) for 3
sheets, describe how the calculation needs to take place, and provide
the actual expected results based on the sample data?

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article ,
MattEd101 wrote:

Column B has a place name in e.g. Exeter on one worksheet or Barnsley
in
another. E129 is being the condition

Column C has a series of numbers 1- 27. F129 and D129 are the
conditions
e.g. 22 and 5

So what I am trying to achieve is if Column B contains Exeter then look
at
that specific worksheet withing the range of worksheets and sumproduct
two
lines of cash flows based on the two rows where the numbers 22 and 5
are in
Column C

Hope you can understand that

"Domenic" wrote:

=SUMPRODUCT(--(THREED(Start:End!$B$154:$B$180)=$E129),--(THREED(Star
t:End!
$C$1
54:$C$180)=$D129),--(THREED(Start:End!$C$154:$C$180)=$F129),THREED(S
tart:E
nd!$
H$154:$H$180))

In the above formula, the second and third arguments of SUMPRODUCT
both
refer to Column C, for which two different criteria has to met. Can
you
confirm which columns need to be referenced, and the condition that
applies to each?

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article ,
MattEd101 wrote:

I say works a treat

my new formula turned out to be

=SUMPRODUCT(--(THREED(start:End!$A$9:$A$13)=$B9),--(THREED(start:End
!$B$9:
$B$1
3)=$A9),THREED(start:End!D$9:D$13))

Which worked. This was only a test however so I tried to replicate
with
the
following (which has 3 conditions)

=SUMPRODUCT(--(THREED(Start:End!$B$154:$B$180)=$E129),--(THREED(Star
t:End!
$C$1
54:$C$180)=$D129),--(THREED(Start:End!$C$154:$C$180)=$F129),THREED(S
tart:E
nd!$
H$154:$H$180))

But each time I press F9 on the Threed arrays, the result does not
refer
to
the data in the colums specified.

E.g C154:C180 should look at an array of numbers, but when you
press F9,
it
actually returns an array of place names which is what B154:B180
should
be
looking at! I thought anchoring the array would help but apparently
not





"MattEd101" wrote:

I realised I hadn't used ranges of the same size this morning,
works a
treat
now

Cheers for the response

You will probably see me on here again at some point.

Matt

"Domenic" wrote:

The ranges for each argument need to be the same size. For
which
range
does the first condition need to be met?

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article
,
MattEd101 wrote:

Dominic

I am having reall issues with Threed.

Im trying to incorporate it into a sumproduct as shown in
your last
response. The formula is

=SUMPRODUCT(--(THREED(Start:End!A3)=A6),--(THREED(Start:End!A6
:A9)=$
B6),
THREED
(Start:End!C6:D9))

However the first two Threed arrays look at text rather than
figures.
For
example Start:end!A3 looks at two worksheets with the words
Exeter
in
worksheet 1 and Barnsley in worksheet 2. However within the
formula
above the
threed function returns

{0;"Exe ";"Barn ";0}

which when I put in the condition =A6 of "Exeter", the
formula
returns
False.

Is this a limitation of Threed or am I doing something wrong?

Cheers

Matt

Hi,

I am having the same issues as others but haven't been able to resolve them.
The formula I have tried after the add in installation is
=SUMPRODUCT(--(THREED('General Scientific:tab1'!A3)=AE3,(THREED('General
Scientific:tab1'!J3)))). I am looking to sum j3 numbers in the same cell in
every tab if the criteria of ae3 matches that in a3 in every tab. Can you see
what my error could be here? So frustrating.

Thanks!

Sorry if this is a repeat but I can't see if my last one posted. I am trying
to replicate the same thing everyone else is but it's not working. I have the
add in. This is my formula
=SUMPRODUCT(--(THREED('B2B:tab1'!A3)=AE3,(THREED('B2B:tab1'!J3)) )). I am
trying to look in all tabs from the A3 cell that is equal to the contents of
AE3 and if it is to sum the range of all amounts in J3 on every tab. What am
I doing wrong? I get N/A as a result. Thanks.