Dynamic sheet that transfers data from another sheet (same file) and adjusting for rowsize

[DawitX]

I hope someone can help me with my problem below.

What I'm trying to do is to copy a number of columns from one sheet (ExtractionType1) to a master sheet (MasterType) in an order depending on the relevancy of the columns which I want to decide. So I want the columns on the sheet, MasterType, to be left alone but the corresponding data which will be taken from the sheet, ExtractionType1, to automatically be displayed on the master sheet with some form of macro. The thing is that the sheet ExtractionType1 will be empty until I extract my data (copied from a different program) so the rows can vary for that sheet.

Right now I have recorded a macro in which it fills the sheet, MasterType, with the data from ExtractionType1. But first I have to start off with dragging the plus sign from MasterType!A3 until last line. I'm not sure I'm making myself understood but what I want to do is automate this irrespective how many rows I have on the sheet, ExtractionType1.

If say ExtractionType1 is empty and I extract my data to it; I want MasterType to show the columns I value automatically. In this case I want the names above the data on MasterType to be locked. Is this possible to do and if so can it work dynamically so that if I update the data on the sheet, ExtractionType1, it will automatically show in MasterType.


I've included a sample file in case it helps. I'm running MS Excel 2010 and Windows 7.

Thanks in advance.

[ragulduy]

Maybe:

Please Login or Register  to view this content.

I think it works for your example.

[DawitX]

Thank you, yudlugar, for your help and for such a quick response.

The code works fine for the data currently there but if I try to add more rows to the sheet, ExtractionType1, I get errors on the Z column (sheet: MasterType) and a number of columns that follow Z-column (e.g. AA, AB, AC, AD, AE... etc). Maybe it's possible to limit the columns on the MasterType sheet to only as many columns as the ExtractionType1 sheet?

While I'm at it, is it possible to actually have the MasterType sheet to update the data without running the macro manually?

error.jpg

[ragulduy]

change this:
LCOL = Sheets("MasterType").Cells(1, Columns.Count).End(xlToLeft).Column
to:
LCOL = Sheets("ExtractionType1").Cells(2, Columns.Count).End(xlToLeft).Column
the formula I used to allow you to automatically update the data is this:
=INDEX(ExtractionType1!3:3,MATCH(A$1,ExtractionType1!$2:$2,0))
If you put that in A3 and copy across/down it should automatically update.

[DawitX]

Brilliant yudlugar! It works exactly as I wanted to. You have been a big help here!

If it's not too much to ask, is there anyway to adjust for the rowsize on ExtractionType1? I dragged from A3 (sheet: MasterType) and copied across/down as you said for the index formula and I added about 500 rows just incase I would paste a large dataset to the ExtractionType1 sheet. The problem is that once the MasterType sheet finds no data from ExtractionType1 sheet it displays zeros on every row. Is there an easy way to remove it automatically as well?

Brilliant so far!

[ragulduy]

change the formula to:
=IF(INDEX(ExtractionType1!3:3,MATCH(A$1,ExtractionType1!$2:$2,0))="","",INDEX(ExtractionType1!3:3,MATCH(A$1,ExtractionType1!$2:$2,0)))

[DawitX]

A big thank you! Everything is working perfectly now!

Merry festivities and a happy new year to you, dear yudlugar!