Solved

excel dynamic/variable rows

Posted on 2014-10-27
5
232 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 20

Expert Comment

by:Ejgil Hedegaard
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
excellent, thanks
0
 

Author Comment

by:wsmyth
Comment Utility
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

A high-level exploration of how our ever-increasing access to information has changed the way we do our jobs.
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

772 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now