Posts by nb-

    Re: Pivot Table - Percentage Of Data Of Type

    Thanks, I was being very silly. I will have a play, looks as though I am going to have to use several pivot tables to get what I want, which is Male/Female as a percentage of A or B in cat1 and both A&B.

    I have a pivot table based on data in the form (simplified);

    Year Cat 1 Sex
    1 a F
    1 A M
    1 B U
    1 B F
    2 A M
    2 A M
    ETC. ETC. ETC.

    So in the pivot table we have year 1 in the first row year 2 in the second etc. cat 1 as the column headings.

    What i want to do is get the data item area of the pivot table to display the percentage male female etc by year, something like =(Count of F in Sex)/(Count of Sex), how can I do this?

    So year 1 would be 50% Female, 25% Male, 25% Undefined.

    Re: Loop Through Variables

    I am trying to get variables passed to a function from sheets throughout a workbook into an array without having to write out a page of if ismissings as i dont know how many there will be.

    Is it possible to pass these as an array?

    This has sort of been asked before

    but is there a way to loop through a series of variables like var0...var9? You can obviously loop through and make a set of strings with the right names,

    Dim I As Integer
    Dim Var As String
    For I = 1 To 5
    Var = "Var" & I
    Next I

    Is there a way to use the contents of a string variable to call a variable with the same name as contents of the string? or somehow concatenate the a string with a number like

    How do I select different sheets from within a function?

    I have a function that takes the parameter of a cell, and then looks up and down from that cell, and returns a value based on those values. This works fine until I select a cell on a different sheet from the cell the function is written in.

    In outline it is as below;


    Re: Getting The Cell Index For A Function Not The Cells Value

    Thanks Andy, but all I can get out of that is the cell the formula is in, I dont know how to set a cell passed to the function as the active cell to use activecell.xxxx which would be one way of doing it if its possible.

    What I want to do is have in an Excel cell;

    =testfunction(B6, X, Y)

    And be able to drag it down a column, across a row etc, and have B6 change accordingly. But what I want is the row and column index of the cell as i would get in Excel by typing


    as the result depends on the values X cells above it and Y cells below it. See example below;

    I am trying to get the cell index from a cell reference passed to a function and I have no idea how.

    eg. In excel


    What I want is to get is the row and column index for cell D4, not whatever is in cell D4, so I can just click the cell in excel, and drag the formula about etc.