graphing a pivot table

tealeaf · May 21st, 2002, 06:32 AM

i know it`s possible to graph a pivot table on a histogram graph (the one with bars) but i was wondering if it`s possible to graph the pivot table using VBA for a scatter graph (the one with just points, no lines connecting them)

Ian Mac · May 21st, 2002, 06:33 AM

Quote:

On 2002-05-21 05:32, tealeaf wrote:
i know it`s possible to graph a pivot table on a histogram graph (the one with bars) but i was wondering if it`s possible to graph the pivot table using VBA for a scatter graph (the one with just points, no lines connecting them)

Why do you want to use VBA?

tealeaf · May 21st, 2002, 06:36 AM

well it`s part of a program which retrieves data from a database and then puts it in a pivot table then graphs the pivot table. if it`s possible without vba that`s cool too i guess.

Ian Mac · May 21st, 2002, 06:41 AM

Quote:

On 2002-05-21 05:36, tealeaf wrote:
well it`s part of a program which retrieves data from a database and then puts it in a pivot table then graphs the pivot table. if it`s possible without vba that`s cool too i guess.

I haven't used Scatter charts but you might like to use Dynamic Ranges to adjust the Data Ranges.

Look at:

http://www.mrexcel.com/board/viewtop...c=8379&forum=2

and

http://www.mrexcel.com/board/viewtop...c=8969&forum=2

Should get you started, I use this technique to Dynamically polt Column/Bar charts from a PivotTable that I Hide/Unhide items on.

If you need any more let me know.

Ian Mac · May 21st, 2002, 07:24 AM

Quote:

On 2002-05-21 05:41, Ian Mac wrote:

Quote:

On 2002-05-21 05:36, tealeaf wrote:
well it`s part of a program which retrieves data from a database and then puts it in a pivot table then graphs the pivot table. if it`s possible without vba that`s cool too i guess.

I haven't used Scatter charts but you might like to use Dynamic Ranges to adjust the Data Ranges.

Look at:

http://www.mrexcel.com/board/viewtop...c=8379&forum=2

and

http://www.mrexcel.com/board/viewtop...c=8969&forum=2

Should get you started, I use this technique to Dynamically polt Column/Bar charts from a PivotTable that I Hide/Unhide items on.

If you need any more let me know.

I don't understand what you mean by 'But my boss wants it done this way'? (other thread)

The solutions here can be expanded to meet your bosses requirements, if you let me know what you need. there is no VBA involved so which way does your boss want it done?

I assume your using Excel 97, otherwise you could've used Pivot Charts. Other than the way I propose I can't think of a way to Dynamically adjust a chart to a Pivot Table.

Ian Mac · May 21st, 2002, 07:29 AM

tealeaf

I'll continue with this thread, if it's all the same to you.

Check out the second one a proposed and come back to this thread with any questions, I've explain a little on that link about NamedRanges.

Don't worry we'll get this one licked yet.

tealeaf · May 21st, 2002, 08:14 AM

Quote:

On 2002-05-21 06:29, Ian Mac wrote:
tealeaf

I'll continue with this thread, if it's all the same to you.

Check out the second one a proposed and come back to this thread with any questions, I've explain a little on that link about NamedRanges.

Don't worry we'll get this one licked yet.

cute.
i tried ur second suggestion though i`m not sure what COUNTA is. also i`m getting an error when i type that code in the box (=OFFSET...)
it could be a typo but it could also be that i have Excel in French...

i`m not sure yet.
teal

tealeaf · May 21st, 2002, 08:26 AM

ok well right now, i have my graph done, and when i do an update and retrieve stuff from the database, my pivot table is updated just fine. using
Selection.QueryTable.Refresh BackgroundQuery:=False
however, the only way to get the graph to update so far is to hit save...kinda dumb no? i'm sure ur suggestions both work, but i don't get 'em...help? sorry.

Ian Mac · May 21st, 2002, 08:51 AM

Quote:

On 2002-05-21 07:26, tealeaf wrote:
ok well right now, i have my graph done, and when i do an update and retrieve stuff from the database, my pivot table is updated just fine. using
Selection.QueryTable.Refresh BackgroundQuery:=False
however, the only way to get the graph to update so far is to hit save...kinda dumb no? i'm sure ur suggestions both work, but i don't get 'em...help? sorry.

I've mailed you an example.

tealeaf · May 21st, 2002, 09:02 AM

i just checked my email, that was really sweet of u. i'm reading it now. thanks in advance.

May 21st, 2002, 07:29 AM	#6
Ian Mac MrExcel MVP Join Date: Feb 2002 Location: Newcastle, UK Posts: 1,174	tealeaf I'll continue with this thread, if it's all the same to you. Check out the second one a proposed and come back to this thread with any questions, I've explain a little on that link about NamedRanges. Don't worry we'll get this one licked yet. __________________ "Have a good time......all the time" Ian Mac

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 21st, 2002, 06:32 AM	#1
tealeaf Board Regular Join Date: May 2002 Location: mtl, canada Posts: 160	i know it`s possible to graph a pivot table on a histogram graph (the one with bars) but i was wondering if it`s possible to graph the pivot table using VBA for a scatter graph (the one with just points, no lines connecting them)

May 21st, 2002, 06:36 AM	#3
tealeaf Board Regular Join Date: May 2002 Location: mtl, canada Posts: 160	well it`s part of a program which retrieves data from a database and then puts it in a pivot table then graphs the pivot table. if it`s possible without vba that`s cool too i guess.

May 21st, 2002, 08:26 AM	#8
tealeaf Board Regular Join Date: May 2002 Location: mtl, canada Posts: 160	ok well right now, i have my graph done, and when i do an update and retrieve stuff from the database, my pivot table is updated just fine. using Selection.QueryTable.Refresh BackgroundQuery:=False however, the only way to get the graph to update so far is to hit save...kinda dumb no? i'm sure ur suggestions both work, but i don't get 'em...help? sorry.

May 21st, 2002, 09:02 AM	#10
tealeaf Board Regular Join Date: May 2002 Location: mtl, canada Posts: 160	i just checked my email, that was really sweet of u. i'm reading it now. thanks in advance.