Excel help required - moving range of data from one worksheet to another

[garystu1965]

Hi all,

I was wondering if an Excel expert on here can help me with something I need to do for a report.

One worksheet has multiple columns of data.

I want to be able to filter the data and transfer it to a fresh worksheet so I can put some fancy formatting so it can be used for a report.

The original data has about 30 columns but on the new version I only need to show about 5 of the columns.

I have sort of half achieved it by creating a custom filter and moving the filtered records to another worksheet but it doesn't have the facility to hide or remove the columns I don't want displaying.

Any ideas ?

Thanks,
Gary

 

[Spin_bowler]

Yes. Right click on the grey letter at the head of the column you don't want to see and select 'hide' from the menu.

 

[wemorgan]

I'm not entirely sure what you mean, but in the new worksheet are you able to highlight, for example all of column A, by clicking A, then right click and select 'hide'?

 

[jaymanek]

if im reading correctly, all you need to do is unfilter the original sheet, select all (ctrl + a), copy, paste into new worksheet... hide or filter the rows/columns you dont want and then do whatever you want to do.

 

[garystu1965]

Sorry i forgot to say i need the data to be automatically transferred to the new worksheet. I know how to do it manually.

 

[LTD]

Highlight the data range in one column that you want

Copy

Go to new sheet and locate top cell you want data to start from

Paste Special

Paste Link

The data should then be a replicant copy of the data on the original sheet. If the original data changes, so will the data on the second sheet.

Repeat as necessary for all the columns that you require

 

[st13phil]

You can use MSQuery to extract the rows you're interested in from worksheet #1 and display only the columns you want in worksheet #2.

Take a look at the menu path Data>Import External Data>New Database Query and then select Excel Files as the data source. Point the query to worksheet #1 and use the Query Wizard if you're not familiar with constructing SQL queries. You will need to experiment with formatting to be sure you can get what you want when the query result (in worksheet #2) is refreshed.

 

[jaymanek]

ahh then we are into the world of VLOOKUPS & HLOOKUPS... Im not sure I can explain that on here... took me a while to learn it well. Maybe look it up on excel help.

As above, you can also use a query but I find it a lot more complicated, especially if its only a small data set you are working with.

 

[trainer]

For simplicity the paste special and paste link tip posted by LTD is a good way of doing it. Quick and effective.