Do I need A Macro?

[mosiki]

Hi<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Just wondering if there is an array function on excel for the below problem or will I need to construct a macro.<o></o>
<o></o>
I have a work book with 22 worksheets. Worksheet 2 is the summary worksheet which summarizes data from worksheets 3 - 22. worksheet 1 is where i do my analysis, pulling info from the summary sheet. Only certain data is pulled from the summary sheet at any one time.<o></o>
<o></o>
Sheets 3-22 are named Arsenal, Aston Villa, ....Wigan Athletic - i.e. Premier League teams. Each of these contains results, opponents, goals etc relating to that team, which is fed into the summary sheet. Each worksheet is named after that team. Eg. The Arsenal data is contained in a worksheet named 'Arsenal!', and so on for each team.<o></o>
<o></o>
On my analysis sheet I have a data validation list where i click on which teams' info i want to compare. If i click on <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1:City w:st="on">Chelsea</st1:City> for example, i have a lot of lookup functions that pull <st1:City w:st="on"><st1lace w:st="on">Chelsea</st1lace></st1:City>'s info (results, goals, etc) from the summary sheet. If i change to arsenal, then the lookups will pull in arsenals data automatically from the summary sheet. The data validation list has the same name as each worksheet. Eg. <st1:City w:st="on">Chelsea</st1:City> is an option on the data validation list, there is also a worksheet named <st1:City w:st="on"><st1lace w:st="on">Chelsea</st1lace></st1:City>. This is the same for all 20 teams.<o></o>
<o></o>
<o></o>
The above works fine, but here is where it gets slightly more complicated. There is data specific to each team that cant be put into the summary sheet due to the long process it would involve. Each of these data sets are contained within the worksheet relating to that specific team. Eg. When I click on <st1:City w:st="on"><st1lace w:st="on">Chelsea</st1lace></st1:City> on my analysis sheet it pulls in the normal info from the summary sheet via lookups. I also want to pull in a certain range from the worksheet named '<st1:City w:st="on"><st1lace w:st="on">Chelsea</st1lace></st1:City>!', lets say A1:D10. If i changed my selection on the analysis sheet to Arsenal I would then need this to pull in the same range of data A1:A10 from the sheet named 'Arsenal!' as it did when <st1:City w:st="on"><st1lace w:st="on">Chelsea</st1lace></st1:City> was selected. This will also be conditional if certain criteria is met. Eg. If S1 (on analysis sheet) = "Yes" then go to worksheet('<st1:City w:st="on"><st1lace w:st="on">Chelsea</st1lace></st1:City>!').Range(A1:A10).select.copy go to analysis sheet paste special values in a selected range. if S1 = "no" then leave cells blank in selected range on analysis sheet.<o></o>
<o></o>
Is there a function that can use the name of my data validation selection as the sheet name that i want to look in to. Eg. if I select <st1:City w:st="on">Chelsea</st1:City> on my list in cell T2 (data validation list), can i have a function to look at T2 and then go to the worksheet named <st1:City w:st="on"><st1lace w:st="on">Chelsea</st1lace></st1:City> and pull in the data i need? If I change my list selection to Arsenal, the function will then go to the sheet named Arsenal and pull in the same data range but in this different worksheet ?<o></o>
<o></o>
<o></o>
If this makes no sense just ignore. I just bought a book on macros so will figure something out. If you have any ideas to speed me along that would be great.<o></o>
<o></o>
Cheers<o></o>
<o></o>
Mosiki.<o></o>
<o></o>

 

[shyrath]

You can use an onchange event on the validation selection list.

So if your sheetnames are identical to the validationlist (or use a vlookup in another cell) then you could use this below.

assuming the selection list is in C2, if you change the selection then the macro would look at the value, then jump to that sheet, get the range you specify and paste it back into the summary sheet.

(put in the worksheetmodule of the sheet you want it on).

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

             If Target.Address = "$C$2" Then

        Sheets(Target.Value).Range("B3:E8").Copy Destination:=Range("C10")
    
            End If
        
    End If

Application.EnableEvents = True

End Sub

 

[pgc01]

Hi Mosiki
Welcome to the board

You can use formulas to display (or not) the specific data.

If I understood correctly,

- in T2 you have a data validation list where you pick the sheet name
- in S1 you write "Yes" when you want to display the specific data

To do it with formulas:

- in each worksheet define a name "OutData" referring to the data you want to appear in the analysis sheet

- you didn't write where in the analysis sheet you want the specific data to appear.

Assuming the specific data appears in a range starting in A20, in A20:

=IF($S$1<>"Yes","",IF((ROWS($A$20:A20)>ROWS(INDIRECT("'"&$T$2&"'!OutData")))+(COLUMNS($A$20:A20)>COLUMNS(INDIRECT("'"&$T$2&"'!OutData"))),"",INDEX(INDIRECT("'"&$T$2&"'!OutData"),ROWS($A$20:A20),COLUMNS($A$20:A20))))

Copy down and accross the maximum number of rows and columns of the all the specific data ranges

HTH

 

[mosiki]

Thanks a lot guys, try these out when I get out of work, let ya's knoe how I get on .

 

[mosiki]

pg

Can I define different ranges with the same name within the same workbook ? I would have 20 ranges called outdata on 20 different worksheets, or am I getting this wrong? I'm messing around on this with excel 2003 and it doesnt seem to allow this. I will be doing it for real on excel 2007 though.

So far it works for the first sheet but doesnt update when i change selection on data validation list due to outdata name tag only being deployed to one worksheet.

 

[pgc01]

Hi again

There are 2 possible scopes for a name, Workbook or Worksheet.

A workbook name can be used in any worksheet and will always have the same value. A worksheet name is specific to the worksheet. If you try to use it in another worksheet it will not be recognized unless you preceed it with the worksheet name, like a range.

It seems you are using a workbook name, instead of worksheet names.

To define the workbook name Outdata, for example for sheet1!A1

Name: Outdata
Refers to: =Sheet1!$A$1

That's not what we want. Delete it

To define the worksheet name Outdata, specific to the worksheet Sheet1, for example referring to sheet1!A1:

Name: Sheet1!Outdata
Refers to: =Sheet1!$A$1

In the solution I posted each worksheet has its own name with its specific data. This way when you select the worksheet in your validation list, the formula will address the corresponding data.

 

[pgc01]

I will be doing it for real on excel 2007 though.

When you try to define the name in excel 2007 you'll notice, in the define name form, a new dropdown where you can choose directly the scope of the name. Although the interface is simplified, the functionality is the same as in excel 2003.

 

[mosiki]

Got it, works a treat, thanks a lot.

much appreciated.