+ Reply to Thread
Results 1 to 13 of 13

Complete an Excel Sheet from Address Data taken from rows (lines)

  1. #1
    Registered User
    Join Date
    08-20-2019
    Location
    Surrey, England
    MS-Off Ver
    Office 365
    Posts
    8

    Complete an Excel Sheet from Address Data taken from rows (lines)

    Good morning everyone, thank you for the welcome to this support forum.

    I thought that I was reasonably OK with Excel and basics - at this time however I am stumped. Here's the scenario. I am trying to output a 'Engineer Worksheet' (Form? Printable page)

    1. I have a straight forward sheet which going left to right across the sheet has 'Name, Address, Tel, etc.

    2. I have a second sheet which represents an Engineers Job Sheet - Which I would like to complete from the data entered from point "1" (name, address, tel, etc,), then a button to print the document.

    Now of course I could use the "=" option from sheet 1 to sheet 2, this however is only good for one left to right line of the data - I would need to manually change it every time - which of course is pointless.

    I need to enter the data in to sheet one as it needs to be a maintained for historic reasons.

    Can you help please?

    Kind regards
    Dave

  2. #2
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Complete an Excel Sheet from Address Data taken from rows (lines)

    Welcome to the forum

    Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Registered User
    Join Date
    08-20-2019
    Location
    Surrey, England
    MS-Off Ver
    Office 365
    Posts
    8

    Re: Complete an Excel Sheet from Address Data taken from rows (lines)

    Hello Olly,

    Hopefully file is attached.

    Sheet 1 has the data - two line of it.

    Sheet 2 has an engineers page I wish to populate and wish to print.

    If I have four lines of data for example, I need it to populate the sheet 2 document four times and be able to print it with a print button.

    Hope this helps and thank you once more for your help.

    Kind regards
    Dave
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    08-20-2019
    Location
    Surrey, England
    MS-Off Ver
    Office 365
    Posts
    8

    Re: Complete an Excel Sheet from Address Data taken from rows (lines)

    Not sure what happened to file extension there! It's Excel 365.

    The target goal is to complete the NAME and ADDRESS parts of the sheet.

    If I can see how to complete that - I should be able to complete any other parts of it.

    Best regards,
    Dave
    Last edited by trippyd; 08-20-2019 at 05:09 AM.

  5. #5
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Complete an Excel Sheet from Address Data taken from rows (lines)

    Don't quite understand the workflow you're trying to achieve.

    Do you want to enter a unique identifier (e.g. Job No) on the 'Engineer Sheet', then retrieve the relevant details from 'Job Data' ? If this is the case, then which cell will be changed?

    Or do you want to loop through each row in 'Job Data', and print an 'Engineer Sheet' for each row?

  6. #6
    Registered User
    Join Date
    08-20-2019
    Location
    Surrey, England
    MS-Off Ver
    Office 365
    Posts
    8

    Re: Complete an Excel Sheet from Address Data taken from rows (lines)

    Sorry Olly if not so clear. I will try to explain.

    Sheet one holds as you can see Name, Address, general data across the cells. This data represents one job. Each line represents a job (line 2, 3, 4, 5 and so on)

    I would like to transfer that data from sheet one, in to the relevant matching cell location of sheet two, which is the engineer sheet. I started with using the "=" option... that was OK until I reached line two :-(

    So my goal is, take the information that I manually enter on a line by line basis on sheet 1. Then automatically get it to complete the engineers job sheet - on sheet 2.

    Then to be able to print it.

    I hope this helps.

    I am so grateful for your time.

    Kind regards
    Dave

  7. #7
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Complete an Excel Sheet from Address Data taken from rows (lines)

    That didn't answer my question in post #5...

    I fully understand you want to display one row of 'Job Data' data in the 'Engineer Sheet'. My question is about the trigger to select the row. Is it manually selected (and if so, how - which field is entered to identify the Job Data row), or do you want to automatically loop through all Job Data rows, and print an Engineer Sheet for all of them?

  8. #8
    Registered User
    Join Date
    08-20-2019
    Location
    Surrey, England
    MS-Off Ver
    Office 365
    Posts
    8

    Re: Complete an Excel Sheet from Address Data taken from rows (lines)

    Sorry,

    Sheet one - is manually entered data.
    Sheet two - data is automatically pulled in to it from sheet one.

    Then as you say I would like to you to automatically loop through all Job Data rows, and print an Engineer Sheet for all of them when selected, which a print button.

    (gosh - i feel so daft at times)

    Thank you Olly,

    Dave

  9. #9
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Complete an Excel Sheet from Address Data taken from rows (lines)

    Here's one approach:

    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    08-20-2019
    Location
    Surrey, England
    MS-Off Ver
    Office 365
    Posts
    8

    Re: Complete an Excel Sheet from Address Data taken from rows (lines)

    Hello Olly,

    Great Thank you. After copying code in to master sheet it works a treat - always a however.

    Is there a way you can add to the script so as I can select what "Lines" I wish to print?

    At this time it wants to print 568 lines :-)

    Thank you.

    Regards
    Dave

  11. #11
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Complete an Excel Sheet from Address Data taken from rows (lines)

    How will you be selecting which rows you want to print?

  12. #12
    Registered User
    Join Date
    08-20-2019
    Location
    Surrey, England
    MS-Off Ver
    Office 365
    Posts
    8

    Re: Complete an Excel Sheet from Address Data taken from rows (lines)

    Hello Olly,

    I can either highlight or filter, print by date.

    I can use whatever method is easier for you to compile, either way I am extremely grateful.

    Regards
    Dave

  13. #13
    Registered User
    Join Date
    08-20-2019
    Location
    Surrey, England
    MS-Off Ver
    Office 365
    Posts
    8

    Re: Complete an Excel Sheet from Address Data taken from rows (lines)

    Hello again Olly,

    Hope you have had a great day.

    The code you sent over does the first part perfectly. It completes the second sheet as needed.

    I have tried since you sent this over to use various pieces of code found on line to complete the task.... but have failed miserably.

    If you get an opportunity to help me with the last part I would be exceptionally grateful.

    Kindest regards.
    Dave

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 01-26-2019, 04:04 PM
  2. Moving complete rows of data from one sheet to a new sheet when a date is entered
    By meadnl89 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-28-2014, 11:39 AM
  3. [SOLVED] VBA to Archive lines of data when work is complete
    By sailadayaway in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-14-2014, 09:24 AM
  4. Repeating macro for various worksheets to complete lines in consolidation sheet
    By RossChip in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-08-2013, 07:46 AM
  5. Replies: 2
    Last Post: 12-20-2012, 07:47 PM
  6. Converting a complete, single column address into separate columns for ADDRESS, CITY, ST,
    By jeffrogerssn in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-08-2012, 10:34 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1