Solved

excel dynamic/variable rows

Posted on 2014-10-27
5
242 Views
Last Modified: 2014-10-28
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
0
Comment
Question by:wsmyth
  • 3
5 Comments
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 40407295
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
 

Author Comment

by:wsmyth
ID: 40407333
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
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40407378
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
 

Author Closing Comment

by:wsmyth
ID: 40407409
excellent, thanks
0
 

Author Comment

by:wsmyth
ID: 40408794
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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article will shed light on the latest trends when it comes to your resume building needs. For far too long, the traditional CV format has monopolized the recruitment market.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question