Paste Special - Paste Link

**bengaluru** · 08-12-2009, 02:55 PM

Hello,

I have a Master Sales spreadsheet which gets updated when 10 of my users update their individual spreadsheet. This is done in the most simple way – Copy >> Paste Special >> Paste Link from the Individual to Master.

I have allocated 60 rows each to my sales team – such as Rows 1 to 60 to Mr. A, Rows 61 to 120 to Mr. B and like, for each month. The problem is that Mr. A may not use all the 60 rows in a particular month or may also exceed 60 rows in some months. If they do not use all the rows, my master sheet will have blank rows in the middle, but if they exceed 60 rows, row 61 and over does not show up in my Master list.

Is there a solution that my Master sheet updates the first empty row from any of the individual sheets? For example if Mr. A completes his row 7, my fist empty row should get updated and when Mr. B updates one of his rows, my next available empty row should get updated, and the like.

Is this possible ?

Thank you for your help.

**JBeaucaire** · 08-12-2009, 04:41 PM

I can imagine a technique where you go ahead and pull in the data for 100+ rows for each person on a different sheet in your workbook. Then you use some sort of MAX() formula or COUNTIF() formula to create a chart telling you how many rows of actual data are filled in on each sheet.

Sheet12  (sheet with this chart)
   A      B      C           D
Name    Rows  Cumulative  SheetRef

Mr A     45     0        Sheet2
Mr B     55     46       Sheet3
Mr C     29     101       Sheet4
Mr D     40     130       Sheet5
Mr E     10     170       Sheet6

Since the data is now in the local sheet, you can use an INDIRECT() reference to pull over the row data. So on Sheet1, your master list, let's say you start at Row2 and want the FIRST row of data from Mr A's data to come over. This is a bit of a beast, so I uploaded a sample sheet so you can see it in action. The only thing you need to add is 4 more sheets and the references to the external data.

=IF(A2="", "", INDIRECT("'" & LOOKUP(A2, Sheet12!C:C, Sheet12!D:D) & "'!R" & A2-LOOKUP(A2,Sheet12!C:C,Sheet12!E:E) & "C1", 0))

**bengaluru** · 08-13-2009, 08:51 AM

Thank you so much JBeaucaire for your time and efforts.

Honestly this is way over my head and it would take some time for me to understand.

In your example Is Sheet 1 my Master List ? What is Sheet 12 ?

If Sheet 1 is my master list why is it listing all of Dog's rows in a series and then Bird, and then Cat ? I was thinking to have it this way - if Mr. A updates his row today it would come up as first row in my Master. And if Mr. C updates next, his will be Row 2 and again Mr. A updates tomorrow, his will be Row 3 and the like.

**JBeaucaire** · 08-13-2009, 09:00 AM

Originally Posted by bengaluru

Thank you so much JBeaucaire for your time and efforts.

Honestly this is way over my head and it would take some time for me to understand.

That's why I thought the sample sheet would help you by giving something to directly examine. Hope that helped.

In your example Is Sheet 1 my Master List ? What is Sheet 12 ?

Sheet1 would be your Master, yes. Sheet12 is simply where I created a chart that watches Sheets 2-11 (you said there would be 10 sheets for the other employees) and keeps count of how many rows of data are active on all sheets.

Not demonstrated in Sheet2-11 is the fact that the cells in column A are actually a link to the other guys's workbooks. Sheet2 A1 would be =[Mr A.xls]Sheet1!A1....etc.

The Sheet12 formula is designed to count the items on each sheet that AREN'T equal to 0. You would get 0 if you linked to a cell in the remote sheet that didn't have a value yet. So this lets' you put links on your sheets2-11 that refer to 100s of cells in the remote sheets without worrying the count will be off. Sheet12 will figure out how much real data there is.

If Sheet 1 is my master list why is it listing all of Dog's rows in a series and then Bird, and then Cat ?

Because that's what you indicated you want, a complete single list of all the data in the filled out rows on each person's sheet. Without further description, ordering it from top to bottom sheet-to-sheet is logical.

I was thinking to have it this way - if Mr. A updates his row today it would come up as first row in my Master. And if Mr. C updates next, his will be Row 2 and again Mr. A updates tomorrow, his will be Row 3 and the like.

Hmm, I don't think I know of any way to do that with formulas. Since you're referring to remote sheets, having each of of them funnel individual entries into your sheet based on the moment they make the update, that's going to be a bunch of VBA, each with access to your sheet, not sure about that. Have to think about it some more.

**bengaluru** · 08-13-2009, 09:18 AM

Thanks my friend for the quick reply. To better understand my issue I am attaching a sample spreadsheet which is my Master. My 10 users have similar worksheets which they update that gets updated to my Master.

I have now allocated 60 rows for each of my user for each month.

I think the way you have put it (listing each salesman's data in a series alwo would solve my issue. Can you tell me how I do for my sheet that is attached.

**JBeaucaire** · 08-13-2009, 09:43 AM

You know, it occurs to me this may be WAY simpler to just expand your sheet to show 100 rows per employee on the one sheet. I know this will result in a bunch of zeros, but that's OK. Just do it.

So, you'll have 1000 rows of data, 100 per employee. Each set of 100 rows refers to a separate sheet. Easy so far.

Then, just turn on the DATA > FILTER > AUTOFILTER and "custom" filter one of the columns to show "does not equal zero". That should hide all the zero rows.

That should be a lot easier than 10,000 INDIRECT() formulas.