excel dynamic/variable rows

Hi
I have a spreadsheet with a reference to an external SQL data source which has a variable number of records/rows.
I want to add a new sheet which references this sheet and ensure the new sheet includes all rows.
Sheet 1
Account / Name / Address 1 / Address 2 / Address 3 / ... etc
(say 5000 records)

Sheet 2
Account / Name

Sheet 2 needs to reference all of the rows from sheet1. I believe this is achieved by using dynamic ranges but would like some help setting this up.
Thanks in advance
wsmythAsked:
Who is Participating?
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.

Ejgil HedegaardCommented:
If a formula range reference all rows (say $A$2:$A$5000) in the sql data table, the range in the formula will automatically adjust when the sql table is updated.
0
wsmythAuthor Commented:
I have no issues if the SQL table always has 5000 rows. However the number of records in the SQL table is variable.
Currently 5000 but changes daily. With a direct reference from sheet2 to sheet1, sheet2 does not display the new records.
0
Glenn RayExcel VBA DeveloperCommented:
If you have an Excel Table created as the result of a SQL connection, then you should be able to refer to the table using structured referencing and won't need to use relative referencing (i.e., cell addresses) at all.

Using structured references with Excel tables
Use structured references in Excel table formulas

-Glenn
0

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
wsmythAuthor Commented:
excellent, thanks
0
wsmythAuthor Commented:
Hi
Not sure if I can continue this as I have accepted a solution.
I have set up structured reference from my sheet2 to sheet1 whixh contains the SQL data in a table format.
If I name my table SLA then the resultant formula in sheet 2 is
=SLA[[#This Row],[ACCOUNT]]
This provides the first row in my sheet2. Real question is how to get sheet2 rows to automatically populate, matching the number of rows in my sheet1 table?
0
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
Office Productivity

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.