Solved

excel dynamic/variable rows

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

Connect further...control easier

With the ATEN CE624, you can now enjoy a high-quality visual experience powered by HDBaseT technology and the convenience of a single Cat6 cable to transmit uncompressed video with zero latency and multi-streaming for dual-view applications where remote access is required.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Problem to line 23 53
get the ALL CAPITAL words from a cell 4 19
populate  some cells after data verification 45 18
Open source office 5 16
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

791 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