Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

excel dynamic/variable rows

Posted on 2014-10-27
5
Medium Priority
?
257 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 23

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 2000 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This article describes a serious pitfall that can happen when deleting shapes using VBA.
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 in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

722 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