sheet reference question

[Mark CU]

I have 3 sheets, sheet one is a list, sheet 2 is a different list. sheet 3 is a master list, combination of 1 and 2. 1 and 2 have new entries all the time.

I would like as entries are made to sheet 1 or 2 to automaticaly appear in the next available row in 3. ( so we dont have manually copy and paste into sheet 3 )

Thank you

[Tom Urtis]

Right click on your Sheet1 tab, left click on View Code, and paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)
Sheet3.Range("A65536").End(xlUp).Offset(1, 0).Value = Target.Value
End Sub

Repeat the process for Sheet2.

Two points:

(1) I assume in your case that the VBA sheet code names are the same as the visible sheet tab names ("Sheet1", "Sheet2", and "Sheet3"). We can modify the code to reference the sheet tab names instead, but this way is more succinct.

(2) You did not specify if your list is the only data on Sheet1 and Sheet2. I assume your growing list in Sheet3 is in column A. The way this code is, anything you enter in any cell on Sheet1 or Sheet2 will be compiled in Sheet3. Maybe this is what you want, or maybe you don't care because all you have is data in column A on Sheet1 and Sheet2. If your situation is more complex than you first described, please repost.

[Mark CU]

the situation is not more complex. But I have not worked with VBA. So this very new to me, is the formula based way of doing the same thing ?

Thanks again

[Tom Schreiner]

If there is a formula, it would be a lot more complicated than the code given...
What ranges and sheet names do your lists reside?
The code solution is very simple if we have all the names of the pieces involved.
Thanks,
Tom

[ This Message was edited by: TsTom on 2002-05-18 07:35 ]

[Mark CU]

I tried the solution and got an error msg..."compile error...expected end sub" highlighted was IonChange (ByVal Target as Excel Range )

Thanks again...

[Brian]

It may be easier to do things in reverse.

Place all of the new data in the sheet 3 master list. The master list must have a column that identifies whether a particular item should be on list1 or list2. (Best if this is in the first column, labelled "List", I'll assume "1" or "2" is placed in that column. Place the row number in column2 (you can hide that column) Use "=row".

There are then a number of methods to create list1 and list2.

1) You may not even need to have separate lists. Use an autoflter: Select the row of titles and then select Data>Autofilter. This will add a drop-down arrow at the top of each column. Click the first one (list) and select "1". The master list will then be filtered so that it only shows rows that have "1" in the sheet column. You can use the subtotal function to perform calculations on only the visible data (see the help). Or you can use the sumif or countif or array formulas, if you want the calculations fro both lists to be available at the same time. (Place these calculations in the rows above your filter row, or on another sheet.)

2) Use a pivot table to create lists1 & list2

3) If you really need separate lists, then you can use a lookup function.
In the first row of the list, place "1" in the first column of the first data row, and place place "0" (zero) in the first data row, column 2 Assuming row #2:

a2 = 1 (change to 2 for list 2)
b2 = 0

Assuming that all lists have the same columns, place the following formula in A3 and the drag to fill the rest of the data table.
A3 =VLOOKUP($A$2,INDIRECT("MasterList!"&($B2+1)&":$65536"),COLUMN(),0)

or (to get rid of #N/A! at bottom of table) use:
=IF(iserror(VLOOKUP($A$2,INDIRECT("MasterList!"&($B2+1)&":$65536"),COLUMN(),0)),"-",VLOOKUP($A$2,INDIRECT("MasterList!"&($B2+1)&":$65536"),COLUMN(),0))

Periodically, you will need to ensure that the formulas are copied down far enough. I use the "-" instead of "" so that I can see that there are enough formulas.

I recommend a checksum on the list sheet to ensure that the lists match.

on the list sheet, enter:
C2: =COUNTIF($A$3:$A$65536,2)-COUNTIF(MasterList!$A:$A,2)
D2:=COUNTIF($A$3:$A$65536,"-")
A1: =IF(OR($C$2<>0,$D$2<2),"ERROR - Drag down formulas to more rows","OK")

4) DGET probably works too

5) Array formulas may work and eliminate the need to copy down formulas, but this is more complex.

6) VBA

Cheers,

Brian

[Brian]

I tried the previously suggested code. That works if you only have one column and never change data, but I dought that is what you have.

The suggested code always places the new data at the bottom of ColA. Therefore multi-column data turns into one column. And any edited data just becomes another new data point at the bottom of the column.

[Tom Urtis]

Brian --
That is exactly what I meant in point #2 of my post, regarding my code suggestion.

Mark CU --
Is this still unresolved for you? If so, please repost and one of us can assist.

[Brian]

Sorry Tom, I did not read all the text of your post.

Another person just asked a very similar question at:
http://www.mrexcel.com/board/viewtop...8728&forum=2&3