Excel Tips - Calculating proportions with COUNTIF

Hi everyone

I've got a PivotTable which counts information that is split down into years and categories. I'd like to work out the proportion of the categories for each year and am unable to do this.

I've edited the field and changed the 'Show data as:' to '% of total'. However, this shows the splits as a proportion of all the data. What I'd ideally like is a '% of sub-total', but such a thing doesn't seem to exist.

Does anyone know how I can do this? I'm running Excel2000.

Any help much appreciated, kind regards

Nick

I am trying to do something like:

Code:

dim my_array(x, y) as variant

but I do not see a way to work around this.

Currently I have a solution which is to simply declare the array with constant values way larger than the array will ever need to be. This seems to be inefficient. Is there a better way to declare an array in which I cannot predict it's dimensions?

I run a fantasy league & use excel for the weekly results

Because it has Year to Date information I have to add to the document on certain tabs so I copy n paste the file from one folder to another from week to week.

I've done this in the past & haven't had any problems, but now I have added some formulas (index/match & some other sum formulas) & it has grown from 144k to 1500k to recently 34mb.

I have excel 2000. I know that this isnt probably enough information but I thought I'd throw it out to get some feedback & answer any questons that you might have to figure it out.

Hey everyone,

I am trying to paste a logo into a protected worksheet, and make it a certain height (the width is not important, but it needs to be to scale). The problem i have is that the logo will change (i am making a template), and the size each client logo is different. I have managed to get the picture pasted in using this code (which i found on some other help site):

Sub Insert_Pict()
Dim Pict
Dim ImgFileFormat As String
Dim PictCell As Range
Dim Ans As Integer
ActiveSheet.Unprotect
ImgFileFormat = "Image Files (*.bmp),others, tif (*.tif),*.tif, jpg (*.jpg),*.jpg"
GetPict:
Pict = Application.GetOpenFilename(ImgFileFormat)
If Pict = False Then End
Ans = MsgBox("Open : " & Pict, vbYesNo, "Insert Picture")
If Ans = vbNo Then GoTo GetPict
GetCell:
Set PictCell = Application.InputBox("B3", Type:=8)
If PictCell.Count > 1 Then MsgBox "Select ONE cell only": GoTo GetCell
PictCell.Select
ActiveSheet.Pictures.Insert(Pict).Select
ActiveSheet.Protect
End Sub

My problem is i can only find code to scale the height and width of the logo using ScaleHeight and ScaleWidth, which is not what i want. I want to set the height of the logo, and keep the proportations.

Can somebody help?

Maybe you guys could take a break from giving free technical school
answers to lazy partiers and try this fun problem. I dreamed part of
it last nicht.

How do I transpose an array of words and numbers into an array of
different proportions?

The proportions of the arrays can be anything, including n x 1 and 1 x
n.
The size of the array can be anything that fits on a worksheet and can
be located anywhere on the worksheet.

Ex:
Array of the present (4x5)
ab 12 de 1.55 ghe
ef 34 nu 3.65 unt
gh 56 mc 2.45 wen
kl 78 vm 1.35 rep

After a re-arrange (7x3)
ab 12 de 1.55 ghe ef 34
nu 3.65 unt gh 56 mc 2.45
wen kl 78 vm 1.35 rep

I am uncomfortable with VBA so don't use VBA.

I don't want to copy/paste because I want the second array to update
immediately when I change the values in the first array.

Also another reason I don't want to use VBA is I would necessitate to
run it after every update.

Thank you and have a good evening,
Manfred Straub
(originally from east Zurich)

Hey everyone,

I am trying to paste a logo into a protected worksheet, and make it a certain height. The problem I have is that the logo will change (I am making a template), and the size each client logo is different. I have managed to get the picture pasted in using this code (which I found on some other help site):
Code:

Sub Insert_Pict()
Dim Pict
Dim ImgFileFormat As String
Dim PictCell As Range
Dim Ans As Integer
ActiveSheet.Unprotect
ImgFileFormat = "Image Files (*.bmp),others, tif (*.tif),*.tif, jpg (*.jpg),*.jpg"
GetPict:
Pict = Application.GetOpenFilename(ImgFileFormat)
If Pict = False Then End
Ans = MsgBox("Open : " & Pict, vbYesNo, "Insert Picture")
If Ans = vbNo Then GoTo GetPict

ActiveSheet.Pictures.Insert(Pict).Select
ActiveSheet.Protect
End Sub

The picture must fit into a certain height, so the height of the picture is the determining factor for the picture size. I want to be able to resize any picture (ie any random height and width) to a specified, constant height and have the picture remain in the same proportions (ie have the code automatically resize the width).

I would like to set the height of the picture, and have the width automatically adjusted to scale.

I cant use the functions ScaleHeight and ScaleWidth because this requires specifying a certain width as well as height, which is not suitable since my pictures can be of any random proportion.

If i could use the ScaleHeight to set the height of the picture, then another function to determine the width required to keep the picture in proportion, then I think that could work.

Can somebody help?

What is the easy way to create forms in different sheets.......

In Main Sheet (Sheet1) each row contains some informations about a particular item.
I want to create Forms in separate Sheets and datas for this Form 1 (Sheet2) should be datas of Row#1 of Sheet1 & datas for this Form 2 (Sheet2) should be datas of Row#2 of the Sheet1.......

Hi Guys,
Does anyone know of a web site which offers Excel tips and tricks.
Is there a free book which I can download that will give me tips and tricks.
Thanking you in advance,
Regards

J-O

Hy guys,

I still work with excel 2007, so, can someone tell me please if the 2010 version shows the formula tips, when we are tipping a formula in the "name manager box", and in the "conditional formating box".(when you use a formula to do conditional formating), like they are shown when we tipe a formula in a spreadsheet cell??
I think that would be a great help

Hello. I recorded the code below to convert pages of workbook to pdf. It works well on my computer but on a co-worker's computer the pdf produced has header and footer pictures shifted slightly down and to the right, showing small areas of white area on his pdf and I do not see this on my pdf produced.

-Any idea what might cause this situation?
-If I have pictures in the header and footer and the pictures are inserted from my desktop,
does this cause Excel issues with those headers/footers on another computer? Is a better
way to load and link the pictures somewhere in the Excel document?
-Is Excel not the best tool to convert to pdf? Would it be better to use Publisher
or even Powerpoint linked to Excel sheets? How about one of the many Excel to
PDF softwares out there?

Thank you.

Sub convpdf()
Sheets(Array("sheet1", "sheet2", "sheet3", "sheet4", "sheet5")).Select
Sheets("sheet1").Activate
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Documents and Settings\myname\Desktop\pdf_file.pdf", Quality:= _
xlQualityHigh, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=True
End Sub