Excel Dynamic Chart #2: Filter & Sort Feature

Hello,

I am trying to create a dynamic chart of interest rates over a 10 year period (daily rate entries -- thus over 3000 lines of data) for 18 countries. Eventually, I want to use a filter or pulldown menu so that the user can choose which country will display in the chart. Rates are updated daily. I am trying to decide if I should use the Table feature in Excel 2007 or do this by naming ranges and using the offset function. Or perhaps a combination of both?

Any insight is much appreciated!

--GinGin

Hi all,

I'm trying to figure out a drop down control for a series of charts based on one table of data.

What I've been trying to do is have all the charts on one sheet, and the data table on the other. Currently, the columns are filtered such that if the filters are employed, the data displayed in the charts varies. That's pretty standard I think. The data encompasses several divisions within one company. The desire is to have a manager for any given division select their division name from a drop down box on the chart sheet, and display the chart for their specific division. So far I have been unsuccessful.

The data table is constantly being added to as the charts display a trend over time so named ranges do not work in this situation.

I'm thinking it would be something like a remote control filter. Where the drop down box on the chart page would control what the division column filter selects on the data table page.
Thus the filter would change to reflect the proper division instantly based on the selection.

Is that possible?

Thanks!

Neo

I have a spreadsheet which contains 1972 rows of data. The spreadsheet contains filters so a user can select to see their specific data (ie, geographic sales region).

The problem is that creating a bar chart of 1972 rows makes it unreadable. So I decided to chart only 30 rows of data. However, now when a user selects their region, it only shows the static 30 rows...meaning it cuts off all data that is in rows 31 through 1972. I'd like the chart to show a dynamic range based on the filter up to a maximum of 30 rows.

I've been reading up on named ranges using offset...but I'm new to this and don't fully understand how to adjust that formula to do what I want. That only seems relevant if I'll be adding more data to my sheet?

Thanks for any help or suggestions!

Howdy All!

I've been working on a project in Access. It works fine, and exports tables to Excel. The number of records varies each time the Access file exports. Therefore, I've created a chart w/ dynamic ranges.

The chart works fine....It grows and shrinks accordingly. I'd like to include a line to show the average...dynamically. I found an equation that works w/ dynamic ranges. This function works well (found in cell C2).

However, I can't get that info turned into a line on my column chart. When I add that value into the chart, and change it to a line chart, I end up w/ one single data point. Understandably, because that's all there is.

Therein lies the question: How do I apply that value dynamically for each record so it shows as a line on my chart?

I've tried using trend lines, but they don't actually average...so they're not very helpful.

Attached is an example. Feel free to play w/ the data (add or remove as you will). The column chart changes accordingly, as does the average value in C2. But...I'd still like to get the average value to show as a line...

Thanks for the help.

In case of "excel challenged" users that sometimes Sort information wrong and then save the workbook wrong, can this feature be somehow turned off?

Can anybody help me,

I'm looking to disable the sort feature in a spreadsheet in Excel 2007.

I'm trying to write it into the VBA but can't work out how.

I've tried :

Application.CommandBars["Cell"].Command["S&ort"].Enabled = False

but this does not work, and this is pretty much the extent of my excel knowledge

Any help with this will be much appreciated.

In all I want to disable the sort function from the command bars and any other place it can be used from

Thanks in advance

I have a dynamically ranged, autofiltered list, and I am trying to figure out how to add a formula to the bottom of certain columns.

I want to add a subtotal to the bottoms of columns J, K, Q, R, T, U and X.

EXAMPLE: "SUBTOTAL(109,J:J)"

For other reasons, I cannot use the built-in Subtotal Feature in Excel 2007.

I know the code on how to find the first open row, but I cannot determine how to write the subtotal formula so it covers all the cells above each formula. I either get a circular reference, or it doesn't factor in all the cells above it.

If someone can at least show me the correct syntax for one of the columns, I can at least mimic it for the rest.

Thank you!

Is it possible to select multiple sheets and then apply the Sort tool to all the sheets selected? I just tried it myself but it didn't work. I selected several sheets and then highlighted a table I want sorted, but when I went to the pull down menus, "Sort" was grayed out, as was most of the tools under the "Data" menu.

I have the same table on every sheet (same location, same format/formulas, etc. The only thing that is different is the contents of each table. I want to be able to sort all the tables on every sheet simultaneously rather than have to go from sheet to sheet and applying the sort tool once on each sheet.

Hey guys,
I recently worked on an excel project for my old boss and created a working template only to learn that Office:Mac does not support the VBA macros i wrote...I cannot figure out to achieve my goal w/o using an advanced data filter command. This shees will be used multiple times per day, so i need to write a formula to accomplish the goals. as running the data filter manually four times per sheet is to cumbersome.

here's what im trying to do: the shop is creating cabinet doors, so there will be fifty or so doors in an order with a variety of lengths and widths. Im trying to write a sheet that will sum the lengths and width and order and sum them from longest to shortest for each part.

I've been able to sort the list longest to shortest, and sum the number of each lengths and width. but in doing so i have list that has longest to shortest widths with duplicate entries IE:
10,9,8,8,7,6,6,6,5. I used the advanced filter function to copy the list to another location and copy unique entries only IE 10,9,8,8,7,6,6,6,5 --> 10,9,8,7,6,5
any ideas on how i might complete this operation using a formula?


Second:

Im using a sum-match formula to sum the number of doors that have a particular length and width. What ends up happening is that i get a double counted list. in the above example i will get the sum of doors with a width of 8 twice and 6 three times. i was able to use and AND greater then adder to my forumla so that i would only get the sum for the first unique value in the list. Then i used the advanced filter with a condition of greater then zero and extracted only the "real" sums from the count list. Again, im stumped as to how i might accomplish this w/o the advanced filter feature.

its a really long post, my apologies!

complete example

5 doors, three are a unique size.

quantity 2 10X10
quantity 1 8X10
quantity 1 8X8
quantity 1 6X8

number length width
2 10 10
1 8 10
1 8 8
1 6 8


how to list lengths and widths from largest to shortest and total each unique length and width....

im currently using the LARGE function to order the pieces longest to shortest (this creates a list with duplicate entries as 10 is the first and second largest width in the list)

and how to sum each unique w/o double counting or having a list with zeros....


for those of you who made it to the bottem of this post: THANKS for sticking with me!