A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

using THREED function with VLOOKUP to sum across multiple workshee



 
 
Thread Tools Display Modes
  #1  
Old May 21st, 2008, 06:40 PM posted to microsoft.public.excel.misc
CM
external usenet poster
 
Posts: 161
Default using THREED function with VLOOKUP to sum across multiple workshee

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
  #2  
Old May 21st, 2008, 11:56 PM posted to microsoft.public.excel.misc
Domenic[_2_]
external usenet poster
 
Posts: 265
Default using THREED function with VLOOKUP to sum across multiple workshee

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

  #3  
Old May 28th, 2008, 10:01 PM posted to microsoft.public.excel.misc
CM
external usenet poster
 
Posts: 161
Default using THREED function with VLOOKUP to sum across multiple work

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


  #4  
Old June 19th, 2008, 01:47 PM posted to microsoft.public.excel.misc
Eddy Stan
external usenet poster
 
Posts: 43
Default using THREED function with VLOOKUP to sum across multiple work

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


  #5  
Old September 29th, 2009, 09:49 PM posted to microsoft.public.excel.misc
MattEd101
external usenet poster
 
Posts: 7
Default Threed Function

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


  #6  
Old September 30th, 2009, 12:10 AM posted to microsoft.public.excel.misc
Domenic[_2_]
external usenet poster
 
Posts: 265
Default Threed Function

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!C69))

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

  #7  
Old September 30th, 2009, 09:21 AM posted to microsoft.public.excel.misc
MattEd101
external usenet poster
 
Posts: 7
Default Threed Function

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!C69))

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


  #8  
Old September 30th, 2009, 11:42 AM posted to microsoft.public.excel.misc
MattEd101
external usenet poster
 
Posts: 7
Default Threed Function

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$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!C69))

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


  #9  
Old September 30th, 2009, 11:58 AM posted to microsoft.public.excel.misc
MattEd101
external usenet poster
 
Posts: 7
Default Threed Function

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$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!C69))

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

  #10  
Old September 30th, 2009, 12:28 PM posted to microsoft.public.excel.misc
Domenic[_2_]
external usenet poster
 
Posts: 265
Default Threed Function

=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$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!C69))

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:07 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.