2

I'm synchronizing data with an external database and the result is delivered to me on a sheet called All Items. Setting up a pivot table is easy. However, on the horizontal axis I need to display a custom value, computed from one of the columns in the externally linked data set.

When I go to Analyze -> Change Data Source, I can see that the currently regarded data area is called 'All Items'!Query. I'd like to extend it by a column or two, so that my pivot table can display these values as well.

So, instead of 'All Items'!Query as the data source I'd like to have 'All Items'!Query and the next two columns too. I have no idea how to approach it nor what to try. Suggestions would be warmly appreciated.

I tried to define my own area called 'All Items'!Query_and_stuff but the number of records retrieved during synchronization varies, so my extension needs to take that into the account. No idea how.

Konrad Viltersten
  • 36,151
  • 76
  • 250
  • 438
  • try the OFFSET formula, either in your reference OR in a new Named range that you create based on the original Query named range. something like `=OFFSET('All Items'!Query;0;0;ROWS('All Items'!Query);COLUMNS('All Items'!Query)+2)` – K_B May 27 '13 at 13:00

1 Answers1

2

Define

'All Items'!Query_and_stuff = offset('All Items'!Query, 0,0 ROWS('All Items'!Query),COLUMNS('All Items'!Query)+2)

Kazimierz Jawor
  • 18,861
  • 7
  • 35
  • 55
gssi
  • 5,043
  • 5
  • 23
  • 23
  • @K_B Thanks, this might work. However, I'm not clear on how to define such a value. What I've done this far is to simply select the original data area **and by hand** add the two extra columns. Then, I just typed in the name in the box on the top left naming it. How do I go about defining the new area dynamically? – Konrad Viltersten May 27 '13 at 14:11
  • Depending on the version you are using: v2003 or earlier - Insert/Name/Define ...; v2010+ - select any cell and right-click and select 'Define Name'. – gssi May 28 '13 at 15:35
  • It works for a given run of the data synchronization. The problem is that when I update the data, the set might be longer (or shorter) and then I'll need to redefine the area manually. I need my user to just hit the update button and have the extended area to redefine itself. Is it at all doable? – Konrad Viltersten May 28 '13 at 19:55
  • Unless you have Automatic calculation turned off, the definition will redefine itself whenever the definition of Query is revised. Otherwise, you can issue an Application.Calculate command in a VBA Worksheet.Change event. – gssi May 28 '13 at 21:38
  • That'd be **perfect**! I'll try that out tomorrow at work. You're actually saying that if I design an area *Shazoo* by offset of *'Hazaa'!'Beep*, then *Shazoo* will get vertically longer if *Beep* gets vertically longer?! Cool stuff! – Konrad Viltersten May 29 '13 at 21:11