|
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 |
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 |
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 |
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 |
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 |
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!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 |
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!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 |
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: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 |
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: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 |
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: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 |
Threed Function
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 |
Threed Function
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 |
Threed Function
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 |
Threed Function
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 |
Threed Function
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 |
using THREED function with VLOOKUP to sum across multiple workshee
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! |
using THREED function with VLOOKUP to sum across multiple workshee
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. |
| All times are GMT +1. The time now is 07:34 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com