Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Automatically populating cells down a row
Hi all--
I have a workbook with sending cells A1:E1 on Sheet1. What I would like to do is link these to Sheet2 and have A1:E1 populate down the rows in Sheet2 each time the values in A1:E1 are changed or updated in Sheet1... For instance, the first time cells A1:E1 are filled in Sheet1 it populates A1:E1 on Sheet2, the second time these sending cells are filled it populates A2:E2 on Sheet2, to A3:E3, and so on. I am quite new to Excel, but I have done an extensive bit of research on the web for answers to this scenario, and I haven't had any success finding a solution. Could anyone point me in the right direction or tell me what I need to do to make this work? Thanks, and apologies ahead of time if this question has been asked on these boards before. I didn't see it. Eric Last edited by erstaples : May 29th 12 at 05:22 PM |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatically populating cells down a row
I would suggest worksheet_change event code but need a couple of
details............. How do Sheet1 A1:E1 cells get changed or updated? Would you want the code to run when any of Sheet1 A1:E1 were changed or only when all of them were changed? Gord On Tue, 29 May 2012 16:13:28 +0000, erstaples wrote: Hi all-- I have a workbook with sending cells A1:E1 on Sheet1. What I would like to do is link these to Sheet2 and have A1:E1 populate down the rows in Sheet2 each time the values in A1:E1 are changed or updated in Sheet1... For instance, the first time cells A1:E1 are filled in Sheet1 it populates A1:E1 on Sheet2, the second time these sending cells are filled it populates A2:E2 on Sheet2, to A3:E3, and so on. I am quite new to Excel, but I have done an extensive bit of research on the web for answers to this scenario, and I haven't had any success finding a solution. Could anyone point me in the right direction or tell me what I need to do to make this work? Thanks, and apologies ahead of time if this question has been asked on these boards before. I didn't see it. Eric |
#3
|
|||
|
|||
Hi Gord,
Thanks for the reply. I've been giving it some thought, and since I'm going to be passing this workbook along to users with little knowledge in excel I want to make it as simple as possible. I researched macro buttons and I think that would be the best way to go about putting this into action. Anytime the macro button is pushed in Sheet1 it will update everything into a new row in Sheet2. My only worry is that the macro button will be pushed too often, posting to sheet2 haphazardly, or submitting duplicate entries. Here is a simplified version of the information I want submitted: Column 1: Job Number 2: Client 3: Description 4: Time to completion Anytime the button is pushed with a new job number, i want data from Sheet1 to be entered on a new row. If the button is pushed with the same job number I want Sheet2 to simply update that information. For instance, if this is on Sheet2 Column 1: Job# 44123 2:Microsoft 3:Business Cards 4: 8 hours And the button is pressed with this information: Column 1: Job#44123 2: Microsoft 3:Business Cards 4: 2 hours I want to simply update the information on the existing row. If it's an entirely new job number I want the information the next open row. I hope this makes sense. I'm new to VBA, but I think that what I'm looking for, in part, is an IF code. Something like IF it's an existing job number in column 1, then update adjacent columns 2,3,4, Else enter data to new row... I hope this makes sense. Eric Quote:
Last edited by erstaples : May 30th 12 at 05:30 PM |
#4
|
|||
|
|||
Gord,
Is it safe to say that you're a bit stumped on this one? Just wondering if I should keep checking back for an answer or not... I am continuing to research VBA and will post if I come up with a solution on my own. Just reaching out to see if it's possible... Thanks, Eric Quote:
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatically populating cells down a row
Sorry about that...........kinda just lost track.
I will have another look today. Yes I am a bit stumped by the added conditions 1. If jobnum in Ax is unique, copy Ax:Dx over to Sheet2 after Sheet1 Ax:Dx are filled in. 2. If jobnum in Sheet1 Ax is a duplicate then search for that duplicate jobnum on Sheet2 and copy just Bx:Dx and overwrite Sheet2 Bx:Dx. So we first have to have jobnum entered in Sheet1 then B:D filled in. When next time a jobnum is entered we have to check to see if it a duplicate of an exisring jobnum. Then we have to check and see if any data in B:D has been changed. Then we have to search for that jobnum on Sheet2. How am I doing so far? How often could you be entering a duplicate jobnum. Why the overwrite? Couldn't you just keep everything on one sheet and simply filter. Gord On Thu, 31 May 2012 23:16:50 +0000, erstaples wrote: Gord, Is it safe to say that you're a bit stumped on this one? Just wondering if I should keep checking back for an answer or not... I am continuing to research VBA and will post if I come up with a solution on my own. Just reaching out to see if it's possible... Thanks, Eric erstaples;1602234 Wrote: Hi Gord, Thanks for the reply. I've been giving it some thought, and since I'm going to be passing this workbook along to users with little knowledge in excel I want to make it as simple as possible. I researched macro buttons and I think that would be the best way to go about putting this into action. Anytime the macro button is pushed in Sheet1 it will update everything into a new row in Sheet2. My only worry is that the macro button will be pushed too often, posting to sheet2 haphazardly, or submitting duplicate entries. Here is a simplified version of the information I want submitted: Column 1: Job Number 2: Client 3: Description 4: Time to completion Anytime the button is pushed with a new job number, i want data from Sheet1 to be entered on a new row. If the button is pushed with the same job number I want Sheet2 to simply update that information. For instance, if this is on Sheet2 Column 1: Job# 44123 2:Microsoft 3:Business Cards 4: 8 hours And the button is pressed with this information: Column 1: Job#44123 2: Microsoft 3:Business Cards 4: 2 hours I want to simply update the information on the existing row. If it's an entirely new job number I want the information the next open row. I hope this makes sense. I'm new to VBA, but I think that what I'm looking for, in part, is an IF code. Something like IF it's an existing job number in column 1, then update adjacent columns 2,3,4, Else enter data to new row... I hope this makes sense. Eric |
#6
|
|||
|
|||
Gord,
Thank you for taking the time to respond. I will get back to this in the morning. I just saw your response but it's a little late and my brain is dead from the work day. ;) Last edited by erstaples : June 2nd 12 at 07:29 AM |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatically populating cells down a row
Perhaps you could email me a sample workbook with some examples of
data and an explanation of what you're wanting to achieve. gorddibbATshawDOTca change the obvious. Gord On Sat, 2 Jun 2012 06:24:04 +0000, erstaples wrote: Gord, Thank you for taking the time to respond. I will get back to this in the morning. I just saw your response but it's a little late and my brain is dead from the work day. ;) |
#8
|
|||
|
|||
I figured it out, Gord. It turns out that the solution was really really simple.
I did a Do Until... Or Code:
trends_counter = 7 Do Until Trends.Sheets("sheet1").Cells(trends_counter, 1).Value = ThisWorkbook.Sheets("komori").Range("JobNumber").Value Or Trends.Sheets("sheet1").Cells(trends_counter, 1).Value = "" trends_counter = trends_counter + 1 Loop ThisWorkbook.Sheets("komori").Range("JobNumber").Copy Trends.Sheets("sheet1").Range("A" & trends_counter).PasteSpecial xlPasteAll ThisWorkbook.Sheets("komori").Range("JobSpecs").Copy Trends.Sheets("sheet1").Range("B" & trends_counter).PasteSpecial xlPasteAll Trends.Save Trends.Close I'm learning as I go here. If there's a simpler or more efficient way of doing this I'd love to hear it, but I've tested this and it does what I need it to do... Last edited by erstaples : June 3rd 12 at 06:33 AM |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatically populating cells down a row
Good deal...........
Gord On Sun, 3 Jun 2012 05:26:18 +0000, erstaples wrote: I figured it out, Gord. It turns out that the solution was really really simple. I did a Do Until... Or Code: -------------------- trends_counter = 7 Do Until Trends.Sheets("sheet1").Cells(trends_counter, 1).Value = ThisWorkbook.Sheets("komori").Range("JobNumber").V alue Or Trends.Sheets("sheet1").Cells(trends_counter, 1).Value = "" trends_counter = trends_counter + 1 Loop ThisWorkbook.Sheets("komori").Range("JobNumber").C opy Trends.Sheets("sheet1").Range("A" & trends_counter).PasteSpecial xlPasteAll ThisWorkbook.Sheets("komori").Range("JobSpecs").Co py Trends.Sheets("sheet1").Range("B" & trends_counter).PasteSpecial xlPasteAll Trends.Save Trends.Close -------------------- What this does is the counter in the second book first looks for the job number. If the counter finds the job number it stops there and any new job specs from the first workbook will update down the row. If the counter doesn't find the job number it goes to the first empty cell and the new job & job specs are copy/pasted from the first workbook. I'm learning as I go here. If there's a simpler or more efficient way of doing this I'd love to hear it, but I've tested this and it does what I need it to do... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically Populating a Worksheet | Excel Worksheet Functions | |||
Automatically populating fields in excel | Excel Worksheet Functions | |||
Automatically populating new rows with formuals above | Excel Programming | |||
Automatically populating a future date and time | Excel Discussion (Misc queries) | |||
Help Automatically Populating A Summary sheet | New Users to Excel |