Dynamically linking worksheet to worksheet

Thank you for the prompt reply.

This gets me further than where i was previously.  If i select more than one worksheet (by city) which I will have set for auto filter it still dumps that data in to the incorrect city and requires me to uncheck and recheck that filter for it to filter out the incorrect data.  In the case of the worksheet with the correct city, it works fine.

Still looking for something a little more friendly and dynamic.

I'm redoing the base worksheet at the moment, give me a few and I will post it so you see what I am working with.

I hate to say this (don't shoot the messenger). You are getting into database territory. One of the tips to knowing it are that you need the same data in multiple worksheets. See "Which Application to Use" here:
http://www.officearticles.com/misc/purchasing_vba_programming_for_microsoft_office_applications.htm

I don't think access will be a viable option for me at this point, as I am not familiar with it in the least.

I will also be provided with other data dumps in Excel format  to input into this spreadsheet.

Are you saying that what I am asking is not possible?

Attached is the foundation of the worksheet i will be working with.

Hi Garry,
Let me take a crack at your sheet and if I have any questions I will post back...

Ardhendu.

Thank you, I appreciate the help.  Just holler if you have any questions.

Gary...

I wrote a macro to do what you want to..now as soon you update Column F with the worksheet name it will move the data into that worksheet automatically..the only change that i made is to bring worksheet name sync to what you are writting in column-f as in both should be exact..and it will copy the entire set of values in the worksheet automatically...as and when you write it...

Saurabh...

I don't see the macro?  Would you be able to explain a little more as to where it is?

Will i be able to add worksheets?
Update column with worksheet name?

Sorry, i'm new to the more advanced excel work, got thrown into it.

Thanks for your work so far

To see the macro..do a alt+f11 which will open the vb editor and to to the sheet module of master...

Yes you will be able to add the new sheets and again the macro is based on the concept that the column-f entry matches with the sheet name so if they do..it will automatically move that entry over that sheet...

Also you need not to do anything special..all you need to do is update column-f in your master sheet with the sheet name as in with office name which you want to update and it will automatically will move that entry to that sheet considering the name you write is exact like the sheet name as shown in the example workbook that i uploaded...

Also while opening the workbook..you need to click enable macros to make the macro work...

Saurabh...

Wow, i think you hit it right on the nose.

Is there an easy way to transfer this macro to another document?  My primary document is at work and in Excel 2007.

Yes...You can see the macro code...by doing alt+f11 over the workbook which will open the vb editor--then open the project explorer of the workbook...in that you would see a module by the name of master..double click on that module..it will take you to the vb module where the code is...you can copy the code..and follow the same steps by placeing that in your original document in the module that sheet which you want to automate...

Saurabh...

Actually, it doesn't quite work the way i had hoped, or maybe i wasn't quite clear.

Here's what else i was looking for it to do.

1.  If i update any individual field, it would update on the proper worksheet.
2.  If i delete the data, but not the row, it would delete from the worksheet.

Currently, it does not do that.

Is this possible?  Again, your assistance is already greatly appreciated.

Hmm..Yes i know i didnt designed that function that ..to design that i need to know quick things from you...

1...How Many data points you are expecting...???

2...Is there any field in your sheet which is always going to be unique and its going to be there always..??

Saurabh..

1.  Data points? Do you mean rows? ~5k or so
     Data points? Cities? < 30
2.  Yes Login, in the immediate future it will have a consistent format of XX111111 where it has 2 characters and 6 numeric.  Possible it could have otherwise in the distant future, but most likely not.

When i stated it would update an individual field it would update on the proper worksheet, i was hoping for a feel as the linking provides, dynamic data.

Then i wont recommend Excel being the best source to do what you want to since your data points is 5,000 rows and 30 columns..which is 150000 data points which would make excel go crazy and slow like anything...even if you do it by macro...it would take considerable time to excute because of the file size...

I'll look for some other options to do it..like any database like access or something...

If you could continue with the Excel that would be great.  I can break things down to significantly smaller sections, i was simply looking grand scale, but if you recommend not going grand scale that is an easy fix..

I was considering to keep all stages of this project in one document, but it is not necessary, so i can break this into individualy documents rather than building each stage.  and the chance of it actually reaching the 5k and 30 col mark is probably a far shot, so i could easily keep this project scaled down.

We have made massive progress due to your assistance so I would would hate to take backward progress.

Okay..there you go..now instead of the values if you update F Column...it will link the respective sheet to that row by formulas..and again formulas will auto update itself when you perform the other two actions which you asked..that is updating values when you edit the entry...

Saurabh...

It appears to be working just as I had hoped. It gives a VB run time error if i delete the city information for the row, but it doesn't affect the overall operation of the worksheet, so I'm more than happy with this.  I was told by my experts that this functionality could not be done, you proved them wrong.

Saurabh,

Thank you very much.  You made a complex task simple for me and did it such a short period of time.

I had just found a bug that when i entered multiple fields in a same row and in a number of cases it placed between 1 and 3 entries in the respective worksheet, but your new version doesnt do it.  It appears to be perfect.

Thanks again, perfect job!

Great...if your problem is solved..then go ahead and close the question...you can refer to following link about how to close a question...

http://www.experts-exchange.com/help.jsp#hi407

Saurabh...

Saurabh resolved my issue promptly and to 100% satisfaction.

Thanks again!

Rather then closing the question like this...click on accept as a solution on my comment which helped you and grade it...it will close the question right there and then..the current process will delete the question in 4 days and you wont be able to access it in future as well...

Saurabh...

Gotcha, i figured i did something wrong but wasnt sure how to fix

Great..Thanks for the Grade..and im finally off to sleep..so cya till next time...

Saurabh,

I'm afraid i found a bug and hopefully you will be able to fix it.  Somtimes when you add a row of data in the master, it adds multiple entries to the respective worksheet.  Once it starts to occur, the issue seems to compound.

Do you have somewhere I can send the document as it will not let me attach it.  It is in .xlsm format and zipping doesn't seem to accept either.

Please advise.

Yes ee doesnt let you attach excel 2007 documents and more over i dont have excel 2007..please change it to excel 2003..that is .xls and then upload it will go through..and it should not do that what you are saying..anwyays let me have a look..

Here you go.

I'm heading home, will be able to respond next in ~45 minutes or so.

let me know once your are back..have a couple of questions to ask...

im back

Okay..are you doing like this..that lets say you wrote CHI in f3 then you are deleting it..and writing some other office name in F3..??

I might have wrote it in a couple of times, but I did set for a data validation drop down list for city, not sure if you see that in the 2003 format.  From there i have either deleted the data or I have selected a blank entry i added to the drop down list.  I am not deleting the rows.

I have tried selecting CHI then changing it to CHA but it didnt like that, it entered the data in both sheets but didnt delete in the incorrect one.  I had to remove it for one then enter for the other and remove that to remove from both sheets.

Does that make sense?

Yes..because i didnt set that functionality because you never mentioned that you going to play with column-F as well which you are acually doing...and if you do that then im not sure about how to go for this since you dont have any unique parameter as well which i can use for reference...

you can use the Login as unique identifier, the format in most cases will be XX111111 such as SM011602

Can you open a related question for this one..as i have to redo the entire set of things again...and also have couple of queries in mind...

Saurabh...

yes, sorry.  Didnt realize it was going to evolve into what it has.

I have one more idea too, i will add that into the new question.

new question created, same title but p2