Copy Row to single cell multiple sheets

Question

Hello I appreciate any assistance on this.  

I want to copy a row of values from one worksheet to a single cell on multiple sheets.

For example, on the first sheet, I have a row with 10 values.  Each value needs to be copied to a specific cell on 10 sheets.  The cell ID is the same.  

A1 needs to copy to first sheet A1

A2 needs to copy to second sheet A1

I hope that makes sense.

I truly appreciate any guidance.

regards,

kevin

Answer 1

Hi Kevin,

According to your description, you use a third party tool to generate an Excel file, the file has three kinds of worksheets: 

1. Index sheet tab 1.
2. Summary sheet tab 2: If you have tested with 3 items, then there may be 3 rows in the summary sheet.
3. Detail sheet and the number depends on tests: If you have tested with 3 items, then there are 3 detail sheets.

And the result you want to achieve is that copy each value in a column to the related detail sheet.

Please let me know if I miss anything.

If it is your scenario, since the data and worksheets in the Excel file are always updated, it is not feasible to automatically get the related value in each detail sheet with some settings or formulas in Excel. You may need to type the formula in each cell in detail sheets to get the value in summary sheet.

However, you may achieve your requirement by using macros, to make sure that you can get professional help, I would suggest you post a new thread in this category in our forum: Office/ Excel/ Microsoft Office programming/ Office 2016.

And you can attach the link of this thread to the new thread so that the support can understand your scenario and you do not need to type again.

Thanks for your understanding.

Best Regards,

Gloria

Answer 2

Thank you for the response.  This is interesting I will do some research on this tonight as I can see it having some value in some of the things I am trying to do and learn.

I apologize if I am not explaining it well enough.  The reason I need them on different sheets is that is how it is exported from the tool I am using.

Essentially I am testing data lines with a piece of hardware.  It exports a custom report to excel.  It creates an Index tab on the first tab.  The second tab is a summary of all the results based on the number of test performed.  It could be 2 test or 1000. from the 3rd tab is the details of each test and the number of tabs is based on the number of test performed.  could be 2 or 1000.  There is 1 specific piece of information on Tab 2 in a column that I want copied into the individual sheets.

So on tab 2 (summary) starting at C3 and going down I need those cells copied over to the other sheets to a single cell.  C3 on tab 2 to F3 on Tab 3.  C4 from tab 2 (summary) to F3 on Tab 4 and so on.

Again the amount of tabs is dependent on the number of test done so it will vary greatly.

Thank you.

Answer 3

Why do you explicitly want them on separate sheets.

Why not use the Data tab > Data tools > Text to columns tool to pivot it from a row to a column, then add a second column with order identifiers ie  a1, a2, a3 ... and maybe a third column with datetimestamp or some other unique identifier.

You could look in to "Get & Transform" / "PowerQuery"  to automate this transformation.  The first time you define the steps required to transform the data the way you want, the system records what you want ("macros" in background), so that they can be replayed every time you add more data to the workbook

Here are links to a few intro pages to Get & Transform that can get you started

@ Get & Transform in Excel 2016 Intro -  

https://support.office.com/en-us/article/Get-Transform-in-Excel-2016-881c63c6-37c5-4ca2-b616-59e18d75b4de

Excel 2016 includes a powerful new set of features called Get & Transform, which provides fast, easy data gathering and shaping capabilities. Get & Transform enables you to connect, combine, and refine data sources to meet your analysis needs. These features are also used in Power BI, and in the Power Query Add-In available for previous versions of Excel. Links to more info about using the feature.

@ Get & Transform- Pivotal Advance Boosts Excel’s Power – PowerPivot Enhances PivotTablehttp://www.excel-university.com/articles/journal-of-acct/pivotal-advance-boosts-excels-power/

September 2011             Jeff Lenning

In today’s accounting world, financial and operational data typically is stored in a variety of programs and formats. When accountants need to prepare a report based on data from various systems, the first step is to export the data into Excel. Typically, it is fairly easy to export the required data into Excel. But, depending on the structure or format of the data, or if multiple data tables need to be combined, it is not always easy to summarize the data in a single report.

That’s where a new Excel tool called PowerPivot comes into play. PowerPivot is a free plug-in from Microsoft that boosts the capabilities of the already popular PivotTable function, allowing you to create previously impossible PivotTables that enhance Excel’s efficiency and effectiveness. ET MR PivotTables.docx

@ Getting Started with Get & Transform in Excel 2016https://support.office.com/en-us/article/Getting-Started-with-Get-Transform-in-Excel-2016-a8310388-2a12-438c-9d29-c6d29cb8df6a

With Get & Transform in Excel 2016, you can search for data sources, make connections, and then shape that data (for example remove a column, change a data type, or merge tables) in ways that meet your needs. Once you’ve shaped your data, you can share your findings or use your query to create reports.

ET R 0 Ribbon.docx

@ Import and analyze dataImport data

https://support.office.com/en-us/article/Import-and-analyze-data-ccd3c4a6-272f-4c97-afbb-d3f27407fcde#ID0EAABAAA=Import_data

You can use Excel’s Get & Transform (Power Query) experience to connect to multiple data sources, then use the Query Editor to shape and transform data.

    Getting Started with Get and Transform

    Import data from external data sources

    View and Manage Queries in a Workbook

    Add a query to an Excel worksheet

    Introduction to the Query Editor

    Shape data

    Edit Query Step Settings

They aren't offering it right now, but check back periodically to see when they do start offering this specific webinar. I think it will help you greatly.  (on the other hand you may want to sign up for the other 3 webinars that they are offering right now).

@ data cleaning- MyExcelOnline webinars by John Michaloudis and Oz Du Soleil

Free Power Query & Data Cleansing Webinarhttps://www.myexcelonline.com/free-excel-webinars 

  *  The most powerful feature in Excel since VLOOKUP...it's called POWER QUERY (GET & TRANSFORM) & it will save you HOURS each day!!!

  *  Transform messy data & automate your daily/weekly/monthly reports within minutes (NOT DAYS!)... AND without using VBA or Macros!

  *  Data Cleansing Excel Tips & Special Bonus attendee material that you can keep and use to become better at Excel straight away, skyrocketing your personal & professional development!

  *  Plus learn how to use Data Models, Unpivot, Parse Text, Consolidate Reports & Flash Fill!

  *  Data Models- Analyzing multiple datasets as if they are a single dataset

  *  Parsing Text- Splitting data apart, ie when name age and email are all in same cell

  *  Inner Join – Comparing lists to extract data that appears on both lists

  *  Unpivot – convert a report into raw data so that I is useful for analysis

  *  Consolidate multiple worksheets

Answer 4

Dave, I appreciate the response.

The sheets are created automatically with an on open instance.

I am exporting all this data automatically but I am not able to capture the information I want to copy until after this happens.  I need to mirror the information from the first sheet after all this happens each time.

Basically I have a piece of hardware that gathers data.  I am able to export all this data into a summary sheet which is the sheet with the Row information.  I want to take this data and separate it for clarity to multiple sheets.  These sheets are not static in that I have a cell to paste into.  Sometimes there are 10 sheets other time there is a 1000.

What I want is to be able to figure out a way to associate the data on the rows with an individual cell on different sheets.

Thank you

Answer 5

Hi Kevin, I think this is what you are looking for, if not please let me know . . .

If say, Sheet 1's name is Sheet1 ,then enter this formula into A1 on Sheet 2

=Sheet1!A1

Then on Sheet 3 cell A1 enter this formula:

=Sheet1!A2

And so on . . .