Excel Magic Trick 304: Charts: Add Secondary Axis To Frequency Column Chart

I am following this tutorial from youtube. It is a search function to search for a specefic word in an entire row. Here is the first part of the code:

Code:

=SEARCH(C$3,Table2[[#This Row],[DESCRIPTION]])

except i only get a value in the cell that i typed this in, instead of it searching the rest of the row like in the tutorial. i can't figure out how to fix this. anyone know what im doing wrong?

Good morning,

trying to find out what to do with my spreadsheet and the formulas in it.

If you look into the file 369. I can get to the stage where it outlines the mismatch figure. In the example it is 6 Mismatches.

But I cannot seem to get the table belwo right. Everytime I try and copy past the formula, the first set of data is the one from the first line and then I get a #NUM error, due to the fact that there seems to be a number reoccurence somewhere.

I took the youtube tutorial, but even copying that formula did not work.

My table consist of 2 sets of 600 values each.

Can someone tell me why I am getting this error and help me resolve the issue that the result is an existing set of data?

THANK YOU

BayerStars23
[IMG]file:///C:/Users/BINGLE%7E1/AppData/Local/Temp/moz-screenshot.png[/IMG][IMG]file:///C:/Users/BINGLE%7E1/AppData/Local/Temp/moz-screenshot-1.png[/IMG]

Hi All,

Thank you in advance for viewing this thread!

I have created a macro that will plot a line graph based upon 5 series. This works well, however, I would also like to add a vertical line that is based upon a value in the primary X-axis.
For example, the X values may range from 50 to 100 and I want to place a vertical line on "65". (The ranges will change each time the spreadsheet is used and will be extremely volatile and thus could be well beyond this example range).
I am able to create one manually with an XY scatterplot using a secondary axis, but then the secondary X-axis has a different min and max than the primary X-axis, and I don't want to have to manually change the min and max ranges each time I run the spreadsheet. And if I set the min and max to "auto" then the secondary is still different than the primary.
I'm also familiar with the option of changing the primary chart type to an XY scatter and then adding the secondary XY to it and thus the x-axes are the same. However, with this option my primary X-axis range doesn't format correctly. I can't have it "fixed" because the range will change with each spreadsheet use (and I don't want the user to have to manually change it) and if it is set to "auto" it usually starts at "1" and then goes beyond the range data. And I am wanting to chart only the given range and nothing more.
Your thoughts are sincerely appreciated and welcome. Thank you!

Is it possible to make a 3D column chart where one series is plotted on a secondary axis?

Ideally, I would like to use the 3D column column subchart type (bottom left hand column of the chart type selector). This chart type allows comparison across categories and series. Alternatively, a clustered 3D chart type would also work (middle left hand column of chart type selector).

I can get a flat 2D clustered chart to plot series2 on a secondary axis. But when I try doing the same thing on either of the 3D options, when formatting series2, the axis tab is missing, so I can't specify the secondary axis.

I've attached my example of what I've tried.

Any ideas or fixes?

Thanks, cat

Hello there.
First, and before all, I must admit that I am a kind a "dumb" for Excel, because I always were using it, only for better look's of simple tables, schedule's and similar. Although, I was all the time aware of great power's of this program, but newer tested it.
So, finally after few years of tempting, I decided to make a proper "order-list" for the shop in which I work. What I needed is to ,based on products and their prices, make a drop-down box, with list of the products, and after choosing one , proper price will be shown in next column. I find out how to do it with "Excel magic trick #5" on Youtube.
This is what i achieved:

http://i49.tinypic.com/dorzgn.jpg

However,
You see that in row 16, or line 2 in the order box ,we have #N/A below Cena (Price), Iznos PDV (amount of VAT), Cena sa PDV (Amount with VAT), and Iznos (Total amount). If we choose one of the product from drop-down box (now, as You can see none is chosen) and type number in Kolicina(Quantity) column, all this would be automatically changed into proper value.
But what if we have only one thing in order box, instead of 2 or all of 10 ? How to avoid those #N/A, and make instead to be blank cell, which is to be counted as 0, because of final calculation?

In this order-form we have only line 1 and 2 programed to have drop-down box in line Naziv robe (Name of the product). If I putted same drop-down boxes in rest 8 columns, #N/A would be shown in all programed cells bellow, like in line 2...

Kind regards from Serbia

Hello,

I am constructing a make-shift database in excel but can't seem to find a way to get magic trick #213 to work.

What I am trying to do is create a dynamic filter via 16 criteria. I would like any results that match all criteria that I specify and then displays results in a separate table to the right.

Thank you so much for your help - I would attach my spreadsheet to this message if I knew how.

BR,

PR

I am creating a graph which i wish to be a stacked column chart comprising of 3 drugs.

The category's along the x axis include 20 GP surgeries and national figures. Obviously national figures are hugely bigger than the GPs. I therefore wish this category to be on a secondary Y axis.

I know how to create a secondary axis for a series, but not for one category. Is this possible?

any help much appreciated

john

I'm trying to create a stacked column chart, but with column D requests on the secondary axis.

I used a tutorial on-line to create a stacked column chart with the total displayed above the bar, but it seems in 2007 I can't get even this to display the data label above the bar.

http://www.ozgrid.com/Excel/stacked-column.htm

What am I missing here?

Looking for a little help tweaking Excel Magic Trick 185. I've reached what is probably just a mental block.

I'm trying to adjust a spreadsheet for work using the processes described in Excel MagicTrick 185. (Great series, by the way...) The catch is that the source data table can not be modified under any circumstances (we've already asked).

The problem is different than EMT_185 in that there are 9 columns that can potentially define a match as opposed to just the one shown in EMT_185. There are two user options ('I-III' and 'A-C') which in combination establish the headings for the 9 possible conditions (I-A, II-A, III-A ...) .

(See the linked spreadsheet) I've been able to:
determine if a row in the source matches user provided conditions (col Y),
get the count of matched source rows (row 26),
determine the match instance (col AA), and
place the source data in the output table (col AC)

What I can't quite get my mind around is suppressing the blanks in the output (i.e.:getting match 1 on row 1, match 2 on row 2, etc.)

What am I missing? Please help. A spreadsheet showing what I have so far is located on google docs. Perhaps you could make a new EMT out of it!

BTW.. this used to be done via vba, however that is no longer an option (don't ask... It just isn't, and it's not a topic that is open to discussion -- we tried).

http://www.youtube.com/watch?v=tqCEY5YMyqw

I am trying to duplicate the concept on my spreadsheet. It is conceptually the same, the formula is slightly different.

Mine is as follows:
{=IF(ROWS(A$5:A5)>$B$2,"",
INDEX(US!A$5:A$72,
SMALL(IF(US!$AU$5:$AU$72=Sheet1!$A$2,ROW(US!$AU$5:$AU$72)-ROW(US!$AU$5)+1),ROWS(A$5:A5))))}

Resulting in a #VALUE! error. Can anybody advise me what should the formula me?

Thank a million!
Long