Email:      Pass:    Pass?

Free Excel Forum


Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More


I've been working on a fairly dynamic spreadsheet and could really use some help. This spreadsheet allows a user to enter data on one sheet, then pull output charts and graphs off of two other sheets.

One of the charts I want to create is a table of 9 columns by 10 rows. Column A is the row headers and Row 1 is the column headers. Each cell, except for the headers, contain numbers, both positive and negative. Each row is raw data that's being cut and pasted directly into the cells. The last row, however, is dynamic, and the data will change per the inputs on another worksheet. Is there a way to make each column, save the header, sort automatically, from highest to lowest?

This way, when a user inputs their data on Worksheet 1, the dynamic row within this table will automatically fill with the proper data and then re-sort. The other thing is that I need each cell within the table to maintain their color. Any help would be really appreciated. Thanks.

Similar Excel Video Tutorials

Helpful Excel Macros

Sort Data With Headers in Ascending Order in Excel
- Macro that sorts data that has headers in ascending order in Excel. This macro assumes that you data has headers on it.
Sort Data With Headers in Descending Order in Excel
- This Excel macro sorts data that has headers in descending order. This means that data is sorted Z to A and 10 to 1 - o
Sort Data that Doesn't Have Headers in Descending Order in Excel
- Sort data in Excel that doesn't have headers using a macro. This Excel macro assumes that your data set does not have a
Sort Data that Doesn't Have Headers in Ascending Order in Excel
- Sort data that doesn't have headers in ascending order in Excel with this macro. This is a simple sort macro that assum
Reverse Row or Column Order in a Worksheet
- This macro reverses the order of a selected row or column in excel. It will completely reverse the data.

Similar Topics

Using Excel 2003.

I want to create a list (or table) including headers. (The main purpose is, for any new data added, all formulae automatically apply.)

The issue arises because the headers are dynamic, in that each header cell references a header list on an assumptions worksheet.

The problem is that when I highlight the headers and relevant data rows underneath, and press ctrl-L to make the list, excel removes header cell formulae, and replaces it with fixed values.

Is there a way to get list creation to work with dynamic headers?
I tried making the list without a header row, but excel just makes the first row a default header row.


I'm having an interesting problem in Excel that I could use some help with. Here is the situation. I have a spreadsheet where there are currently a fixed number of columns with headers. I would like to dynamically control these from a Data sheet, where when a new header is added to the list, the table on the Actuals sheet automatically expands to include the new header. This needs to be done as simply as possible, hopefully with no user input, it just happens.

I've created a Headers named range that will automatically expand to include all new headers added to the row with a Dynamic Named Range, so that part works fine. My issue is just getting the new column added automatically to my data table. Don't know if this is possible, but thought in this forum someone may have encountered this problem in the past.

I've posted my spreadsheet as well for reference.


I have created dynamic workbooks for each operators with the same headers and columns titles.
now I want data from those dynamic files automatically store in one dynamic master workbook (with same headers and column titles) , from which I can generate some charts for respective ' Cause Code' ( this is the column in each workbook). i have attached two dynamic files for better idea. Keep in mind operators can fill their respective file at the same time. as soon as they type in or save the file, the data should go to master and create the charts.
thank you



I'm not a programmer, and my knowledge of Visual Basic is basically nothing. Still, I'm not completely incompetent with Excel. I could use some help if anyone is willing.

Attached is a sample workbook that shows a pretty close situation of with what I'm dealing. On sheet 1 there is data, which in real life, some will be static and some will be dynamic. On sheet 2, there is a table where only one row will be dynamic, and will be pulled off of sheet 1.

The goal is to make each column on the table from sheet 2 to sort in descending order, automatically, and as the dynamic data changes, while keeping it's color code. I think one of the issues is that when the dynamic data feeding into the table changes, it simply changes the data across the row, rather than finding the new and correct location - since the data had been previously sorted. It all gets complicated to write out.

Essentially, everytime this spreadsheet is used, the data will change. The other issue is that I need to format the table to create some white space between columns and rows so that it's easier to read. So far I've been doing this manually and it's driving me crazy. There's got to be a way to do all of this, right?! Any help would be greatly appreciated.

I have a list of student names sorted alphabetically, in a table with the grade/mark for each student on a cell to the right of their name
On the same sheet I have copied and pasted this list as linked cells.
I want to be able to sort these linked cells automatically from highest to lowest whenever I enter or change a grade /mark in the original list. (ie I want the student with the highest grade at the top and lowest grade at the bottom of this linked list)

The original list has headers: Forename, Surname, Grades, and runs from B1:D20 (including headers)
The linked list which I wish to sort automatically by grade has the same headers and runs from F1:H20

The problem of course is that every time the linked list is sorted the links are broken.
I have been going round in circles trying to solve this.
Can anyone help?

I am trying to create a pivot table matrix

Essentially, I am creating pivot tables using dynamic data sources, defining the sources using offset and counta.

I need to get these tables in a specific pattern on a single sheet. For instance, I may in a particular case want to align 4 pivot tables around a box. For instance, let's say we have 4 tables - A, B, C, and D. It so happens that A's column headers are also D's column headers, B's column headers are also C's column headers. Similarly, A's row headers are also B's row headers, and C's row headers are also D's row headers. I want to display this relationship on 1 sheet in dynamic say putting an imaginary blank square in the middle, and arranging pivot tables around it, such that A/D's column headers are on right, B/C's on left and A/B's row headers are on top, C/D's on bottom. so essentially, the four corners of the center square also form corners of the the pivot table data, in that each corner of the square/rectangle is one of the corners of one of the pivot tables. Am I getting too verbose and unclear as to what I want? If so, please drop me a message and ask for a clarification.

So...essentially, one of the problems I guess is placing dynamic pivot tables next to each other as Excel gets angry and says pivot tables cant overlap if ever the data source changes to include more columns/rows.

Secondly, for some of the tables I want to display column/row headers, for other not.

And thirdly, I want to use a bit of conditional formatting on the pivot tables. Not sure I can do that on dynamic pivot tables.

Please help!

Hello everyone please could someone take a look at a macro for me?
I have a list of letting agents that I need to sort into a table to import into Access. The list is dynamic in that not all the fields are present for every record.
I will have over 4000 records to sort out, so I would really like to automate this.
If possible the macro should read rows from the data worksheet and write to columns and rows on the Table worksheet.
I have attached a small example of the data and output required.
To make it a little more challenging the column header name is part of the data and will need stripping out as well.

Thanks for looking.


Lettings Agents.xls

My goal is to create a series of graphs that are controlled by the Customer list and Date range at the top of the Charts sheet. I have figured out how to change what the chart displays based on the customer list. However, I cannot get the date range in the charts to match the date range at the top of the page. I used this post( ) to get to where I am. However, I adapted it because I only want to look at quarters (Months) and do not need Year or the Dynamic Chart.

My file contains three sheets: Data, ChartData, and Charts. The Data sheet contains the raw data for all customers, while the ChartData sheet pulls the appropriate data from the Data sheet. The Charts sheet pulls the data from the ChartData sheet and displays it in the graphs. The Customer list is built from column O on the Charts Sheet.

If there is a way to pull the data directly from the Data sheet and get rid of the ChartData sheet I would like to do that, I just do not know how. I plan to add more columns of data to the Data sheet (I have put headers in there for the time being) and generate more graphs based on the additional columns. Also, if there is a better way to generate the customers list, ideally I could do it based on the column in the Data sheet. Right now, it is using the list in column O of the Charts sheet. I would like to stay away from coding if at all possible. I do not think I am currently capable of handling coding in the future.

Thanks in advance for your time and help with this.

Attachment 41809

Hello all,

I am having the following problem for which I want to build a macro.

I use a table similar to the one attached which basically has column headers one-hour time slots (such as 06:00:00, 07:00:00 etc) and the cell's contain time values in minutes:seconds. The number of columns is static, however the number of rows is dynamic as it is the output of another macro that returns an unknown number of rows each time.

What I do is taking this amount of data and copy/pasting it into my worksheet under my column headers. Now I want to create a macro that will automatically go to the last row (preferably leaving a couple of rows blank) and doing the following calculations for each column: average, count and another one that I will hardcode in the macro

My problem lies with the dynamic nature of the data, otherwise it would have been easy to create it. Any ideas on how I can work with that?

Please note that the range of columns will be different in my worksheet (i.e it will not be from A->U but probably some other column range, yet static and specified).

Looking forward to your feedback,



I want to use dynamic charts which point to a data range.
Therefore updating the data will automatically update the chart.

Can I create the dynamic chart to only plot the highest 5 sets of figures (obviously most important), out of 20 rows of data?

With the data changing the highest 5 may also change thus the need for some kind of dyanmic range setting?

Any help appreciated......


I want to use dynamic charts which point to a data range.
Therefore updating the data will automatically update the chart.

Can I create the dynamic chart to only plot the highest 5 sets of figures (obviously most important), out of 20 rows of data?

With the data changing the highest 5 may also change thus the need for some kind of dyanmic range setting?

Any help appreciated......

I have more pivot table woes... I'm automating a long process which involves multiple pivot tables. All I need to do is sort by the last column which is a dollar total. The Data>Sort option does not seem to be working at all (I can select all of the rows in the pivot table and then use this, but this hasn't worked well for automation). When I click on the column header for the last column (the one I want to sort with), then select Data>Sort I get a message "cannot determine which pivot table field to sort" but I can do this on the other column headers.

Excel "help" talks about different ways to sort, but I can't get any of them to work with the pivot table.


Any suggestions?!?

Thank you!


Hi, I have a worksheet and I'm trying to create a macro that will automatically sort two columns of data. I have attached a version of the worksheet I am using with the data wiped out and replaced with random information for privacy purposes. I'm looking for a macro that will auto sort ranges AO:AQ and AS:AU, which I am using to populate the two graphs at the bottom of the page. I want these columns to update any time the numbers change. (If it matters, the numbers in the original worksheet are driven by formulas, as opposed to this version where I just copied and pasted random values.) I am looking to sort (in descending order) the values in yellow columns AP and AT. There are headers in row 1. Any help you can provide would be MOST appreciated. I've spent hours trying to tweak various code snippets from the macro recorder, forums and websites, and have not found anything that works for me. As is probably obvious, I am a very novice VBA user, so the closer you can get me to formulating something that works without a lot of editting, the more helpful it will be.

Much thanks for all your help.

My Problem:
I currently create my employee schedule in excel and I am trying to find a way to decrease the time I spend entering date. Currently worksheet A lists the employee name in the first column and the days of the week as column headers, where I input the employee shifts for the week. This view is good for the employees because it shows them their total hours for the week. But I need to make sure we have the right staffing in the different departments, we have 6 job codes. Right now I manually enter the data onto worksheet B, the set up of B is not ideal. I have the days fo the week listed as column headers and the job codes in the first column. I basically type in the shift and employee name this table to see staffing by department. This ends up taking a ridiculous amount of time.

What I Want to Do:
Worksheet A: row headers-employee name, column headers-day of the week, start and end times desplayed in table
Worksheet B: row headers-job code, column headers-day of the week, employee name and shift displayed in table
I want to be able to enter only the shift times into either table and have the other table pull the information. The problem is that I want to be able to sort my list of employees (depending on job code, shift, a to z etc) and not have it mess up the other worksheet. I thought a pivot table would work best for this, but I cannot figure out the best way to set up my worksheet to allow for all the data to be on the pivot table.

Any suggestions would be greatly appreciated!

This one is a little different than other similar topics.

I have a pivot that bases its data on a fixed number of columns (37).

The problem with the source data is that the column headers, which are values on the pivot, often change their names.

For example, Column W may be named "Sprocket" on one day, it might be changed to "Gear" the next. The number of columns remain the same, but the column headers change.

The pivot does not account for these changes. As soon as the column header changes its name, it removes that column after a refresh (i.e. unchecks that column in the Pivot Field List and that column no longer shows up on the generated pivot).

Is there a way, either in options or by VBA, to get the pivot to include these columns automatically after they change their header name? I tried making a dynamic pivot with OFFSET and a named range, and while it included the newly named column in the Pivot Field List, it does not automatically display the newly named column in the pivot after a refresh.

I fear my end users are not savvy enough to open the Pivot Field List and check the box.

Any solution for this? Thank you very much!

I'm trying to consolidate large numbers of spreadsheets which all have a similar ancestor (i.e. they ostensibly contain the same data but have mutated over time).

I want to name the ranges so that I can pinpoint each one (the column headers seem to be relatively consistent).

Something like:
Workbook("x").Names.Add Name:="Data" &"a13" RefersTo:="=Offset($a$14....counta($a$14:$a$114),0)"

Where a13 is a column header and the data beneath it is what I'm interested in.

1. Give the Range a Name "DataXXXXXX" where XXXX is the column header. One problem is that some column headers have spaces.

2. Assign the range an appropriate dynamic range value (so it specified how many rows according to whether there's data in it), using the same column as the header's in. e.g. if my colum header is b13, my data will be b14 down to b## and will vary by sheet.

3. Ideally, I'd like it to hop along horizontally until it reaches a blank square. i.e. it'll do all the columns (though I can cope with if it just does a number20 as this will be more than most of the spreadsheets and it will just keep redefining the "Data" name, one assumes)

This way I can bring the stuff into one consolidation spreadsheet as it is, and then over time migrate them to a consistent format.

Any ideas?


I am trying to have this table sort automatically. I want to sort by column AA the Error%. When I enter each days errors the error rate will change but this column does not sort automatically. I have to do data sort then the column AA again to get the correct sort.

I want the lowest error rate on top and the highest at the bottom automatically. If possible I would like the lowest rate in Green and the highest in Red.

Can anyone help with this?
I've attached a sample of my spreadsheet.

Hi, I'm trying to automatically sort a dynamic table, because vlookup is giving me problems. Basically, whenever my data changes, I want my table to be sorted from lowest to highest, along with the corresponding data. Here's an example of what I mean:

$2 Paul 4 Apples
$4 Mary 8 Apples
$5 Joe 10 Apples

If Paul sells 12 Apples, and makes $6, then I want the table to re-sort to:

$4 Mary 8 Apples
$5 Joe 10 Apples
$8 Paul 16 Apples

I hear that this possible without using a macro. I'd like to stay away from that if possible. Any suggestions?


Hi all and first thanks for all the great information that you give out.

I have been sending myself mad trying to sort out something that I thought was going to be easy but have been messing about so much that I have almost forgotten what I was trying to achieve.

I have uploaded an example spreadsheet to assists.

This is what im trying to achieve.

I have multiple worksheets with computer asset details and to keep it simple the data is on Sheets 1 to 3
The output sheet has all the Headers in Row 1

What I am trying to achieve is to get all the data from multiple sheets 1 to 3 and more, match the headers from those sheets which are vertical in Column A1, match those to the Output Sheet and copy the data over to the output sheet.

In my example data I have filled in some of the column data in the output sheet
As you can see sometimes there are more columns with data in than others.
The main identifier is the CIName
I have been trying to work this out using Transpose but due to the input data having different amounts of data I was getting myself in a mess.

If you can think of an easier way to achieve the end result I am up for listening to it.
So basically I want to match the headers from Sheets1 to 3 with the Headers in the output sheet and then list the data in columns and not rows.

Thanks for you help.

Really appreciate your time

I am trying to figure out a way to sort a worksheet based on values in two columns. I have columns A-G and rows 5-29 (although i will need to expand on the 29 at some point). The rows need to stay complete so the descriptions and other inputs are not lost.

Columns E and F are the values i want to sort by. they are a range between 0 and 5 (5 being more severe). I want it to sort highest to lowest based on the sum of the inputs in E and F with row E holding more significance. For example, if i have points 3,5 - 3,3 - 5,3 and 4,5 i want to auto sort them as follows: 4,5 - 5,3 - 3,5 - 3,3. Also, i would like to be able to change the values in E and F and have it update the sort automatically.

It is important that my rows stay in tact.

Any ideas?

-Thank you!

I have a row with the following column headers:

Monday, Tuesday, Wednesday, Thursday, Friday.

The actual data begins the row under the days of the week, and the row with the above column headers can and does change.

I need to create a dynamic range (i'll call it week) that starts at the cells below Monday:Friday, and extends as long as the data stretching down.

I know how to create a dynamic range normally, but instead of set values, I need to use the cells below the column headers, whatever they may be.

My current range "week" is as follows:


I would like somthing more like this

=OFFSET("cell below the column header "Monday",0,0,COUNTA("Cell below the column header "monday":"As far down as the data below monday extends in Friday's Column"),1)

I hope I didn't confuse you.

Hello all,

so I have a table that is linked to a data source (an Access Database)
when I refresh the data, it adds and deletes rows as it should but it also ensures that the headers are always the same.
If you try to change the column header and refresh the data link, the header goes back to its original state.

Is it possible to change the headers or fool excel to ensuring these headers exist as another string instead of its header from the data source?

additionally, the data table forces a auto-filter and does not allow you to remove it...
is it possible to remove it?

I would like to append the table with some additional information but i wont be able to filter it, So i want to change the auto-filter range.

Hello everybody --

This is my first post, and I want to thank everybody for using this forum. This website has been very helpful for many of the projects I have been working on for quite some time.

On to my post...I have created a spreadsheet that has many dynamic ranges, one of which is a list of vendors that my company uses, named "Company". This range is in a spreadsheet labeled "Analysis Search". From this list and on another worksheet, I have created a drop down list via data validation where the user inputs the vendor, and should the vendor not be in the dynamic range, a prompt will appear and warn the user that the name of the vendor is not already in the dynamic range and to add it once the entry is complete.

What I am looking to do is for Excel to automatically add the vendor to the dynamic range instead of having the user have to input the vendor. Also, I have used VB to have the range automatically sort alphabetically, so I would like to keep that functionality if possible. Is something like this feasible in Excel? Thanks in advance!

I have column headers in row 4. How would I change the below code to include a data range with a dynamic # of rows and columns? (instead of A4:BF1599 I would like to just use all data in the worksheet knowing row 4 is the header row)....

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"A4:BF1599").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable2", DefaultVersion:=xlPivotTableVersion10

Sub sort()
ActiveSheet.Range("A8").sort Key1:=ActiveSheet.Columns("A"), Header:=xlGuess
End Sub

In my spreadsheet, I'm using either a userform to enter data into my spreadsheet or enter the data directly into the spreadsheet without using the userform. How can I use this code to automatically sort my data by date the moment I click the enter button on my userform. Also, if I decide not to use the userform to enter my data directly into the spreadsheet, and i click an empty cell in column A, I would need the data to sort automatically as well.

for example,

Instead of inserting a row to enter data on 10/1/07 and today is 4/3/08, I would like to just enter the data on the last empty row and after I click enter on the userform or click on an empty row, the data will automatically sort.