Automatically update the contents added on Sheet 1 to Sheet 2 & 3

[lifeisaspreadsheet]

I need some help to be able to automatically update the contents added on Sheet 1 to Sheet 2 & 3.

I've in Column A Cell 45 the name of an auto driver with some particulars in Row 45. The tracking system is such that there are 2 other related reports that need to have contents of a driver added to sheet 2 and 3.

Now the row number is not the same as Sheet 1 in both.

All I need is to be able to get the first 3 points (Drivers name, age and license expiry date right at the bottom.

Example: If sheet 1 has data in Cell A45, B45, C45. I would like it to be returned on sheet 2 and 3 right below the last cell with data, so if there the last data appears in A33. Then those 3 items out of row 45 should get updated in sheet 2 and 3 in A34, B34 and C34.

Hope I've been able to describe my problem in detail.

Please assist!

[Pete_UK]

You can do this with a few formulae, and that will automatically update itself when a new name is added. Post an example workbook and I'll show you how.

Pete

[lifeisaspreadsheet]

Sure, will do that in a couple of hours as not the system!

Thanks!

[Pete_UK]

Well I'm about to go to bed now, so I'll pick it up tomorrow (later today).

Pete

[lifeisaspreadsheet]

Sure sir, its like 6:00am here

Good night!

I'll post and lookout for our answer later tmrw.

Thank you!

[lifeisaspreadsheet]

Hey Pete,

Here's a snapshot from my report. There's a lot that will go into these sheets from our Cabie.com database, but to begin with I need to get this running.

So each time I enter a new agency name in Column C. I would like to get the agency name updated in sheet 2 & 3 as well, but if a duplicate agnecy name is added it shouldn't be done else it will create multiple enteries. For example: In the attached Doortsep Services has 2 drivers but I would only need Doortsep Services once in Sheet 2 & 3 else it will mess up my calculations...

Similarly I would like to get the Drivers name Automatically updated from Sheet 1 to Sheet 2 & 3 (Please see highlighted in green)

Look forward to your expertise. Thanks in advance!

Best Regards,

[lifeisaspreadsheet]

Hi Pete,

Just wondering if you got a chance to look into this request of mine...

Please advise!

Kind regards,

[Pete_UK]

Attached file shows how it can be done with formulae (copied down to row 20 in sheets 2 and 3).

Hope this helps.

Pete

[lifeisaspreadsheet]

Hi Pete,

This was awesome! Thanks a bunch for all your efforts and great help:-)

One more request....

Would it be possible to give me a brief explanation of the magic you did on my request (obviously if its not too much that I'm asking for)?

It will help me assist others as well, if need be.

Thanks a ton again!

[Pete_UK]

The formulae in columns M and N can be copied down as far as you need them (I copied to row 49) and basically they set up a sequential count for each unique name for Driver and Agency - you can see that the second occurrence of Doortsep Services does not get included in the list. So, what we need in order to be able to retrieve this data in the other sheets is some function which will increment when we copy it down. The ROWS($1:1) function will do this, as initially it will return 1, but when it is copied down the function becomes ROWS($1:2), then ROWS($1:3) and so on, returning 2 and then 3 respectively.

So, in sheet2 the MATCH function in column A tells us which row the data occurs on, so it can be used with an INDEX function in column B to get the Agency names. Other formulae on that sheet make use of the SUMIF function, as there may be a number of values on Sheet1 that we want to add together. The important thing here is that they are offset slightly from the range to be summed, i.e. in F2 we have SUMIF(Sheet1!L$3:L$46,Sheet2!B2,Sheet1!J$4:J$47), so we are summing the range J4:J47 while looking at the range L3:L46.

Sheet3 basically makes use of INDEX and MATCH functions.

Hope this helps.

Pete

[lifeisaspreadsheet]

Awesome!

I'm falling short of words. But all I can say is "BIG THANK YOU"

I'll mark it as "Solved" right away.

You rock!