YTLE#76: Data Analysis & INDIRECT function

Hi

I have a model in excel which takes a large amount of data and runs it through the model to produce two output numbers. I then have to remove groups of the data while keeping the rest in and pull out the results, then put that group back in and remove the next group and so on.

I am trying to find a way to make this be done automatically so other people can use the model easily. I have seen that somehting similar to this can be done by creating scenarios and then creating a summarry pivot table. However, this pivot table will not update when a new set of data is used and therefore other users would not be able to change the data and still use the table.

Is there a way to make this table update or achieve the same results using a different method?

Thanks

Hi,

I am an analyst, I am using Pivot table, Filters & cond. formatting for data analysis in Excel . I would like to know that, What are the other feature/add-ins/Plug-in exist in excel which is useful for data analysis. Please Help. Thanks in advance.

Hi,

I am an analyst, I am using Pivot table, Filters & cond. formatting for data analysis in Excel . I would like to know that, What are the other feature/add-ins/Plug-in exist in excel which is useful for data analysis. Please Help.

Hi,

I have a problem with a spreadsheet (attached). The problem (for me) is quite complex and any suggestions would be very welcome. My spreadsheet has three sheets - Costings, Materials, and Validations. In the Costings sheet I have three columns - A, B, C. I am trying to create a complicated dependent list where Column A determines the list in column B, and Column B determine the list in Column C. I have got as far as getting column A to determine the choices in column B using the INDIRECT function with the data ranges being named as the data validation cells for column A:

=INDIRECT(SUBSTITUTE(A3," ",""))

However column C is more difficult as different values in A and B must dictate the list in C. For example if the value in A contains "Offshore Plate" AND "Carbon Steel" the validation list in column C must be the range listed under "OffshorePlate" in the validations sheet. Again this works using the INDIRECT function by combining the values of columns A and B and removing the spaces.

=INDIRECT(SUBSTITUTE(A2&B2," ",""))

However if column A is "SAW Tubular" then Column C will be pointing towards two different validation ranges depending on what is selected in column B. What I want the sheet to do is select the range "Alloysawt", if column B contains anything but "carbon steel" or "carbon steel EU stock". If column B does contain "carbon steel" then I want column C to display the data validation range "Sawt".

Also if column A contains "Structural Hollow Section" I want column C to look up the value in B and then display the data range "Struchs" if any of the lookup values match the contents of the cell in column B. I guess this is another case where 2 conditions need to be met in order to display the correct validation list in column C.

Is this possible? if so could someone please help! I am struggling with this!

Hi,

I am trying to recall a named range in my data validation as follows:

In Sheet1 I have named range = category. Formulae is: offset=OFFSET(A3,1,0,5,1) and the scope is "workbook".

In a separate worksheet (Sheet 2) I have data validation = indirect(F3) where F3 = category.

However, excel pops up "The Source currently evaluates to an error. Do you want to continue?" I know the offset formulae is correct because if I set data validation to reference "=category" it works.

I believe it's a scope issue because when I click on the name drop down on the top left hand corner of Sheet 2, the named range "category" doesn't actually appear. However, when I go to the name manager the scope is definitely "workbook".

Can anyone help? Thanks!

Hi,

i know it is possible for a selection in a drop down box to determine another field using the INDIRECT function in validation

eg - 1st drop down box - Football, Rugby, Cricket
2nd drop down box (if chosen football) - displays list of football teams
2nd drop down box (if chosen rugby) - displays list of rugbyteams
2nd drop down box (if chosen cricket) - displays list of cricket teams

but is it possible for the 1st drop down box to determine what is available in a range of other drop downs?

eg - 1st drop down box - Football, Rugby, Cricket
2nd drop down box (if chosen football) - displays list of football teams
3rd drop down box (if chosen Man Utd) - displays list of Man Utd players

thanks in advance
Rich

Hi

I have a problem regarding using lists with the indirect function. I have a series of class names and each has a named range associated.

Each class has a secondary named range that is used for including or excluding names and that removes each name as it is used.

I am trying to use Indirect so that by entering the class number the relevant list appears -

However the function will not work in the sheet I am working on, I have tried it in another sheet and everything is fine, so the problem is in the workbook, unfortunately it is a rather large workbook as well.

I was wondering if anyone else had come across something similar.

Thanks in advance

Regards


Jeff

Hello,

I have installed the Analysis ToolPak successfully (and have used the Data
Analysis tools many times before), but sometimes the Data Analysis option
simply fails to appear in the menu. What could be causing this?

Thanks,
Eric

I'm trying to use a combo box with my data validation. It works great when I'm not using the the INDIRECT function for the validation. But I can't get it to work with it.

I downloaded the code from the internet.

What it does when i don't use the INDIRECT function: I click on a cell that has a Data Validation assigned to it, it activates the combo box to be visible by just clicking on the cell one time, and it auto fills when i start typing. And when I click on the drop down, it list all my options that I can select.

What it does when i use the INDIRECT function: I click on a cell that has a Data Validation assigned to it, it activates the combo box to be visible by just clicking on the cell one time, but it does not auto fill when i start typing. And when I click on the drop down, it does not list any options.

I was wondering if i can add/change something to the code to make it work?

I am also wondering if I can make the combo box expand when i click on the cell?

This is the code:

VB:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)    Dim str As String 
    Dim cboTemp As OLEObject 
    Dim ws As Worksheet 
    Dim wsList As Worksheet 
    Set ws = ActiveSheet 
    Set wsList = Sheets("Order Form") 
     
    Cancel = True 
    Set cboTemp = ws.OLEObjects("FenceSelection") 
    On Error Resume Next 
    With cboTemp 
        .ListFillRange = "" 
        .LinkedCell = "" 
        .Visible = False 
    End With 
    On Error Goto errHandler 
    If Target.Validation.Type = 3 Then 
        Application.EnableEvents = False 
        str = Target.Validation.Formula1 
        str = Right(str, Len(str) - 1) 
        With cboTemp 
            .Visible = True 
            .Left = Target.Left 
            .Top = Target.Top 
            .Width = Target.Width + 3 
            .Height = Target.Height + 3 
            .ListFillRange = str 
            .LinkedCell = Target.Address 
        End With 
        cboTemp.Activate 
    End If 
     
errHandler: 
    Application.EnableEvents = True 
    Exit Sub 
End Sub 
Private Sub Worksheet(ByVal Target As Range) 
    Dim str As String 
    Dim cboTemp As OLEObject 
    Dim ws As Worksheet 
    Set ws = ActiveSheet 
    Application.EnableEvents = False 
    Application.ScreenUpdating = True 
     
    Set cboTemp = ws.OLEObjects("FenceSelection") 
    On Error Resume Next 
    With cboTemp 
        .Top = 10 
        .Left = 10 
        .Width = 0 
        .ListFillRange = "" 
        .LinkedCell = "" 
        .Visible = False 
        .Value = "" 
    End With 
     
errHandler: 
    Application.EnableEvents = True 
    Exit Sub 
End Sub 
 
 
Private Sub FenceSelection_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) 
    Select Case KeyCode 
    Case 9 
        ActiveCell.Offset(0, 1).Activate 
    Case 13 
        ActiveCell.Offset(1, 0).Activate 
    Case Else 
    End Select 
End Sub 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines