Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Really need some help - Creating a table without manually typing in1000's of cells
Hi,
I am stumped. I am not good at writing macros(but I am learning). Here is my situation. I have 2 tables. One table is a summary, while table 2 shows the specifics of the items in table one. Once someone types an ID # into table one it immediately populates table 2 with 18 rows (that all have the exact same ID #). If someone creates another entry another 18 rows are populated. What I am trying to do is on the 2nd worksheet link the top cell and pull down 17 more cells(then vlookup the figures). The problem is if I have 500 entries, i have to create the top cell and pull down 17 cells 500!!! times. Is there an easy way to do this? Thanks, John |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Really need some help - Creating a table without manually typing i
It is not clear what you mean by pulling down 17 more cells. Also, where are
the ID numbers coming from. It may help if you post you old code. " wrote: Hi, I am stumped. I am not good at writing macros(but I am learning). Here is my situation. I have 2 tables. One table is a summary, while table 2 shows the specifics of the items in table one. Once someone types an ID # into table one it immediately populates table 2 with 18 rows (that all have the exact same ID #). If someone creates another entry another 18 rows are populated. What I am trying to do is on the 2nd worksheet link the top cell and pull down 17 more cells(then vlookup the figures). The problem is if I have 500 entries, i have to create the top cell and pull down 17 cells 500!!! times. Is there an easy way to do this? Thanks, John |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Really need some help - Creating a table without manually typingi
On Jul 31, 4:14 pm, Joel wrote:
It is not clear what you mean by pulling down 17 more cells. Also, where are the ID numbers coming from. It may help if you post you old code. " wrote: Hi, I am stumped. I am not good at writing macros(but I am learning). Here is my situation. I have 2 tables. One table is a summary, while table 2 shows the specifics of the items in table one. Once someone types an ID # into table one it immediately populates table 2 with 18 rows (that all have the exact same ID #). If someone creates another entry another 18 rows are populated. What I am trying to do is on the 2nd worksheet link the top cell and pull down 17 more cells(then vlookup the figures). The problem is if I have 500 entries, i have to create the top cell and pull down 17 cells 500!!! times. Is there an easy way to do this? Thanks, John I actually haven't written any code. I am unsure how to proceed is the main problem so let me explain on the first sheet it looks like this ID Data1 Data2 Data3 Data4..................................... 111 # # # #............................................ The second sheet needs to be in this format ID 111 Data1 # 111 Data2 # 111 Data3 # 111 Data4 # 111 Data5 # 112 112 112 .... .... ... ... 113 113 113. So as data in input horizontally it's creating on the 2nd sheet individual data(basically in one long column). When the next row of data is input on the original sheet it creates the exact same number of data points below the original input. Any ideas? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Really need some help - Creating a table without manually typi
I hard coded the numbers on sheet 2, didn't use an links. I can modify the
code to add formulas if you need it. Sub MoveData() RowCount = 1 LastRow = 1 With Sheets("Sheet1") Do While .Range("A" & RowCount) < "" ID = .Range("A" & RowCount) For ColCount = 2 To 19 Data = .Cells(RowCount, ColCount) With Sheets("Sheet2") .Range("A" & LastRow) = ID .Range("B" & LastRow) = Data LastRow = LastRow + 1 End With Next ColCount RowCount = RowCount + 1 Loop End With End Sub " wrote: On Jul 31, 4:14 pm, Joel wrote: It is not clear what you mean by pulling down 17 more cells. Also, where are the ID numbers coming from. It may help if you post you old code. " wrote: Hi, I am stumped. I am not good at writing macros(but I am learning). Here is my situation. I have 2 tables. One table is a summary, while table 2 shows the specifics of the items in table one. Once someone types an ID # into table one it immediately populates table 2 with 18 rows (that all have the exact same ID #). If someone creates another entry another 18 rows are populated. What I am trying to do is on the 2nd worksheet link the top cell and pull down 17 more cells(then vlookup the figures). The problem is if I have 500 entries, i have to create the top cell and pull down 17 cells 500!!! times. Is there an easy way to do this? Thanks, John I actually haven't written any code. I am unsure how to proceed is the main problem so let me explain on the first sheet it looks like this ID Data1 Data2 Data3 Data4..................................... 111 # # # #............................................ The second sheet needs to be in this format ID 111 Data1 # 111 Data2 # 111 Data3 # 111 Data4 # 111 Data5 # 112 112 112 .... .... ... ... 113 113 113. So as data in input horizontally it's creating on the 2nd sheet individual data(basically in one long column). When the next row of data is input on the original sheet it creates the exact same number of data points below the original input. Any ideas? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Joel or Anyone, help continued.
On Aug 1, 7:12*am, Joel wrote:
I hard coded the numbers on sheet 2, didn't use an links. *I can modify the code to add formulas if you need it. Sub MoveData() RowCount = 1 LastRow = 1 With Sheets("Sheet1") * *Do While .Range("A" & RowCount) < "" * * * ID = .Range("A" & RowCount) * * * For ColCount = 2 To 19 * * * * *Data = .Cells(RowCount, ColCount) * * * * *With Sheets("Sheet2") * * * * * * .Range("A" & LastRow) = ID * * * * * * .Range("B" & LastRow) = Data * * * * * * LastRow = LastRow + 1 * * * * *End With * * * Next ColCount * * * RowCount = RowCount + 1 * *Loop End With End Sub " wrote: On Jul 31, 4:14 pm, Joel wrote: It is not clear what you mean by pulling down 17 more cells. *Also, where are the ID numbers coming from. *It may help if you post you old code. " wrote: Hi, I am stumped. *I am not good at writing macros(but I am learning).. Here is my situation. *I have 2 tables. *One table is a summary, while table 2 shows the specifics of the items in table one. Once someone types an ID # into table one it immediately populates table 2 with 18 rows (that all have the exact same ID #). If someone creates another entry another 18 rows are populated. What I am trying to do is on the 2nd worksheet link the top cell and pull down 17 more cells(then vlookup the figures). *The problem is if I have 500 entries, i have to create the top cell and pull down 17 cells 500!!! times. Is there an easy way to do this? Thanks, John I actually haven't written any code. *I am unsure how to proceed is the main problem so let me explain on the first sheet it looks like this ID * Data1 * *Data2 * Data3 Data4..................................... 111 * # * * * * * *# * * * * # #............................................ The second sheet needs to be in this format ID 111 *Data1 *# 111 *Data2 *# 111 *Data3 *# 111 *Data4 *# 111 *Data5 *# 112 112 112 .... .... ... ... 113 113 113. So as data in input horizontally it's creating on the 2nd sheet individual data(basically in one long column). When the next row of data is input on the original sheet it creates the exact same number of data points below the original input. Any ideas?- Hide quoted text - - Show quoted text - I've edited the code so that colums 18 to 33 are in the data fields. But I can't figure out how do make it so that columns 34 to 53 will display in the next column. This is exactly what I need my data to look like. ID SEQUENCE MONTH_YEAR PROJECTED_SAVINGS ACTUAL_SAVINGS QUARTER FISCAL_YEAR 1 1 8/1/2008 1200 1200 1 2009 1 2 9/1/2008 1500 1500 1 2009 1 3 10/1/2008 500 500 2 2009 1 4 11/1/2008 2100 2100 2 2009 1 5 12/1/2008 0 0 2 2009 1 6 1/1/2009 0 0 3 2009 1 7 2/1/2009 0 0 3 2009 1 8 3/1/2009 1600 1600 3 2009 1 9 4/1/2009 2000 2000 4 2009 1 10 5/1/2009 0 0 4 2009 1 11 6/1/2009 0 0 4 2009 1 12 7/1/2009 0 0 1 2010 1 13 8/1/2009 0 0 1 2010 1 14 9/1/2009 2500 2500 1 2010 1 15 10/1/2009 300 300 2 2010 1 16 11/1/2009 0 0 2 2010 1 17 12/1/2009 0 0 2 2010 1 18 1/1/2010 0 0 3 2010 2 1 8/1/2008 1200 1200 1 2009 2 2 9/1/2008 1500 1500 1 2009 2 3 10/1/2008 500 500 2 2009 2 4 11/1/2008 2100 2100 2 2009 2 5 12/1/2008 0 0 2 2009 2 6 1/1/2009 0 0 3 2009 2 7 2/1/2009 0 0 3 2009 2 8 3/1/2009 1600 1600 3 2009 2 9 4/1/2009 2000 2000 4 2009 2 10 5/1/2009 0 0 4 2009 2 11 6/1/2009 0 0 4 2009 2 12 7/1/2009 0 0 1 2010 2 13 8/1/2009 0 0 1 2010 2 14 9/1/2009 2500 2500 1 2010 2 15 10/1/2009 300 300 2 2010 2 16 11/1/2009 0 0 2 2010 2 17 12/1/2009 0 0 2 2010 2 18 1/1/2010 0 0 3 2010 The dates are headers to the columns, the id is already gotten, the 1 through 18 is just a count. The actual and the projected is where I am having the problems. The first column is easy based on the code you gave me, but I can't figure out how to make it move a column over and print the next set of information. The quarter and fiscal years aren't as important. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a template that is manually updated from another sheet. | Excel Worksheet Functions | |||
How do I get consecutive numbers without typing them in manually | Excel Discussion (Misc queries) | |||
list all worksheets in a workbook w/o manually typing | Setting up and Configuration of Excel | |||
Creating a bar graph manually | Excel Worksheet Functions | |||
Creating menus manually without VBA? | Excel Programming |