Pulling data from master spreadsheet to another spreadsheet

[vas_51]

Hi All,

This has really been bugging me for the past few hours. Any advice/suggestions would be highly welcome. I use Excel 2010.

In short, I have a big spreadsheet with data regarding employees. Column A: First Name, Column B: Surname, Column C: Payroll no... etc. Column L is Region (South, South East etc.).

I have previously created several other worksheets in the same workbook based on the region but I did that by simply filtering by region and then copy+pasting it into the relevant sheet.

Is there a way of creating a link between the master and sub-spreasheet based on the region? I would like new employees to be automatically added to their relevant region sub-sheet as they are entered into the master.

Thank you in advance, please help!

Vas

[ARGK]

Hi Vas
This could be done using VBA code but we would need a sample layout.
Tony

[vas_51]

Sample Spreadsheet.xlsx

Hi Tony,

Thank you for your reply.

Please see sample of the layout attached.

TMS is the master spreadsheet and I need all of the current workers to be split into the region-designated sheets. Also, if any new workers that are added to the TMS could appear in the relevant region sheet, that would be highly useful.

Thank you,

Vas

[ARGK]

Hi Vas
Do you want to up-date the master then update the region sheets or vice versa?
Tony

[vas_51]

Hi Tony,

Please excuse the later reply. I'd like to update the master which updates the region sheets.

Thank you,

Vas

[ARGK]

Hi Vas
Please see sample attached. It works as follows:
As soon as you enter something in Column T a macto will file and copy the row into the correct region and create a link to that data. You will have to make sure that the correct Region is entered into column L or the macro will fail. Suggest you use data valildation in column L.
Hope this helps.
Good luck.

Tony

[samba_ravi]

Please find the attachment
With Helper Column in Column "U" i get the result
Please change the Sheet Name as per your requirement it will get the result

[newdoverman]

This might suit your needs. There are no helper columns used.

[vas_51]

Hi All,

Thank you so much, all of your answers really helped. I can't believe that there are so many solutions to this excel problem. If anyone has any time to even begin to explain how this works, especially newdoverman (purely because your answer was exactly what I needed), I would greatly appreciate it. I'm here to learn as well as get help.

Once again, massive kudos to all of you.

Thank you,

Vas

[newdoverman]

Here is the formula that is the basis of the solution ARRAY entered:

Please Login or Register  to view this content.

INDEX has the following arguments which produces and intersection of ROW and COLUMN (the value that you want).
1. Array - this is a range of cells from which you want to extract a value.
2. The second argument is the ROW and in this formula it is being calculated by the combination of the SMALL and IF functions. The IF part is the condition that will produce an array for the SMALL function. In this case it is looking at the range TMS!$L$2:$L$500 for matches for the value in $A$1. ROW lists all the rows to be considered. This is followed by MIN(ROW... This combination gives a starting row but it is 1 row less than required so the +1 is added. We now have a list of rows and a starting point. This is the ARRAY part of the SMALL function whose arguments are ARRAY,K . The K part of the SMALL function is determined by the ROWS($1:1). This is a counter that increases by 1 with each row the formula is dragged down, finding the smallest, second smallest, third smallest values etc. All of this provides the rows that intersect with the INDEX range, one intersection at a time as the formula is dragged down with the fill handle.

IFERROR eliminates the display of error messages when the data runs out. The ,"") at the end of the formula is what the errors are replace with which in this case is a blank cell.

This is certainly easier to do than explain

Try this formula on small examples and it should become clear what is happening.

If anyone else has something to add/correct/explain please chime in.