Referencing data between sheets in excel 2013/2016

I have a spreadsheet with a list of accounts. (many 000's) Each row has quite a large number of cells and I would like to create a summary sheet showing every row but with only a small number of cells. On the face of it very easy but I need to have it dynamic based on the number of rows.
Eg
Sheet 1
name, add1,add2,add3,add4,postcode,telephone

Sheet 2
name, telephone

simple to set up a reference in sheet 2 along the lines of "sheet 1!A1"
Manually I can copy this formula down the rows of sheet 2 to match the number of rows in sheet1
Question
Is there a way to have the reference "dynamic" so that when a new row is added to sheet 1, sheet 2 will be correctly populated.
William SmythAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

you could use this to fill rows in advance

=IF(sheet 1!A1<>"",sheet 1!A1,"")

Open in new window


Regards
Saurabh Singh TeotiaCommented:
You can basically use this formula in sheet2..

=Vlookup(Sheet2!A1,Sheet1!A:G,7,0)

Assuming you enter name so for that name it will pick up the telephone number automatically even when you add new values...

Saurabh...
William SmythAuthor Commented:
A possibility but sheet 1 is linked to an outside data source which is refreshed on opening excel. There are situations where another 500-1000 rows could be added. It is this dynamic nature that has me looking for a better solution.
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Rgonzo1971Commented:
Hi,

you could use this in the sheet 2 module when activating the sheet (it fills down to the last row of A column)

Private Sub Worksheet_Activate()
    lastRowColA = Sheets("Sheet1").Range("A" & Cells.Rows.Count).End(xlUp).Row
    Me.Range(Range("A1"), Range("A" & lastRowColA)).FillDown
    Me.Range(Range("B1"), Range("B" & lastRowColA)).FillDown
End Sub

Open in new window

William SmythAuthor Commented:
Hi Saurabh
I do not wish to enter anything in sheet 2. It will be automatically created from sheet 1 data.
William SmythAuthor Commented:
Hi Rgonzo1971
Is VB the only way to achieve this result? The actual data is from a general ledger with over 100 columns. Is it just a matter of referencing each column?
Rgonzo1971Commented:
How many columns in sheet2
Could you send a dummy?
William SmythAuthor Commented:
I have attached a test excel file. Test has actual columns but only 25 rows. Could be several thousand rows
test.xlsx
Rgonzo1971Commented:
HI,

pls try ( the zeros are due to the table greater than the filled data)

Private Sub Worksheet_Activate()
    lastRowColA = Sheets("NLA").Range("A" & Cells.Rows.Count).End(xlUp).Row
    Me.Range(Range("A2"), Range("B" & lastRowColA)).FillDown
    Me.Range(Range("D2"), Range("O" & lastRowColA)).FillDown
End Sub

Open in new window

EE20151104_1.xlsm
William SmythAuthor Commented:
I have pasted the VB code in sheet 2 but am not getting any values returned. Could you be so kind to enter it on my example and upload?
Many thanks
Rgonzo1971Commented:
the code should be in the P&L sheet
William SmythAuthor Commented:
Hi Rgonzo1971
Still struggling to get this working. I have pasted your code into the module / activation on the P&L sheet.
Rgonzo1971Commented:
Does it work on my example

For the code to fire pls select another sheet than the P&L one again

Or insert in the thisworkbook module
Private Sub Workbook_Open()
    lastRowColA = Sheets("NLA").Range("A" & Cells.Rows.Count).End(xlUp).Row
    Sheets("P&L").Range(Range("A2"), Range("B" & lastRowColA)).FillDown
    Sheets("P&L").Range(Range("D2"), Range("O" & lastRowColA)).FillDown
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
William SmythAuthor Commented:
Hi
I have tried code on both sheets and a new sheet with no result.
Also tried in the workbook module.
Rgonzo1971Commented:
But does it work on my example?
William SmythAuthor Commented:
Only file attachment I can see is my test.xlsx file. How can I access your example?
Thanks
William SmythAuthor Commented:
That's it working in your example so I am sure I can identify what I am doing wrong.
Any thoughts on how to stop the P&L sheet being populated with zeroes?
Rgonzo1971Commented:
the table must have all the rows filled surely in your file ( not your dummy) that's the case

EDIT
if not change
    lastRowColA = Sheets("NLA").Range("A" & Cells.Rows.Count).End(xlUp).Row
to
    lastRowColA = Sheets("NLA").Range("A1").End(xlDown).Row
William SmythAuthor Commented:
Many many thanks for your excellent help. Best yet on experts-exchange!
William SmythAuthor Commented:
Exceptional help. Knowledge and communication second to none.
Rgonzo1971Commented:
You're welcome
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.