Automatically transfer data from master sheet to others...

Hi,

I have trawled the web but found no concrete answer, and lots of similar questions but nothing the same. So I'm sorry if you have answered this question a thousand times before! I have promised the boss I can do this... but have over-promised and am in danger of under-delivering (a cardinal sin in the workplace!).

I work for an insulation company and we have all of our jobs, completed and in progress, on a master worksheet.
Currently there are 437 rows of data (but will increase), and columns A to N with various bits of data.
Row A is a location field - there are 5 locations currently.
I would like to be able to add a new line at the bottom of the master sheet, and then this automatically identifies the location from column A and which worksheet is it to be copied to and then copies the data from that new row to the bottom of the relevant location sheet.
I would also like to be able to update the data in the existing entries (e.g. when a job has been assessed initially, and then completed, I need to put the dates in) and for this to update on the relevant worksheet.

Each worksheet has the same format (columns A to N have the same headings in row 1, then data to begin in row 2).

I wouldn't say I'm an advanced Excel user (otherwise I wouldn't be asking this question), but I do have a fairly good working knowledge of it. Currently using Excel 2010. Would ideally like to be able to do it without VBA as it needs to be uploaded to Google Docs and for others in the company to access online.

Any help or advice would be greatly appreciated! I hope I have provided enough information but let me know if you would like more info!

Cheers,

Sean

Hi Pete,

Thanks for your quick reply. I have attached a version of what I am trying to achieve. As I mentioned, I would like to be able to add new lines to the master worksheet and for these to automatically copy to a new line on the sub-worksheets, and any changes to be made on the master to change on the sub-worksheets.

The info you just supplied was helpful - I couldn't quite get it to gel but with some tinkering I'm sure I might get close. The attachment should clear up what I am trying to do at least!

It may not be possible to do this without VBA, this is fine as long I can go back to the boss and say "it can't be done without VBA"!

Many thanks,

Sean

Hi Sean,

there were a couple of typos in the formulae above, which I have corrected. I've also set this up for you in the attached workbook. I've copied the formulae on the subsidiary sheets down to row 30, so you can add a few extra rows of dummy data to the master sheet (where the seq formula has been copied to row 60), and see this automatically reflected in the appropriate sheet. I made a few minor changes to the formulae, and added the sheet name to P1 in each sheet.

So, you can now go to your boss and say "it CAN be done with formulae".

Hope this helps.

Pete

Hi Pete,

This has done the trick! I have been using Excel for quite a few years and always trying to learn more so I will remember this one. Thanks so much! I had been googling the problem for a couple of days on and off to try and find a similar one without luck. Only problem is that they will expect more and more from me now!

Cheers,

Sean.

You're welcome, Sean, and thanks for feeding back.

If you have any other problems with Excel you know where to come back to.

Pete

Hi Pete/Anyone else,

This problem is solved, so thanks! But is it possible for this to work on Google Docs? I'm not a fan of using it myself, I'd much rather have them stored on a shared drive here, but they use Google Docs for everything and the code/formulae doesn't seem to work on the sub-worksheets.

I imagine I need to change the following in the sub-worksheets so that "Master!" is referred to differently??

=IFERROR(MATCH($Q$1&"_"&ROWS(A$1:A1),Master!P:P,0),"")

At the moment, the sub-worksheets are blank - the code is present, just obviously not picking anything up from "Master". Any idea of how to get this to work on Google Docs?

Cheers,

Sean

I've never used Google Docs, so I can't advise about that - this is an EXCEL forum, after all !! <bg>

Pete

DEAR PETE KINDLY HELP ME I WANT TO ALSO MAKE THE SAME EXCEL FILE WITH MY DIFFERENT DATA FORMATE AS PER MENTION BELOW

DATE | FROM STATION | TO STATION | ADVANCE | LORRY NO | tOTAL FREIGHT | ETC...

KINDLY HELP ME I HAVE USE YOUR FORMULA BUT CANNOT ABLE TO GET THE RESULT SO PLZ HELP ME

REGARDS ,
ANOOP
[email protected]

Anoop,

Have a read of the Forum Rules at the top of the page - you will find that you should not post a question in a thread started by someone else, and so I can't answer your directly. Start your own thread instead (and attach a sample workbook to show how your data is laid out and to explain what you want to achieve). Also, do not SHOUT in your posts.

Pete