Auto populate cells if referencing sheet changes.

[kenshih]

So the question is pretty straight forward. I have 2 sheets. One sheet is the one that contains underlying data. Let's say data range is 2 by 10 (columns by rows). If I were to repopulate the underlying data sheet and the data range changes (say 2 more rows) how can I get the sheet referencing this data to automatically increase by 2 rows as well? Do I need to write a macro for this? Any ideas would be greatly appreciated. Thanks!

Ken

 

[Ronald Moore]

If I understand your need (and I'm not totally sure of that), it seems like a better title for your post would be "referencing a dynamic data range." If so, the OFFSET function in conjuction with COUNTA can be used for this. Normally, for brevity in formulas, a name for the dynamic range is defined. For example, if your data begins in cell B2 on Sheet1, define a name for the dynamic range by choosing Insert>Name>Define. In "Names in Workbook", enter a name, say Data, and in "Refers To", enter

=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B$2:$B$50),2)

You may want to adjust the "50" to accommodate the maximum number of rows expected. Now you can use Data just as any other named range. This assumes that no blank cells are interespersed among the cells with data in B2:B50. In other words, if you remove a row, delete it rather than clearing contents.

 

[kenshih]

referencing a dynamic data range

Hey Ronald,

Thanks a lot for your help. I think this is pointing me the right direction but it not exactly what I was looking for. I am actually looking for Excel to automatically insert new rows or columns in my main sheet if the range of data on my other sheet were to change. Reason being I am referencing multiple sheets and my breakdown on the main page will be listed my results. Instead of leaving additional cells blank between the breakdowns it would be nice if Excel could recognize the change and add columns or rows accordingly with the new data. Is there any way to do that? Thanks again for your help.

Ken

 

[Ronald Moore]

Ken,

As I feared, I didn't understand what your real need was, so my first response won't be of any use to you. If you're comfortable using a VBA macro, I think your requirement can be satisfied, if it's simply a matter of inserting enough rows to accommodate a dynamic range (of arbitrary width). However, if you might need to insert columns also (shifting some existing columns to the right), that might complicate things a bit. If you want to pursue this, reply back and I'll see what I can do.

Ron

 

[kenshih]

Ken,

That's exactly what I need (rows and columns). I've only just started to learn some VB code online so I'm pretty new at this. I am very comfortable with Excel but I guess to become a true power user maybe I should take some VB classes as well. Any help would be greatly appreciated. Thank you so much again!

Ken

 

[Ronald Moore]

Ken,

It would be of benefit to you to learn some VBA for Excel, if it is permitted by your organization or you use it for your own purposes only. A word of warning, though. With the power to do great things also comes the power to do great harm. Always make sure you are backed up adequately before running a macro on important data, in case something goes wrong. Since you're fairly new to macros, I would recommend creating a simple scaled down version of your workbook to try the code I'm providing.

The areas on the main sheet where your tables belong, and the particular table which is to be copied into each area, must be specified in some way. I've used a scheme with named ranges which I think will be general enough to handle your needs. The tables will be numbered consecutively starting at 1, to allow convenient indexing by the code. Associated with each table will be three named ranges. For the first table on your main sheet, these will be TBL1, UL1, and LR1. Go to the worksheet which contains the first table, select its top left cell, then type TBL1 in the name box (just above column A). On the main sheet, select the cell which will contain the upper left cell of
the copied table, and enter UL1 (UL meaning "upper left") in the name box. This probably won't be immediately clear -- select the cell at the intersection of the first column and first row which must be shifted if required by table growth, and type LR1 (LR meaning "lower right") in the name box.

insert_tables.xls
	A	B	C	D	E	F	G	H
1
2		X	X
3		X	X
4
5
6
7
Main

In the diagram above, the bordered range is that currently allocated for Table 1 on the main worksheet. [EDIT: the cell borders didn't get through the HTML maker. The range is B2:D4]. Cell B2 (blue) is named UL1. The X's denote some data, so that the old Table 1 is 2 rows by 2 columns. Column E (yellow) and row 5 (green) are the first column and row which must be shifted if required by table growth. In this case, cell E5 (red) would be named LR1. When the macro is run, if the old table is to be replaced by an updated version with at most 3 columns, no columns will be inserted by the macro; similarly, if the updated version has at most 3 rows, no rows will be inserted. If the updated version has 4 rows and 5 columns, for example, then the macro will insert two columns at column E and 1 row at row 5, and copy the updated table so that the picture would then look like this:

insert_tables.xls
	A	B	C	D	E	F	G	H
1
2		X	X	X	X	X
3		X	X	X	X	X
4		X	X	X	X	X
5		X	X	X	X	X
6
7
Main

Note that after the shifts the red cell, now at G6, still retains the name LR1.

The next table and its desired location is defined in a similar way, with names TBL2, UL2, and LR2; and so on up to as many tables as needed.

To place the code below in your practice workbook, Hit Alt-F11, then Insert>Module, and then copy and paste the code into the new module. I've assumed that your main sheet is named "Main", so you may need to edit the line which I've identified with a comment. The constant iNumTables gives the number of tables the macro will update. Begin with a small value here, say 1 or 2, and see if the general idea seems workable for you. If so, then edit and change to a larger number for larger examples.

Note that one assumption I've made is that each source table consists of a single region -- i.e., there are no entirely blank columns or rows in the table.

I don't know if you want to consider the possibility of decreasing table sizes and possibly eliminating blank rows or columns after such an update. I'll wait on that until you finish your initial assessment of this scheme.

Sub UpdateTables()
  Dim rNewTable As Range
  Dim iLRRow As Integer, iLRCol As Integer
  Dim iNumRows As Integer, iNumCols As Integer
  Dim ws As Worksheet
  Dim i As Integer, j As Integer
  
  Const iNumTables As Integer = 1  'number of tables to update
  
  'if necessary, change next line to use name of your main worksheet
  Set ws = Sheets("Main")
 
  For i = 1 To iNumTables
    'clear out old table
    Range(Range("UL" & i), Range("LR" & i).Offset(-1, -1)).ClearContents
    iLRRow = Range("LR" & i).Row    'row number of lower right anchor
    iLRCol = Range("LR" & i).Column 'column number of lower right anchor
    'calculate number of rows and columns currently allocated for table
    iNumRows = iLRRow - Range("UL" & i).Row
    iNumCols = iLRCol - Range("UL" & i).Column
    Set rNewTable = Range("TBL" & i).CurrentRegion 'updated table to be copied
    'if necessary, insert new rows and/or columns to accommodate updated table
    For j = 1 To rNewTable.Rows.Count - iNumRows
      ws.Rows(iLRRow).Insert
    Next
    For j = 1 To rNewTable.Columns.Count - iNumCols
      ws.Columns(iLRCol).Insert
    Next
    'copy new table to main sheet
    rNewTable.Copy Destination:=Range("UL" & i)
  Next
End Sub

Ron