Importing A Certain Section Of Spreadsheet Into Another Spreadsheet

[Mike Langlois]

Hi all,

I am trying to have only a certain part of a spreadsheet imported into another spreadsheet, and can't seem to do it without bringing it all across.

The scenario is:

I have around 30 or so individually named sheets all named after colleagues. These sheets have 10 rows at the top of a sheet that are unnecesary, however from row 11 onwards is a table with the details I want. I want to import that table into another spreadsheet, and have it so I can refresh it easily.

This will then be repeated for all colleagues so i'll have 1 spreadsheet per person, with 1 spreadsheet having all colleagues information on.

I am importing the sheets from the colleagues spreadsheets, however its pulling across the top 10 rows which I do not need. Is there a way to specify what data I want to import from one spreadsheet to another? (such as, from this row onwards)

Thanks,
Mike

 

[PCosta87]

Hi all,

I am trying to have only a certain part of a spreadsheet imported into another spreadsheet, and can't seem to do it without bringing it all across.

The scenario is:

I have around 30 or so individually named sheets all named after colleagues. These sheets have 10 rows at the top of a sheet that are unnecesary, however from row 11 onwards is a table with the details I want. I want to import that table into another spreadsheet, and have it so I can refresh it easily.

This will then be repeated for all colleagues so i'll have 1 spreadsheet per person, with 1 spreadsheet having all colleagues information on.

I am importing the sheets from the colleagues spreadsheets, however its pulling across the top 10 rows which I do not need. Is there a way to specify what data I want to import from one spreadsheet to another? (such as, from this row onwards)

Thanks,
Mike

Hi Mike,

If possible please provide a sample file.

 

[Mike Langlois]

I have attached a copy of the colleagues file. What I want is the table that starts at row 14 to be imported into a new spreadsheet as a refreshable data table, so the second spreadsheet can be refreshed and pull back all data from the colleauges spreadsheet.

If that makes sense?

 

[PCosta87]

Have you considered a Pivot Table?... If I understand correctly it should do what you want

 

[Mike Langlois]

Thanks for your replies PCosta. When I go to use pivot table, it still pulls back the rows above row 14 on my example file.

Also not sure if its clear, but I want this data to be on an entirely new workbook, and to be updated when clicking refresh.

 

[PCosta87]

Thanks for your replies PCosta. When I go to use pivot table, it still pulls back the rows above row 14 on my example file.

Also not sure if its clear, but I want this data to be on an entirely new workbook, and to be updated when clicking refresh.

See if this helps... it can also be used in a different workbook

 

[pecoflyer]

If you are using Windows the following might help http://www.rondebruin.nl/win/addins/rdbmerge.htm
or some code
http://www.rondebruin.nl/win/section3.htm

 

[Mike Langlois]

See if this helps... it can also be used in a different workbook

Thanks for the example Pcosta, and it works, however its not quite what I am looking for. This "master" sheet that has all the tables from the colleagues sheets will be used by basic-level users, so just need it to be "click refresh, all the tables come through".

If I am making sense?

Would I be able to do it with named ranges, thinking out loud?

If you are using Windows the following might help http://www.rondebruin.nl/win/addins/rdbmerge.htm
or some code
http://www.rondebruin.nl/win/section3.htm

I'd rather not use addons or code if possible, but thanks!

 

[PCosta87]

Thanks for the example Pcosta, and it works, however its not quite what I am looking for. This "master" sheet that has all the tables from the colleagues sheets will be used by basic-level users, so just need it to be "click refresh, all the tables come through".

If I am making sense?

Would I be able to do it with named ranges, thinking out loud?



I'd rather not use addons or code if possible, but thanks!

Hi,

Question: What do you mean by "all the tables come through"? In your example there is only one table after row 14.

Just so I can be sure I am understanding your problem correctly, you have a master sheet with a table (after row 14) that you want to be visible in various different sheets/workbooks. Also, you want this various sheets to reflect the changes in the master sheet table by clicking refresh.

If the above is correct, you have several different ways of doing it:
1) Already mentioned: Using a Pivot table... right click on table and hit "Refresh" and all changes come through.
2) Also using Pivot table, have a button (single line of code in VBA) to refresh that table (no need for right click to refresh)
3) Have a simple formula in the various sheets to retrieve those values from master sheet... something like "=Sheet1!A14" (you will need to reference the master sheet's workbook if you are adding the formula to a different workbook) - no need to refresh as it will always be updated
4) Paste Link: Just select the table in master sheet, copy. Then, in the desired sheet, select paste special>paste link. When you change the original data, the changes are automatically reflected in the place where you did the Paste Link (basically the same as point 3, just different method)
...

If I understood incorrectly, please provide further clarification and a sample file with the master sheet and also a sheet with the desired output (complete with some dummy data)

 

[Mike Langlois]

Thanks for your help PCosta, sorry if I am not being clear (I am bad at explaining myself!)

I will have several sheets belonging to colleagues that will look like the attached Colleague 1 example. They will have a table on it (row 14 onwards, populated with examples).

What I want, is the workbook example Main, on the sheet Colleague 1, to be connected to the table on the workbook for Colleague 1. So when I refresh the Main workbook, it retrieves all data from the table in Colleague 1.

Is this possible?

 

[PCosta87]

Thanks for your help PCosta, sorry if I am not being clear (I am bad at explaining myself!)

I will have several sheets belonging to colleagues that will look like the attached Colleague 1 example. They will have a table on it (row 14 onwards, populated with examples).

What I want, is the workbook example Main, on the sheet Colleague 1, to be connected to the table on the workbook for Colleague 1. So when I refresh the Main workbook, it retrieves all data from the table in Colleague 1.

Is this possible?

Hi,

Please find attached 3 options that accomplish what you require:
Note that I moved Colleague 1 and Colleague 2 sheets over to the "Main" workbook in order for it to work on your end on first try... you can have the Colleague 1 and Colleague 2 sheets as different workbooks. For that you just need to adjust the source range of the Pivot Table and you are good to go.

File "Main": Here you have 2 options
- Option 1 -> Using Pivot Tables as already suggested. Here you can refresh each Pivot Table individually in the actual sheet where the Pivot Table is located, or use the button in the "Main" tab/sheet to refresh all Pivot Tables with a single click. Small caveat here: if you add lines or columns to the original data in Colleague 1 or Colleague 2 you will need to adjust the source range of the Pivot Table to account for the new data. One solution would be to expand the source range from the start (which would allow for more data to be added) and filter the "empty" lines in the Pivot Table. A better solution is on the file "Mainv2" (read below)

- Option 2 -> Linking the data. Here the formulas pull the data from the source Colleague 1 and Colleague 2 sheets and are constantly updated as you change that data. Caveat: if you add lines or columns to the original data in Colleague 1 or Colleague 2 you will need to copy the formulas down and/or across to account for the new data. Either do that when needed or account for those new lines/columns from the start, but empty cells will appear as "0" unless you add a condition to the formulas in order to display empty cells as empty.

File "Mainv2":
- Here I changed the source Colleague 1 and Colleague 2 tables (from row 14 onward) to an actual "Table". I then created the Pivot Tables using the table's name as the source (instead of a fixed range). This way you can add or remove rows/columns in the source and the Pivot Table source range will automatically be adjusted since it is referring to the "Table" and not to a fixed range. I believe this was your problem to begin with since you mentioned using named ranges before.

Hope this is comprehensive enough.
If you have any questions feel free to ask and I will gladly try to help

Let me know if this worked for you.

 

[Mike Langlois]

Hi PCosta, thank you so much for your help and your options. Mainv2 seems to be the best way for me to proceed with this, and is pretty much exactly what I need.

Is it possible for the pivot tables to refresh when the colleague1 spreadsheet is closed however? As when I close the single agents spreadsheet it says "reference is not valid" although the link to the data includes the full network path?

If its not possible, do you know if there is a way to make it possible without having needing the spreadsheet open to refresh?

Many thanks again for your help, I didn't realise this would be such a hard task.

 

[PCosta87]

Hi PCosta, thank you so much for your help and your options. Mainv2 seems to be the best way for me to proceed with this, and is pretty much exactly what I need.

Is it possible for the pivot tables to refresh when the colleague1 spreadsheet is closed however? As when I close the single agents spreadsheet it says "reference is not valid" although the link to the data includes the full network path?

If its not possible, do you know if there is a way to make it possible without having needing the spreadsheet open to refresh?

Many thanks again for your help, I didn't realise this would be such a hard task.

Hi,

Yes, with a little VBA code you can update the Pivot Tables when you close the source workbook. Add the following to every source workbook, in the "ThisWorkbook" object (you will need to change the path to where you have your "Main" file):

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Application.ScreenUpdating = False

    Workbooks.Open "D:\Mainv2.xlsm"
    Application.Run ("'Mainv2.xlsm'!RefreshAll")
    ActiveWorkbook.Close savechanges:=True

    Application.ScreenUpdating = True

End Sub

Just open one of the files with the original data and press ALT+F11. In VBA double click "ThisWorkbook" and paste the code above.

My suggestion: update the Pivot Tables when you open the "Main" workbook. With VBA you can force the refresh immediately after opening the "Main"file and all the info will be updated when you start working on it. Much simpler and efficient in my opinion. If you want I can help with that code too.

Hope this helps