Extend Range Of Table Automatically To Fit Data

Jul 14, 2014

I have two data tables (Table1 and Table2) on two different sheets (Sheet 1 and Sheet 2). In Col1 of Table2, I have "copied" the values of Col1 in Table1 by using a simple equal (=) formula. Secondly, I have an autofilter on Table2 and a macro that automatically updates the filter when the sheet (Sheet 2) is selected.

My problem is that I would like Table2 to be more dyanmic, i.e. I don't want to change the range of the Table2 each time I add, or subtract, a value from Table1.

Could a OFFSET formula be employed in any way?

I have attached an example file.

The macro is:

[Code] .....

ExpandTableExample.xlsm‎

View 1 Replies


ADVERTISEMENT

Extend Formula Automatically As Data Entered

May 21, 2008

I found the following code here.

For data entered into column A, it copies the formulas from columns B:E in the row above to the current row.

It works great except fot the first row (A9) where it copies the header row (B8:E8).

How can I get it to not copy when data is entered into A9?

Private Sub Worksheet_Change(ByVal Target As Excel. Range)
Dim c As Range, i As Long
On Error Resume Next
Set c = Intersect(Target, Columns(1))
If c Is Nothing Then Exit Sub
If IsEmpty(c.Offset(-1, 0)) Or Not IsEmpty(c.Offset(1, 0)) Then Exit Sub
i = c.Row
Application.EnableEvents = False
Range("B" & i - 1 & ":E" & i - 1).Copy Range("B" & i & ":E" & i)
Application.EnableEvents = True
On Error Goto 0
End Sub

View 4 Replies View Related

How To Set Table Range To Automatically Adjust To Source Data

Oct 17, 2013

How or if it is even possible to have a table automatically adjust its range or the number or rows it has based on another table

I have two tables:

Table1 is raw data that is added two daily

Table2 has formulas with column header references to Table1 that processes the information and converts it to a readable format. (basically barcodes to words)

Is there a way to get Table2 to automatically resize to the same number of rows that Table1 has whenever data is added??

View 2 Replies View Related

Automatically Extend Formulas

Sep 6, 2007

Right now I have a spreadsheet where I have a person enter in 4 differant values in 4 adjacent cells of a row, then in another 4 adjacent cells of the same row calculations are done with respect to the 4 rows of entered information then displayed. Is there a more effective way in placing and copying these formulas in the last 4 adjacent cells rather then just copying the formula into the first 3000 or so rows of cells?

I say 3000 because each project is differant and will require a differant amount of rows, but by copying the formula, even little IF statements, slows down the program and makes printing a hassle.

View 9 Replies View Related

Pivot Table - Automatically Expand Range Selection Of Data

Oct 12, 2002

Possible to get a pivot table to automatically expand the range selection of data as it grows. I have a worksheet that grows daily and I am running a pivot table from this. If I refresh the pivot table it doesn't pick up new data unless i change the range or i have a huge range selected in the first place. Problem is if i select a huge range then the grouping options i am using won't work correctly.

View 5 Replies View Related

Automatically Extend Pie Chart To Include Categories When They Have Values

May 29, 2014

I have a pie chart on a pre-defined table with 15 categories where the data will populate over time (presently only 3 are populated) in descending order. As some categories will be blank/zero for a while, I'm trying to find a way to avoid showing the blanks/zeros on the pie chart. As I am trying to display the legend on the pie chart segment, any zeros cause the remaining labels to appear grouped together and rather messy. I would therefore like to hide the zero data from the pie chart so the labels do not appear, or to automatically extend the range to only include all non-zero values.

I found the following formula which, when used as a named range, automatically extends a list for data validation:

[Code] ....

I don't know whether there's a similar formula or whether a few lines of VBA are required to run whenever the chart or data worksheet are selected..?

View 2 Replies View Related

Extend Range To Longest Column

Aug 7, 2007

I had asked about automatically naming regions and this is an extension of that post because it's closed. The code we ended up with to name the region is:
Dim sNm As String, sRT As String

If Intersect(Target, Rows(1)) Is Nothing Then Exit Sub '------------------->
If Target.Count > 1 Then Exit Sub '---------------------------------------->

sNm = Replace(Trim(Target), " ", "_")
sRT = "=offset(" _
& Target.Address _
& ", 1, 0, counta(" _
& Cells(2, Target.Column).Resize(Rows.Count - Target.Row).Address & ") )"

ThisWorkbook.Names.Add Name:=sNm, RefersTo:=sRT

My problem now is that I need to have the regions be the length of the longest column. I've tried using a few different ways using the worksheet range but I can't seem to get it to work.

View 9 Replies View Related

Isnumeric Function - Getting Range To Extend By 1 If Not Numeric

Apr 29, 2014

So I found a great piece of code which calculates relative strength index. However, I've been trying to edit it to make it a bit more dynamic and I'm really struggling to get it to work.

Right now the initial code is:

Code:
Function RSI(MyCells As Range)
Dim up_day, down_day
Dim average_up, average_down
Dim RS, cellcount As Long
Dim cll As Rangeups = 0up_day = 0downs = 0down_day = 0cellcount = 0For Each cll In MyCells

[Code] ..........

Basically I tried to amend this as follows:

Code:
Function RSE(MyCells As Range, Length As Double)Dim up_day, down_day, ups, downs
Dim average_up, average_down
Dim rs, cellcount, rangecount As Long
Dim cll As Range
ups = 0

[Code] ..........

Basically i wanted it to look at the numbers in the range and then error check to see if it was a number... if it was a number great. If not then the length of numbers it should calculate across should increase by 1 as it has to include another number to calc across (hope this makes sense). however, i just cant get it to work at all. My code doesn't seem to make any difference. And it also raises the issue of the fact that the intial value for length also needs to be stored to do the final calc.

View 1 Replies View Related

Automatically Update Pivot Table Range

Nov 22, 2006

I have existing Pivot Tables that have a data source on another sheet that varies in size.

How can I have the pivot tables' data ranges reset to match the number of columns and rows on the data sheet?

View 3 Replies View Related

Excel 2007 :: Formula Range Auto Extend Doesn't Work?

Jul 31, 2014

I am using office 2007 and here is the problem I am facing. I am using a formula and it is based on two columns data. The formula result is at C20:C2400, while the two data columns are at A20:A2400 and B20:B2400. I add one more row of data at A2401 and B2401, I expect the formula result would auto extend to C2401 but it doesn't, nothing happen.

I check that I need to turn on the auto extend check box in option, I check and it is already on.

View 3 Replies View Related

Pivot Table Fields Expands Automatically When Updating Data (only Where Data Has Been Changed)

Jan 11, 2013

I have created a pivot table that is connected to an input sheet with data. The input sheet retrieves data automatically from a external source through an add-in to Excel. When updating data the fields expands, but only for the items which have been changed. I want the table to be updated automatically, but not the fields expand automatically. Is there any pivot options to prevent this problem?

It should be mentioned that the pivot table is not directly connected to the input sheet (which is updated from the external source), but from a "help-sheet" reflecting the input sheet with some additional columns. I use conditional formatting and name range in the pivot.

View 1 Replies View Related

Extend Formula Each Time Data Entered In Column Of Next Row

Nov 30, 2012

I have a spreadsheet that requires a formula in column "e". How can I automaticlly extend the formula each time data is entered in column "d" of the next row.

View 7 Replies View Related

Excel 2010 :: VBA Code To Extend Formula To (End Of Data)

Mar 4, 2014

I created the following macro (using the recorder), and now need to modify it so that the formula extends to the end of the data (and no further). I would also like to include a total at the end.

Sub ExcessUsage2()
' ExcessUsage2 Macro
Rows("39:250").Select
With Selection.Font
.Size = 8
.Strikethrough = False

[Code0 ....

View 8 Replies View Related

Extend Formula As Many Data Rows As Referenced Column

Oct 31, 2006

I am after an automatic formula or function which calculates values for long lists instead of me dragging down the formula all the way to the end of the document.

Hence, I have a long list of data in columns A, B&C and I want the formula in column D to automatically be calculated all the way down when the list stops.

View 9 Replies View Related

Automatically Create List / Table From Data That Changes Often

May 30, 2013

I have a client list/table that gets new data each day. (ALL) - see file

I need to create separate lists/tables on new sheets from the main data sheet (ALL) base on certain conditions/criteria...

On the (ID) sheet I need all the clients that have ID numbers to display, on the (Reg no) sheet I need all the clients with Reg numbers to display... etc.

Like I said that the main data sheet grows each day and the lists/tables on the other sheets needs to automatically include all the new clients that's entered.

Is there a way to do this using formulas or do I need code to do this?

View 2 Replies View Related

Automatically Include New Data In Pivot Table

Jun 10, 2008

I have a workbook that automatically creates a new sheet based on the current day. There is a pivot table in the workbook that gets values from multiple consolidation ranges.

Right now I have to manually add the ranges from the new sheet every time. I need that pivot table to update with the same ranges from the new sheet every time a new sheet is created.

There is also another sheet (named "Data Results") that needs to update too, but that's not as urgent.

I've searched everywhere on the forums and can't find anything like that. When the file opens, I've written script to have a box pop up asking whether you want to "View Data" or "Begin New Audit." If you click "View Data" nothing happens and you can just view the file. If you click "Begin New Audit" it creates a new sheet based on the current date.

I wanted to attach the file, but it's an Excel 2007 macro-enabled file (*.xlsm).

View 4 Replies View Related

Update Source Data So Table Automatically Updates

Nov 19, 2008

I have two worksheets in my workbook;

1. Table
2. Raw / Source data.

I have entered all the formulas into my table (sheet 1) referenced to the source data but I want to upadate the source data regularly but when I delete sheet 2 all the formulas come up with #Ref error. how i can update my source data so my table automatically updates

View 2 Replies View Related

Move Automatically Data From Dynamic Table To Static?

Feb 13, 2014

I need to transfer data from a table dynamically updated every 10 minutes to a table that never changes and maintains data, how can this be done using a macro?

View 1 Replies View Related

Sort Table Of Data By Status And Automatically Populate Another Sheet

May 2, 2012

I am trying to sort a table of data by "Status" and automatically populate another Sheet. In Sheet 1, I have data as follows:

Issue Status Issue - 1 Open Issue - 2 Closed Issue - 3 Closed Issue - 4 Open Issue - 5 Closed Issue - 6 Open

I want Sheet 2 to automatically sort this information by Status = "Open".

Issue Status Issue - 1 Open Issue - 4 Open Issue - 6 Open

How I can accomplish this by Excel functions (I do not want to use Pivot table)?

View 4 Replies View Related

Excel Formula To Automatically Select The Previous Months Data For Summary Table

Jan 20, 2014

I need a formula to automatically change the summary column according to the month we are in.

Ie:
Last Months Data
Nov
Dec
Jan
Feb

1
8
1

7
4
7

9
2
9

'Last Months Data' column should show Dec. However, as we move into February and I complete the 'Jan' column, I would like 'Last Months Data' to automatically change to show Jan's data - is this possible?

I was previously told to use the following formula but this would automatically select the current months data and not the previous months data which is what i need - =INDEX(B2:L2,MONTH(TODAY()))

View 2 Replies View Related

Automatically Populate Combobox With The Data In A Certain Range

Mar 12, 2009

I would like to know how to automatically populate a combobox with the data in a certain range. Attached is a simple workbook with the example. I know how to do this with the additem. But I dont know how to create the array or userform initialize to populate the combobox with the range.

View 3 Replies View Related

Automatically Copy/Paste Data From Input Range Based On Criteria

Sep 4, 2006

I have a workbook with 2 - worksheets "DATA" and "LETTER".

The "DATA" sheet has columns "First Name" "Last Name" "Address"...etc.

The user can enter in either a "X" or a "x" in cells B6:B100 on the "DATA" worksheet.

After the user enters a X or x and hits the macro button I need it to copy certain cells from "DATA" worksheet to "LETTER" worksheet. I have listed a example below.

In this example if a "X" or "x" is entered into B6 on "DATA" worksheet and the macro button is hit, then: ....

View 9 Replies View Related

Turning Range Of Data Into Table

Jan 10, 2012

Is it possible to turn a range of data into a more detailed table.

for instance -

the input data would be something like

1 - 3 @ 50%

4 - 6 @20%

8-10 @70%

the output i'm looking for would be a table like below

1 - 50%
2 - 50%
3 - 50%
4 - 20%
5 - 20%
6 - 20%
7 - 0
8 - 70%
9 - 70%
10 - 70%

View 3 Replies View Related

Sorting Table Of Data - Selected Range Only

May 30, 2014

I have some data formatted as a table. There are some blank rows at the end that still contain formula.

Every time I sort the data these rows move to the beginning. I have tried only selecting the active rows of data, but the whole lot defaults in each time

How do I sort data with a selected range only (within a table)?

Don't really want a macro as this will need to be used by others

View 1 Replies View Related

Pivot Table Change Data Range?

Jan 30, 2012

I have created Pivot Table in "Sheet2" and the source data is in "Report" tab. The source data is in the range of A1:K200 -means until the last cell of the excel. Now if the data is more then this we need to change the source data every time before we refresh the pivot table - Is there any macro or any way so the range can be change automatically ?

View 9 Replies View Related

Populate Second Table Automatically From First Table

Oct 15, 2009

I have data that was taken hourly for a whole year. It is in three columns as shown below:

Date Time Value
8/13/2009 0:00 0.6
8/13/2009 1:00 1.2
8/13/2009 2:00 1.4
. . .
. . .
8/13/2009 23:00 0.8
8/14/2009 0:00 0.7
8/14/2009 1:00 1.5
. . .
. . .

What I want to do it is put the data in a table as shown below:

Hour 8/13/2009 8/14/2009
0:00 0.6 0.7
1:00 1.2 1.5

Such that the hour is in the first column and the values for each date is in individual columns. How can I populate the table easily using formula? For the first date column I can just reference the first 24 cells. But how can I create a formula for the second date that I can use to copy over for the third date, the fourth date, and so on? So for 8/13/2009 0:00 I can put into the cell =C2 For 8/14/2009 0:00 I want to put something in that automatically advances 24 cells down the value column and that I can copy and paste into the remaining date columns of the second table to populate it automatically. It seems like this should be easy but I can't figure it out.

View 2 Replies View Related

How To Arrange Data Range Into New Table Based On Multiple Criteria

Aug 27, 2012

I have a problem whereby I have a list of data that has a date, a transaction and a balance. I want to be able to rearrange this data into multiple tables, one for each month.

The pictures below explain the situation better, on the left is the list of data I need to sort, and on the right is how I'd like the data displayed. So for the May columns I would like to display all transactions that happened in May and, depending on whether it is an income or an expense, the amount in the corresponding column.

i.e. so the finish result looks something like this:

View 9 Replies View Related

Extend Column Instead Of Row?

Dec 16, 2013

When i press the corner of a cell and drag it (example =D2) it will only go higher in the number (drag it 3 rows down it just goes D2, D3, D4, D5, D6).

Can I drag it down but make it stick to the 2 and instead go E2, F2, G2 etc?

View 14 Replies View Related

Extend Highlight Bar

Sep 22, 2009

I am editing a fairly sizeable name, address list and I would like the row highlight bar to extend across the page. For instance, when I click on the row number the highlight bar extends across the row, but when I hit return the highlight disappears and I have to click row by row. I would like the highlight bar to extend across a row and then move up or down with the arrow keys.

View 5 Replies View Related

Combined VLOOKUP With A Date Range SUMIF From Pivot Table Data?

Feb 13, 2014

I have a report I am attempting to populate with data from a pivot table in another worksheet. Column A holds all the reference numbers (primary key), column B contains various start dates, and I want column C to contain all the payments made since the start date for each reference number.

The source data is a pivot table with Row = Reference number, column = transaction date, values = transaction amounts. This is an extremely large table, as I'm processing data from almost 1,200 cases, which each have around 20 payments spread over the last year, on completely random days. What I would like to do is build a formula in my report which looks up the records for the reference number from column A, and then adds up all the payments which have been made after the date in column B (and ignore any payments in the table which are before that date).

And to make things more complicated:

if an error is generated, it needs to return as 0, not #N/AThe report has the dates in UK format dd/mm/yyyy, but the pivot table has the dates in SQL format: yyyy-mm-ddThe pivot table is connected to a SQL database via ODBC and has to refresh every time it is opened.

=IF(ISERROR(VLOOKUP(A2,'Transactions'!$A$2:$B$1194,2,FALSE)),0,VLOOKUP(A2,'Transactions'!$A$2:$B$1194,2,FALSE))

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved