Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Best Way to Pull Data from Other Sheets
I have 20 sheets of data that are formatted similarly. On the first sheet,
I want the user to select one of the 20 sheets and have Excel pull in a range of data from that sheet. What is the best way to allow for user input to select the sheet? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Best Way to Pull Data from Other Sheets
Hi,
You can use a lot of these types of formulas: =INDIRECT("'"&A1&"'!A9") Where the sheet name is in A1 and the data you are pulling in is in A9. This technique will only work if all spreadsheet are identical. Otherwise you will need to consider VBA programming. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "opieandy" wrote: I have 20 sheets of data that are formatted similarly. On the first sheet, I want the user to select one of the 20 sheets and have Excel pull in a range of data from that sheet. What is the best way to allow for user input to select the sheet? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Best Way to Pull Data from Other Sheets
I just knocked this up with three sheets; two with tables
Sheet1 I called query I created range names on the other sheets and called them Table1, Table2 ... The tables are dynamic so each table may have a different number of rows. Table1 refers to =OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A)-1,4) in the Insert Name form, each table needs a name. Mine is a four column table. and the headers are ignored (not copied) if you want the headers remove the -1 after the Counta function I created a list of the named ranges and used this for data validation in Cell F1 in sheet1 (the sheet to copy to) Select you table name in F1 then run the macro. The code is copied into a VB Module. ALT + F11, Insert Module Paste the code and return to the sheet Sub CopyTable() Dim tbl As Range Dim cell As Range Dim LastRow As Long Dim LastCol As Integer ' Clear the last details LastRow = Range("A1").CurrentRegion.Rows.Count LastCol = Range("A1").CurrentRegion.Columns.Count Range("A2:" & Cells(LastRow, LastCol).Address).ClearContents Set cell = Range("F1") Select Case cell Case Is = "Table1" Set tbl = Range("Table1") tbl.Copy Range("A2") Case Is = "Table2" Set tbl = Range("Table2") tbl.Copy Range("A2") End Select End Sub You will need to have a 'Case Is = ' for each table and the code will need to be modified. When your are happy you can use a command button torun the code. View, Toolbars, Forms to show the toolbar. click the 4th icon (Button) and draw this in the sheet; you will be promted to assign a macro - choose CopyTable. Change the button text to something informative. To run change the text in F1 and click the button. If this helps remember to click yes! Regards Peter "opieandy" wrote: I have 20 sheets of data that are formatted similarly. On the first sheet, I want the user to select one of the 20 sheets and have Excel pull in a range of data from that sheet. What is the best way to allow for user input to select the sheet? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Best Way to Pull Data from Other Sheets
Shane thats why I went for the vb option therer was not a lot of info to go on.
"Shane Devenshire" wrote: Hi, You can use a lot of these types of formulas: =INDIRECT("'"&A1&"'!A9") Where the sheet name is in A1 and the data you are pulling in is in A9. This technique will only work if all spreadsheet are identical. Otherwise you will need to consider VBA programming. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "opieandy" wrote: I have 20 sheets of data that are formatted similarly. On the first sheet, I want the user to select one of the 20 sheets and have Excel pull in a range of data from that sheet. What is the best way to allow for user input to select the sheet? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Best Way to Pull Data from Other Sheets
I'm going to give a more specific example here.
I have 20 sheets that all have a standard set of columns and rows. Each sheet has the statistics of a baseball team in a standard format (Games, At-Bats, Hits, etc., all exactly the same columns on each sheet.) On the main sheet preceding these 20 sheets, I want the user to select the desired team from a list and have the main sheet pull in the stats from the applicable team's stat sheet. I have designed this in VBA using option buttons. The problem is that for each button, I have to have a set of code that is exactly the same, with the only difference being the sheet name that is being referred to. This is inefficient because when I identify a change I want to make to the code, I have to make it to all 20 code sections. Further, I am expanding this file to include hundreds of sheets and don't to copy and paste code every time, or have to change hundreds of sections for a single common edit I make to the code. I would like to have a single code section, with the sheet name as a variable the user selects (from a drop-down list, set of buttons, whatever). Let's say it's a drop-down list. The user selects "NY Yankees" from the drop-down list. The VBA code goes to the NY Yankees sheet and pulls in a range of data into the main user sheet. How do I get VBA to allow for user input of a sheet so I can have 1 set of code and variable sheet names that code can pull data from? Thanks! Chris "Billy Liddel" wrote: I just knocked this up with three sheets; two with tables Sheet1 I called query I created range names on the other sheets and called them Table1, Table2 ... The tables are dynamic so each table may have a different number of rows. Table1 refers to =OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A)-1,4) in the Insert Name form, each table needs a name. Mine is a four column table. and the headers are ignored (not copied) if you want the headers remove the -1 after the Counta function I created a list of the named ranges and used this for data validation in Cell F1 in sheet1 (the sheet to copy to) Select you table name in F1 then run the macro. The code is copied into a VB Module. ALT + F11, Insert Module Paste the code and return to the sheet Sub CopyTable() Dim tbl As Range Dim cell As Range Dim LastRow As Long Dim LastCol As Integer ' Clear the last details LastRow = Range("A1").CurrentRegion.Rows.Count LastCol = Range("A1").CurrentRegion.Columns.Count Range("A2:" & Cells(LastRow, LastCol).Address).ClearContents Set cell = Range("F1") Select Case cell Case Is = "Table1" Set tbl = Range("Table1") tbl.Copy Range("A2") Case Is = "Table2" Set tbl = Range("Table2") tbl.Copy Range("A2") End Select End Sub You will need to have a 'Case Is = ' for each table and the code will need to be modified. When your are happy you can use a command button torun the code. View, Toolbars, Forms to show the toolbar. click the 4th icon (Button) and draw this in the sheet; you will be promted to assign a macro - choose CopyTable. Change the button text to something informative. To run change the text in F1 and click the button. If this helps remember to click yes! Regards Peter "opieandy" wrote: I have 20 sheets of data that are formatted similarly. On the first sheet, I want the user to select one of the 20 sheets and have Excel pull in a range of data from that sheet. What is the best way to allow for user input to select the sheet? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Best Way to Pull Data from Other Sheets
Chris
Sorry for the delay. I'm sticking with the Data Validation but have changed the code. If you intend to increase the number of sheets over time you need a macro to update your list of sheets. This can be done on a hidden sheet. I placed mine behind the MAIN sheet so its index is 2. I have assumed that the sheet will be named after its team so the user will recognize the team. Place this code in a general module Sub ListSheetNames() ' Create on 2nd sheet then hide sheet Dim i As Integer Dim row As Integer If Worksheets(2).Visible = False Then Worksheets(2).Visible = True End If Worksheets(2).Select For i = 3 To Sheets.Count Cells(i - 1, 1) = Worksheets(i).Name 'Places list in column j change to suit Next i Worksheets(2).Visible = False Sheets("Main").Select End Sub You will need a dynamic range for your list so in the Insert name tabs create a range name Teams and in the Refers to text box place this formula =OFFSET(TeamsList!$A$2,0,0,COUNTA(TeamsList!$A:$A)-1) and click the add box. If you place the cursor in the formula the range is selected, if it look OK close the box. (you will have to unhide the sheet to see it) The code for to copy the list goes in the same general module Sub PullData() Dim wks As Worksheet, Table As Range Dim cell As String cell = Range("L1") 'Change to suit With Sheets(cell) Set Table = .Range("A1:K50") 'Change range to suit End With Range("A1:K50").Value = Table.Value End Sub I set data validation in L1 In the Allow box choose list and in the Source Type =Teams The folling code is a Sheet Change event to call ranges and works when the cell L1 changes. Right click the MAIN sheet and select View code and copy the following code into the sheet module. Private Sub Worksheet_Change(ByVal Target As Range) Set Target = Range("L1") PullData End Sub You may have to change the ranges HTH Please click Yes if this helps. Peter "opieandy" wrote: I'm going to give a more specific example here. I have 20 sheets that all have a standard set of columns and rows. Each sheet has the statistics of a baseball team in a standard format (Games, At-Bats, Hits, etc., all exactly the same columns on each sheet.) On the main sheet preceding these 20 sheets, I want the user to select the desired team from a list and have the main sheet pull in the stats from the applicable team's stat sheet. I have designed this in VBA using option buttons. The problem is that for each button, I have to have a set of code that is exactly the same, with the only difference being the sheet name that is being referred to. This is inefficient because when I identify a change I want to make to the code, I have to make it to all 20 code sections. Further, I am expanding this file to include hundreds of sheets and don't to copy and paste code every time, or have to change hundreds of sections for a single common edit I make to the code. I would like to have a single code section, with the sheet name as a variable the user selects (from a drop-down list, set of buttons, whatever). Let's say it's a drop-down list. The user selects "NY Yankees" from the drop-down list. The VBA code goes to the NY Yankees sheet and pulls in a range of data into the main user sheet. How do I get VBA to allow for user input of a sheet so I can have 1 set of code and variable sheet names that code can pull data from? Thanks! Chris "Billy Liddel" wrote: I just knocked this up with three sheets; two with tables Sheet1 I called query I created range names on the other sheets and called them Table1, Table2 ... The tables are dynamic so each table may have a different number of rows. Table1 refers to =OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A)-1,4) in the Insert Name form, each table needs a name. Mine is a four column table. and the headers are ignored (not copied) if you want the headers remove the -1 after the Counta function I created a list of the named ranges and used this for data validation in Cell F1 in sheet1 (the sheet to copy to) Select you table name in F1 then run the macro. The code is copied into a VB Module. ALT + F11, Insert Module Paste the code and return to the sheet Sub CopyTable() Dim tbl As Range Dim cell As Range Dim LastRow As Long Dim LastCol As Integer ' Clear the last details LastRow = Range("A1").CurrentRegion.Rows.Count LastCol = Range("A1").CurrentRegion.Columns.Count Range("A2:" & Cells(LastRow, LastCol).Address).ClearContents Set cell = Range("F1") Select Case cell Case Is = "Table1" Set tbl = Range("Table1") tbl.Copy Range("A2") Case Is = "Table2" Set tbl = Range("Table2") tbl.Copy Range("A2") End Select End Sub You will need to have a 'Case Is = ' for each table and the code will need to be modified. When your are happy you can use a command button torun the code. View, Toolbars, Forms to show the toolbar. click the 4th icon (Button) and draw this in the sheet; you will be promted to assign a macro - choose CopyTable. Change the button text to something informative. To run change the text in F1 and click the button. If this helps remember to click yes! Regards Peter "opieandy" wrote: I have 20 sheets of data that are formatted similarly. On the first sheet, I want the user to select one of the 20 sheets and have Excel pull in a range of data from that sheet. What is the best way to allow for user input to select the sheet? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Best Way to Pull Data from Other Sheets
opieandy wrote...
I have 20 sheets of data that are formatted similarly. * On the first sheet, I want the user to select one of the 20 sheets and have Excel pull in a range of data from that sheet. * What is the best way to allow for user input to select the sheet? With 29 or fewer such worksheets, there's a way to do this that avoids volatile functions like INDIRECT. Something along the lines of 'Sheet 0'!B3: =CHOOSE(MATCH(SheetNameEntry,{"Sheet 1";"Sheet 2"; . . . ;"Sheet 20"}, 0), 'Sheet 1'!B3,'Sheet 2'!B3,'Sheet 3'!B3,'Sheet 4'!B3,'Sheet 5'!B3, 'Sheet 6'!B3,'Sheet 7'!B3,'Sheet 8'!B3,'Sheet 9'!B3,'Sheet 10'!B3, 'Sheet 11'!B3,'Sheet 12'!B3,'Sheet 13'!B3,'Sheet 14'!B3,'Sheet 15'!B3, 'Sheet 16'!B3,'Sheet 17'!B3,'Sheet 18'!B3,'Sheet 19'!B3,'Sheet 20'!B3) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pull Data from multiple sheets | Excel Discussion (Misc queries) | |||
pull data from multiple sheets with formula referencing a tab name | Excel Discussion (Misc queries) | |||
Trying to pull data from multiple sheets (and Word docs too) | Excel Discussion (Misc queries) | |||
to pull from multiple sheets-index,match,vlookup,if,and,or??? | Excel Worksheet Functions | |||
pull data from multiple sheets based on input | Excel Worksheet Functions |