Update Sheet depending on Main Sheet Data

Hi guys

I am trying to make a template in excel need help on that.
If I add data on the main sheet it will update the data on other sheet depends on the Team Name.
please find attached the sample template . The other sheets should be updated when we add or edit any data in the Main Sheet. It should updated according by team name in Column F in the main sheet.

thanks

surah
New-Template.xlsm
surah79Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Glenn RayExcel VBA DeveloperCommented:
Without using VBA, I thought of a couple of methods.

1) Create sheets that display duplicates of the Main sheet data only if the team name is the same for that sheet and then filter out the blanks.  For example, the "SY" sheet would have formulas testing if the same row on Main had "SY" in column F, and display the related value on Main, otherwise leave the cell blank.  So on the SY sheet:
A2: =IF(Main!$F2="SY",Main!A2,"")
B2: =IF(Main!$F2="SY",Main!B2,"")
...and so on to column L

You'd want to copy these formulas well down the sheet.  Then you'd pick any column and filter out the blanks (team name would be ideal). The disadvantage is that you would have to refresh the filter on that sheet to pick up any changes on Main.

2) You could create an array function (in a hidden, helper column) that would return the largest position values of the team name for that sheet, then use INDEX functions to display in the table.

In this example (again for team SY), you'd add a helper column in M with this formula in cell M2 (using [Ctrl]+[Shift]+[Enter]):
{=LARGE(IF(Main!$F$2:$F$500="SY",ROW($F$2:$F$500)-1,0),ROW()-1)}

Then the table formulas would look like this:
A2: =IF($M2>0,INDEX(Main!A$2:A$500,$M2),"")
B2: =IF($M2>0,INDEX(Main!B$2:B$500,$M2),"")
...and so on to column L.

The advantage here is that no refreshing is required.  However, the items appear in reverse order than on the main sheet (bottom-most items appear first).

See the attached workbook for both types.

Regards,
-Glenn
EE-New-Template.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Excel amusantCommented:
Please find attached your file, click on MACRO button to get the desired output.
New-Template.xlsm
Glenn RayExcel VBA DeveloperCommented:
^The VBA option is good, but you'll need to ensure that your Team values are EXACTLY the same as the sheet names they are to be copied to.  

In the original source file, "AE" is followed by two spaces (value = "AE  ") and the macro will halt because it doesn't match the sheet name.

The fix is easy:
1) Clean up your source data
2) Modify this line of the VBA code
DestinationSheet = ActiveCell.Value

Open in new window

with this
DestinationSheet = Trim(ActiveCell.Value)

Open in new window


Regards,
Glenn
surah79Author Commented:
thanks a lot for this.
Excel amusantCommented:
You welcome Surah.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.