Formula to linking worksheets

[ITC123]

Hi,

I have the basic principle down for linking data between worksheets but I want to have data go multiple worksheets and since I have a tremendous amount of data, having to link each row of information to multiple worksheets will take a lot of time.

What I would like to know if there is a way to do is to basically create a column to use as a qualifier and as I enter each new row of information just denote it in some way shape or form, whether by using a letter or number or what have you. Then in my other worksheets have it set up so that anytime a particular qualifier is used in whichever particular cell I have set up it would then copy that entire row of information into the new sheet.

After typing that all out it sounds a lot more complex in my head. I would think it would be fairly simple that when one worksheet sees a particular piece of information in a predetermined cell that it could take all of the data in that row and copy it into the predetermined spot on the new worksheet.

Any help would be greatly appreciated.


Aaron

[teylyn]

Hello,

I think it would help if you could be a bit less general and a bit more specific. Could you attach a workbook with the basic structure of your spreadsheet and explain what you want to achieve in context?

cheers

[ITC123]

Sure thing, from the example you will see that I have a master list, i want to take certain information that has been categorized and copy it onto another worksheet. In this way when I add new information into the master list I can simply denote a category and the information will be copied in the appropriate places.

I hope this make is easier to understand what I am trying to do and I appreciate all the help, if you would like more info from me just let me know.

Aaron

[teylyn]

If you get rid of the empty row 2, you can use the autofilter feature to show only rows that meet your criteria. No need to duplicate the data in a different sheet.

Duplicating the data with formulas is not the most feasible approach. If you do want to duplicate the data, a macro would be the better way to go, for example when the Secondary sheet is activated, the data in the Master sheet is filtered by the specific criteria and then copied into the Secondary sheet.

[ITC123]

I appreciate all of the help but maybe im not explaining it thoroughly enough or maybe excel is just not capable of what i want to do. I don't want the data sorted or filtered in anyway on the master sheet. that is to be used mainly as the point of entry for the data.

What I want is somewhat of the following "for any value of (x) in column (A), the corresponding row should be copied to said location in sheet (2).


What I'm trying to accomplish is to input my data in to one central location so that I only have to input it once. These will all be sales leads with limited information. As I make a sale from a lead I want to mark it as sold, that information will then be copied to whatever new location that I need.

I basically want to create multiple data tables across the workbook for various needs. For example I want to create a checklist so that as I mark the lead sold in the master sheet that information will be copied onto another sheet that will comprise a checklist so that I can verify certain things have been completed for the customer after the sale. I will also have another sheet where i will be able to input more of the customer information.

I also want to take all of the unsold leads and have those copied onto their own sheet where I will use that information for follow up, so that in this way I can have easy access to the email addresses for all the unsold leads so that i can send them a follow up email.

Again, maybe I am asking too much from excel, i'm not really sure but i do appreciate all of the help you are giving me.

Aaron

[teylyn]

Hmm.

I think what you really wnat is a view or report of your master data based on certain criteria. Using macros or formulae to duplicate the data into different sheets is not the recommended approach.

Why do you want to copy the data that is already in the master sheet? What are you planning to do with the copy of the data? Use it for a mail merge? Use it for charting? Any of these can be done with the original Master sheet, using filters.

You may be better off with Access instead of Excel, where it is easier to create reports/views from a Master table, based on filters.

Again, it's easy to filter your master table to show just the information you want. You can then copy and paste to another sheet for further processing. It's the duplication of data that goes against the best practice approach.