Jump to content


Photo

Capturing Data by Rows from Various Sheets


  • Please log in to reply
3 replies to this topic

#1 Zella

Zella

    Member

  • Members
  • PipPip
  • 12 posts

Posted 16 April 2017 - 02:18 PM

Is it possible to ‘capture’ data from various spreadsheets on a ‘master’ sheet in the same book AND retain that data even if the data is changed on an individual spreadsheet?  These are two separate questions.  Here is the scenario.

 

See the attached Excel workbook.  I haven't entered any formulas yet...that's what I need help with, please.

 

Each sheet has the same Column Headings except the Master, which has one more “Individual”

 

If Dick enters data on his sheet on Row 1, I know how to capture that data on Row 1 of the master sheet, but then if Harry enters data on his sheet on Row 1, how do I capture that same data on the next blank row of the Master sheet rather than overwriting the data captured from Dick’s sheet the day before?

 

Also, is there a way to retain an initial entry in a cell even if it is changed or deleted?  For instance, if Paul enters training data in a row of his sheet on 4/20/17, and I’m able to capture that same data on the Master sheet; then Paul changes the “Date Entered” from 4/20/17 to 4/25/17.  Can the Master sheet retain the initial date Paul entered?

 

I’m thinking I could put a formula in Column A on the individual sheets to enter “TODAY” whenever data is entered on that Row, and I could protect that cell from being changed, but wouldn’t that cell also be updated with today’s date whenever someone goes in and changes the status of a line item?

 

I’m self-taught in Excel from books and forums and have a lot to learn, but some of these unique scenarios aren’t covered in books.  Can you help me, ple

Attached Files



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 845 posts

Posted 17 April 2017 - 12:29 PM

Hi Zella,

 

What you are describing sounds like simply copying the information from your other worksheets and pasting them into a single worksheet. You could do this manually, or you could record a macro that copies the data from each worksheet and then pastes it into your 'master' sheet.

 

To make sure the data is recorded at the end of the worksheet, you will need to use the <Ctrl>+<Down Arrow> shortcut key. This will move to the bottom of a table of data no matter how many rows there are.

 

You can see how to record a macro in the video lesson: Expert Skills Lesson 9-11 Record a macro with absolute references.

 

It's also possible to do this in a more automated way using Excel 2016's new Get & Transform feature. Using this, you can create an 'Append' query that will append all of your tables together into a single table. The appended table will automatically reflect any changes that are made to its source data, but only when it is refreshed. You could still use this technique to quickly append all of the data together and avoid refreshing if you don't want the table to be updated.

 

You can see more about how to create an append query in Session 11 of our Excel 2016 Expert Skills book, which is available from our books page.

 

I have attached an example workbook with an Append query in place, showing how two tables can be automatically joined together. Note that this will only work in Excel 2016.

 

Attached File  AppendQuery.xlsx   17.61KB   29 downloads

AppendQuery.png

 

I hope one of these options is the solution you were looking for, and please feel free to reply if you need any more help with this.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 Zella

Zella

    Member

  • Members
  • PipPip
  • 12 posts

Posted 19 April 2017 - 11:33 PM

Thank you ever so much for your information.  Unfortunately, I won't have control of the workbook once it is created.  It will be used by individuals much less familiar with Excel than even I am.  I could copy all the rows of a worksheet onto the Master, but we're talking about entries over a course of months perhaps a few years.  For the workbook 'controller' to have to copy a row of data from each sheet to the Master sheet would mean having to check all the sheets each day to see if new data has been entered by someone.  Too time consuming of a job for the intended user.

 

I believe the version of Excel my users have is 2013; which eliminates your second option.  I was hoping for a formula that would include some form of '=[copy this row into the next blank row on] '!Master'.  Or, from the Master, =[capture all data from worksheets Jones through Smith] on the next blank row as it is entered on !Jones thru !Smith].

 

I guess I'm dreaming though.  I didn't have much hope for something like I've explained, but I had to put the question out there just the same.  Thanks again. 



#4 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 845 posts

Posted 20 April 2017 - 11:07 AM

Hi Zella,

 

It sounds like what you really want is a central database that all of your users can input data into. Excel isn't designed for that purpose, so it will always be difficult to implement any system of that nature in Excel.

 

For a small database like this, you might want to consider using Microsoft Access instead of Excel. Larger databases are usually created using Microsoft SQL Server.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users