Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
auto fill data from cells
i would like to be able to enter text data in cells in column b on 6
different worksheets and then have a seventh worksheet that will display all of the text data from the six different sheets on it. So, if i list 40 words in b5 thru b45(one word per cell) on all six sheets, i would like to see all 240 words on the seventh sheet(total "catch all" sheet) Any help would be greatly appreciatiated. thanks dude |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
auto fill data from cells
On the 7th sheet, in cell A1
='Sheet1'!B5 fill the formula down to A40 On the 7th sheet, in cell B1 ='Sheet2'!B5 and again fill the formula down to B40 Repeat in columns C, D, E, and F for the other 4 sheets. Change the sheet name in the formulas to whatever the real names of those sheets are. The formulas don't have to start in row 1 on the 7th sheet - but whatever row you start them in, the formula should be written as shown above and then filled down for the 40 rows. "dude" wrote: i would like to be able to enter text data in cells in column b on 6 different worksheets and then have a seventh worksheet that will display all of the text data from the six different sheets on it. So, if i list 40 words in b5 thru b45(one word per cell) on all six sheets, i would like to see all 240 words on the seventh sheet(total "catch all" sheet) Any help would be greatly appreciatiated. thanks dude |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
auto fill data from cells
THANKS FOR THE INFO. What if i only have 20 words on sheet 1 and 10 words on
sheet 2, etc...? How do i get them to automatically recognize the last entry from sheet 1 so that it will add the words in the next cell where sheet 2 starts. Based on your previous instruction, it sets up the formula for 40 cells before the next page could start to total on the master sheet. "JLatham" wrote: On the 7th sheet, in cell A1 ='Sheet1'!B5 fill the formula down to A40 On the 7th sheet, in cell B1 ='Sheet2'!B5 and again fill the formula down to B40 Repeat in columns C, D, E, and F for the other 4 sheets. Change the sheet name in the formulas to whatever the real names of those sheets are. The formulas don't have to start in row 1 on the 7th sheet - but whatever row you start them in, the formula should be written as shown above and then filled down for the 40 rows. "dude" wrote: i would like to be able to enter text data in cells in column b on 6 different worksheets and then have a seventh worksheet that will display all of the text data from the six different sheets on it. So, if i list 40 words in b5 thru b45(one word per cell) on all six sheets, i would like to see all 240 words on the seventh sheet(total "catch all" sheet) Any help would be greatly appreciatiated. thanks dude |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
auto fill data from cells
In your original post you spoke specifically of 40 words per sheet for a
total of 240 words on the 6 sheets. My response was geared toward those 'specifications', and while it put each sheet's words in separate columns on the 7th sheet, it could be changed to put them head-to-tail in one column. To do what it sounds like you want done at this point is probably best done with a macro, and I'll put one together and post back here with it later. I don't have time to put it together right this minute - my day job calls <g. It's not hard to put together, it just takes a little longer to write up and test out than I have available right now. "dudewithaquestion" wrote: THANKS FOR THE INFO. What if i only have 20 words on sheet 1 and 10 words on sheet 2, etc...? How do i get them to automatically recognize the last entry from sheet 1 so that it will add the words in the next cell where sheet 2 starts. Based on your previous instruction, it sets up the formula for 40 cells before the next page could start to total on the master sheet. "JLatham" wrote: On the 7th sheet, in cell A1 ='Sheet1'!B5 fill the formula down to A40 On the 7th sheet, in cell B1 ='Sheet2'!B5 and again fill the formula down to B40 Repeat in columns C, D, E, and F for the other 4 sheets. Change the sheet name in the formulas to whatever the real names of those sheets are. The formulas don't have to start in row 1 on the 7th sheet - but whatever row you start them in, the formula should be written as shown above and then filled down for the 40 rows. "dude" wrote: i would like to be able to enter text data in cells in column b on 6 different worksheets and then have a seventh worksheet that will display all of the text data from the six different sheets on it. So, if i list 40 words in b5 thru b45(one word per cell) on all six sheets, i would like to see all 240 words on the seventh sheet(total "catch all" sheet) Any help would be greatly appreciatiated. thanks dude |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
auto fill data from cells
The following code will take entries from column B on the 6 sheets and copy
them head-to-tail on the 7th sheet in column A. You can adjust the columns used in the code. You'll need to set the sheet names in the code also, so that they match the sheet names in your workbook. It processes all 6 sheets at once, and it assumes no empty cells between entries in column B of the 6 sheets. To insert the code: press [Alt]+[F11] to open the VB Editor. Use Insert | Module in the VB Editor menu to insert a new code module, then copy the code below and paste it into the code module. Close the VB Editor and use the macro when you need to (after modifying the code for the workbook you put it into). The CheckLists macro is the only one of these 2 that will show in your macro list, and it's the one to choose to perform the operation. Sub CheckLists() 'go thru all worksheets 'and if a sheet is one 'with list we want to copy 'then do so, but ignore 'any others, including the 'one (Sheet7) where the lists 'will be combined Dim anySheet As Worksheet Dim doItFlag As Boolean 'clear any older results Worksheets("Sheet7").Cells.Clear For Each anySheet In ThisWorkbook.Worksheets 'change the sheet names in the 'Case Is = 'statements as needed 'and add more Case Is = statements 'if you add more sheets to process doItFlag = False Select Case anySheet.Name Case Is = "Sheet1" doItFlag = True Case Is = "Sheet2" doItFlag = True Case Is = "Sheet3" doItFlag = True Case Is = "Sheet4" doItFlag = True Case Is = "Sheet5" doItFlag = True Case Is = "Sheet6" doItFlag = True Case Else 'for any sheet not listed above 'leave doItFlag as False End Select If doItFlag Then CombineLists anySheet End If Next End Sub Private Sub CombineLists(sourceSheet As Worksheet) 'this will copy entries from column B of sourceSheet 'into column A of destSheet (Sheet7) 'in head-to-tail fashion ' 'these constants control what columns are 'involved in the data copy 'change to use different columns srcColStart = "B1" destColStart = "A1" 'last used row on source sheet Dim srcLastRow As Long 'last used row on destination sheet Dim destLastRow As Long Dim maxRows As Long Dim destSheet As Worksheet Dim srcOffset As Long 'change name of worksheet as needed Set destSheet = ThisWorkbook.Worksheets("Sheet7") If Val(Left(Application.Version, 2)) < 12 Then 'in pre-2007 Excel maxRows = Rows.Count Else 'in Excel 2007 (or later) maxRows = Rows.countlarge End If destLastRow = destSheet.Range(destColStart).End(xlDown).Row If destLastRow = maxRows Then destLastRow = 0 End If srcLastRow = sourceSheet.Range(srcColStart).End(xlDown).Row If srcLastRow = maxRows Then 'nothing to copy, nothing entered 'above the last row on the sheet Exit Sub End If For srcOffset = 0 To srcLastRow - 1 destSheet.Range(destColStart).Offset(destLastRow, 0).Value = _ sourceSheet.Range(srcColStart).Offset(srcOffset, 0).Value destLastRow = destLastRow + 1 Next End Sub "dudewithaquestion" wrote: THANKS FOR THE INFO. What if i only have 20 words on sheet 1 and 10 words on sheet 2, etc...? How do i get them to automatically recognize the last entry from sheet 1 so that it will add the words in the next cell where sheet 2 starts. Based on your previous instruction, it sets up the formula for 40 cells before the next page could start to total on the master sheet. "JLatham" wrote: On the 7th sheet, in cell A1 ='Sheet1'!B5 fill the formula down to A40 On the 7th sheet, in cell B1 ='Sheet2'!B5 and again fill the formula down to B40 Repeat in columns C, D, E, and F for the other 4 sheets. Change the sheet name in the formulas to whatever the real names of those sheets are. The formulas don't have to start in row 1 on the 7th sheet - but whatever row you start them in, the formula should be written as shown above and then filled down for the 40 rows. "dude" wrote: i would like to be able to enter text data in cells in column b on 6 different worksheets and then have a seventh worksheet that will display all of the text data from the six different sheets on it. So, if i list 40 words in b5 thru b45(one word per cell) on all six sheets, i would like to see all 240 words on the seventh sheet(total "catch all" sheet) Any help would be greatly appreciatiated. thanks dude |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
auto fill series between to non-adjacent cells | Excel Discussion (Misc queries) | |||
How can I auto fill cells in excel for example 1 of 50, 2 of 50 | Excel Discussion (Misc queries) | |||
Auto Fill a row of cells | Excel Discussion (Misc queries) | |||
auto color fill cells | New Users to Excel | |||
Auto fill in cells | Excel Worksheet Functions |