Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Debra Dalgleish Question
Hi Debra,
Can you confirm for me that you can't use Dynamic Data Source tip in a named range with the Multiple Consolidation Ranges Pivots. E.g =OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),7) Also is the only way to join the tables to type in the sql field in MS Query (From Memory so my be wrong) SELECT * FROM Range Name 1 Range Name 1 UNION ALL SELECT * FROM Range Name 2 Range Name 2 Thanks VBA Noob |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Debra Dalgleish Question
You can create dynamic named ranges using that type of formula, then use
those names as the multiple consolidation ranges. There may be other techniques to join the tables, but that's one that I've used. VBA Noob wrote: Hi Debra, Can you confirm for me that you can't use Dynamic Data Source tip in a named range with the Multiple Consolidation Ranges Pivots. E.g =OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),7) Also is the only way to join the tables to type in the sql field in MS Query (From Memory so my be wrong) SELECT * FROM Range Name 1 Range Name 1 UNION ALL SELECT * FROM Range Name 2 Range Name 2 Thanks VBA Noob -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Debra Dalgleish Question
Thanks Debra
VBA Noob Debra Dalgleish wrote: You can create dynamic named ranges using that type of formula, then use those names as the multiple consolidation ranges. There may be other techniques to join the tables, but that's one that I've used. VBA Noob wrote: Hi Debra, Can you confirm for me that you can't use Dynamic Data Source tip in a named range with the Multiple Consolidation Ranges Pivots. E.g =OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),7) Also is the only way to join the tables to type in the sql field in MS Query (From Memory so my be wrong) SELECT * FROM Range Name 1 Range Name 1 UNION ALL SELECT * FROM Range Name 2 Range Name 2 Thanks VBA Noob -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Debra Dalgleish Question
Hi Debra,
For some reason it doesn't see the dynamic ranges. Any thoughts what could be wrong ?? Thanks VBA Noob VBA Noob wrote: Thanks Debra VBA Noob Debra Dalgleish wrote: You can create dynamic named ranges using that type of formula, then use those names as the multiple consolidation ranges. There may be other techniques to join the tables, but that's one that I've used. VBA Noob wrote: Hi Debra, Can you confirm for me that you can't use Dynamic Data Source tip in a named range with the Multiple Consolidation Ranges Pivots. E.g =OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),7) Also is the only way to join the tables to type in the sql field in MS Query (From Memory so my be wrong) SELECT * FROM Range Name 1 Range Name 1 UNION ALL SELECT * FROM Range Name 2 Range Name 2 Thanks VBA Noob -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Debra Dalgleish Question
What I described would work for dynamic ranges in the same workbook.
Perhaps yours are in different workbooks, so the technique doesn't work. VBA Noob wrote: Hi Debra, For some reason it doesn't see the dynamic ranges. Any thoughts what could be wrong ?? Thanks VBA Noob VBA Noob wrote: Thanks Debra VBA Noob Debra Dalgleish wrote: You can create dynamic named ranges using that type of formula, then use those names as the multiple consolidation ranges. There may be other techniques to join the tables, but that's one that I've used. VBA Noob wrote: Hi Debra, Can you confirm for me that you can't use Dynamic Data Source tip in a named range with the Multiple Consolidation Ranges Pivots. E.g =OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),7) Also is the only way to join the tables to type in the sql field in MS Query (From Memory so my be wrong) SELECT * FROM Range Name 1 Range Name 1 UNION ALL SELECT * FROM Range Name 2 Range Name 2 Thanks VBA Noob -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Debra Dalgleish Question
Hi Debra,
That's exactly what the problem is PS. Just got a copy of your book this morning and will look forward to picking up some more pivot tips VBA Noob Debra Dalgleish wrote: What I described would work for dynamic ranges in the same workbook. Perhaps yours are in different workbooks, so the technique doesn't work. VBA Noob wrote: Hi Debra, For some reason it doesn't see the dynamic ranges. Any thoughts what could be wrong ?? Thanks VBA Noob VBA Noob wrote: Thanks Debra VBA Noob Debra Dalgleish wrote: You can create dynamic named ranges using that type of formula, then use those names as the multiple consolidation ranges. There may be other techniques to join the tables, but that's one that I've used. VBA Noob wrote: Hi Debra, Can you confirm for me that you can't use Dynamic Data Source tip in a named range with the Multiple Consolidation Ranges Pivots. E.g =OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),7) Also is the only way to join the tables to type in the sql field in MS Query (From Memory so my be wrong) SELECT * FROM Range Name 1 Range Name 1 UNION ALL SELECT * FROM Range Name 2 Range Name 2 Thanks VBA Noob -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Debra Dalgleish Question
Thanks for buying a copy! I hope you find some new tips in it.
VBA Noob wrote: Hi Debra, That's exactly what the problem is PS. Just got a copy of your book this morning and will look forward to picking up some more pivot tips VBA Noob Debra Dalgleish wrote: What I described would work for dynamic ranges in the same workbook. Perhaps yours are in different workbooks, so the technique doesn't work. VBA Noob wrote: Hi Debra, For some reason it doesn't see the dynamic ranges. Any thoughts what could be wrong ?? Thanks VBA Noob VBA Noob wrote: Thanks Debra VBA Noob Debra Dalgleish wrote: You can create dynamic named ranges using that type of formula, then use those names as the multiple consolidation ranges. There may be other techniques to join the tables, but that's one that I've used. VBA Noob wrote: Hi Debra, Can you confirm for me that you can't use Dynamic Data Source tip in a named range with the Multiple Consolidation Ranges Pivots. E.g =OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),7) Also is the only way to join the tables to type in the sql field in MS Query (From Memory so my be wrong) SELECT * FROM Range Name 1 Range Name 1 UNION ALL SELECT * FROM Range Name 2 Range Name 2 Thanks VBA Noob -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Debra Dalgleish Question
Not had a chance to read it yet but I'm sure I will
VBA Noob Debra Dalgleish wrote: Thanks for buying a copy! I hope you find some new tips in it. VBA Noob wrote: Hi Debra, That's exactly what the problem is PS. Just got a copy of your book this morning and will look forward to picking up some more pivot tips VBA Noob Debra Dalgleish wrote: What I described would work for dynamic ranges in the same workbook. Perhaps yours are in different workbooks, so the technique doesn't work. VBA Noob wrote: Hi Debra, For some reason it doesn't see the dynamic ranges. Any thoughts what could be wrong ?? Thanks VBA Noob VBA Noob wrote: Thanks Debra VBA Noob Debra Dalgleish wrote: You can create dynamic named ranges using that type of formula, then use those names as the multiple consolidation ranges. There may be other techniques to join the tables, but that's one that I've used. VBA Noob wrote: Hi Debra, Can you confirm for me that you can't use Dynamic Data Source tip in a named range with the Multiple Consolidation Ranges Pivots. E.g =OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),7) Also is the only way to join the tables to type in the sql field in MS Query (From Memory so my be wrong) SELECT * FROM Range Name 1 Range Name 1 UNION ALL SELECT * FROM Range Name 2 Range Name 2 Thanks VBA Noob -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table Cycling Through Page Fields Automatically | Excel Discussion (Misc queries) | |||
Benefits many people - Question | Excel Discussion (Misc queries) | |||
How do I find and replace a question mark in Excel? | Excel Discussion (Misc queries) | |||
Multiple Consolidation Ranges - Debra Dalgleish | Excel Discussion (Misc queries) | |||
Hints And Tips For New Posters In The Excel Newsgroups | Excel Worksheet Functions |