Separate data into different sheet and link

[nickh1981]

Dear all expert,

I had a master file contains all the data. I want it to be separated into different sheet base on the 12NC no. automatically.

Attached here with the excel file. In master sheet i have all the data. Is there a way that excel can automatically help me to separate those data into different sheet base on different 12NC no? In attached file i have show the result how i want it to be separated "sheet 2-4"

After that, i will assign a drop down list box at master sheet to allow me to select the 12NC. Once the 12NC selected, it will bring me to the 12NC sheet that i selected. Example in column "I5"

Can this be done with formula? or Macro is needed?

[AlKey]

Hi nickh1981,

Please see attached file. The data is separated by formulas are on sheet1, sheet2 and sheet3

[nickh1981]

Hi AlKey,

Thanks for the formula. My original file contains 90 different 12NC, is that means that i have to create 90 sheet one by one and insert the formula. Is there a better way of doing this?

[nickh1981]

If i need to manual key in all the formula sheet by sheet i might just filter the master file then copy & paste to the sheet will be easier.

Is there any other way to do it, perhaps with macro?

[nickh1981]

Anyone can help on this?

[nickh1981]

No one can help?

[AlKey]

You never mentioned in your requirements that the data would have to be split in 90 Sheets! I think maybe someone could offer a VBA solution.

[Pete_UK]

There is no real need to have 90 different sheets - why not have one sheet, on which you can select the number that you are interested in, and have the data automatically appear for that number?

That is what the attached file does with just three simple formulae.

I've created a new sheet called Query, and used cell J1 (yellow) to enable you to select the 12NC number from the list in column N. This is very similar to what you had in the Master sheet, and if you have more than 3 numbers to choose from then you can just add them to the bottom of column N and adjust the range for the data validation in cell J1.

I've inserted a new column A in the Master sheet and put this formula in A2:

Please Login or Register  to view this content.

This is copied down beyond your data (I've just copied to row 50 - the hyphens indicate how far), and it sets up a unique sequential number for each record that matches the 12NC# selected in J1 of the Query sheet. If you are satisfied that this formula has been copied down far enough to accommodate extra data in future, you can hide this column so that your sheet looks exactly the same as before. I've used the same layout on the Query sheet, with this formula in A2:

Please Login or Register  to view this content.

This finds the row in the Master sheet where the first matching record occurs. When the formula is copied down it finds the row of the second matching record, then the third, and so on. Again, hyphens indicate how far down the formula has been copied - I've just copied it to row 20, but you should ensure that it has been copied down far enough to accommodate the maximum amount of data you are likely to need for any given 12NC number.

Then I have this formula in B2:

Please Login or Register  to view this content.

which just gets the data from column B on the Master sheet on the row given in column A. This formula is copied across to H2, and appropriate formatting applied to each cell, and then the block of formulae in A2:H2 can be copied down to row 20.

Now, to use the file just change the number in cell J1 and the data will automatically change. It takes a lot longer to describe than to use.

Hope this helps.

Pete

P.S. If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, I would like to inform you that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

[:) Sixthsense :)]

Try this...

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

Please Login or Register  to view this content.

[nickh1981]

Hi Pete,
Thanks for the suggestion. The reason i want to separate it into different sheet is to allow me to update the data easier. The data will be update daily, so instead of update everything in the master list, i just need to update on the 12NC sheet that i want. Anyway, your formula is good for displaying all data separately in one sheet.

Hi Six,
I apply your vb code to the file, but it has a run time error 5. Can you help me to find out where i have done wrong? Attached here with the file.

[:) Sixthsense :)]

I downloaded your file and the macro is working fine… Can you please click debug and take a screen shot on which line it is showing error?

[soledad]

Plz find my file attached

[nickh1981]

macro error.jpg

Hi Six,

Attached with the error print screen.

[nickh1981]

Great work soledad!!! Thanks.

[:) Sixthsense :)]

@ nickh1981,

Thanks for the screenshot file.

Try the below code...

Please Login or Register  to view this content.

[nickh1981]

Hi Six,

Works great. Thanks for the sharing.

[:) Sixthsense :)]

Glad it helps you and thanks for the feedback