# 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

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.
###### Who is Participating?

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.

Commented:
Hi,

you could use this to fill rows in advance

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

Regards
Commented:
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...
Author 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.
Commented:
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
``````
Author Commented:
Hi Saurabh
I do not wish to enter anything in sheet 2. It will be automatically created from sheet 1 data.
Author 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?
Commented:
How many columns in sheet2
Could you send a dummy?
Author Commented:
I have attached a test excel file. Test has actual columns but only 25 rows. Could be several thousand rows
test.xlsx
Commented:
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
``````
EE20151104_1.xlsm
Author 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
Commented:
the code should be in the P&L sheet
Author Commented:
Hi Rgonzo1971
Still struggling to get this working. I have pasted your code into the module / activation on the P&L sheet.
Commented:
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
``````

Experts Exchange Solution brought to you by

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

Author Commented:
Hi
I have tried code on both sheets and a new sheet with no result.
Also tried in the workbook module.
Commented:
But does it work on my example?
Author Commented:
Only file attachment I can see is my test.xlsx file. How can I access your example?
Thanks
Commented:
Author 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?
Commented:
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
Author Commented:
Many many thanks for your excellent help. Best yet on experts-exchange!
Author Commented:
Exceptional help. Knowledge and communication second to none.
Commented:
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.