I currently have two tables in one worksheet showing the sales of different region.
The problem is, when I sort the data (I can't used the named ranges as it should exclude the first row which is the header), is there a way I can make it dynamic too??
** The Europe table is from A1:G5 where Row 1 is the Header and Row 5 is the Total
have data in the attached file arranged as follows:
"Source Data" sheet Data in columns A to L will be updated daily by pasting from another workbook. On any given day the data in columns A to L may have more or fewer rows than the day before.
"Lookup Table" sheet The aim is to return the data from the "Source Data" sheet; the selection depends on the Data Validation choice in cell O1.
I would like to be able to sort this dynamic list of data - ideally by clicking a single button - in descending order of the values in column L.
In the attached workbook I have hard-pasted data in columns A:M that's designated as an Excel Table to make it dynamic.
In the adjacent columns O:AA I have formulae to extract data from A:M based on the "Frequency" stipulated in cell AD1,
I would like to be able easily to sort the data in the variable sized range in columns O to AA in descending order of the calculated values in column Z.
Best way to do this so that the formula don't get jumbled up or the blank cells in column Z end up at the top of the sorted data?
I have a worksheet with a data base type list (16 cols, 10000 rows deep) which contains only 30 records, for testing purposes. Each cell is bordered in all 4 sides.
There will be a bunch or reports associated with this, requiring filtering and/or sorting. The macro instructions I applied so far are:
1) Locate last row. 2) Select the range from first to last row and apply a common row height (say, 24). 3) Sort as required 4) Filter as required. 5) Print
The sorting step has invariably failed. The funny thing is that the code I used is the one produced by recording the steps. I am able to sort that segment manually, but the macro instructions fail.
I am trying to filter a table of dynamic size. My table begins at P3 (first row of data, not column header) and ends somewhere at the bottom of Column AA. I am getting an error "AutoFilter Method of Range Class Failed." Why? How do I fix this?
Here is my code
VB:
If Target.Range.Address = "$B$4" Then With Sheets("Days Past Due") LastCell = .Cells(.Rows.Count, "AA").End(xlUp).Row MsgBox LastCell ActiveSheet.Range("P3:AA" & LastCell).AutoFilter Field:=17, Criteria1:="MABST" End With End If
I am working on a sampling tool in Excel which is based on what a user enters into a column. The user enters observation codes (text) in column A beginning at row 2 and going down to however many they have. I already have formulas which determine the sample size needed based on the number of nonblank values in that column; the resulting sample size is in G4. I'm thinking that I will need to have a form with a button module to select the random sample from the values in col A. This way Excel isn't running the module while the user is entering the values. The resulting sample can be pasted into a separate column or sheet.
I have created a dynamic chart in excel where either the number of Series or the number of Data Points will change dependent on user inputs in the model. I've done this using the Offset Function in a Defined Name in Excel and dropped this Defined Name into vba code using ActiveChart.SetSourceData.
Anyway, all works fine, pulling the correct data, etc. What doesn't work is the final appearence of the chart. The chart has a Legend placed at the bottom of the chart, as standard, unless there is only one Series, when I remove the legend altogether. Whilst the legend works fine, the Plot Area of the chart doesn't update automatically to accomodate the changing size / existence of the Legend. Does anybody know how to do this? I tried using hieght, etc properties, but I don't know how to make them variable
I'm trying to come up with a histogram chart that adjusts Bin Size based on user input. I've come up with the formulas for Bins and Frequency, but am struggling when it comes to charting the data to dynamically update when the user changes Bin Size. How to make the x-axis dynamically adjust with the number of Bins.
i have a table with 4 columns: (customer name, revenue, expenses, profit) in cell A1, i can select from either of the above headings and what i wanted to do is sort the mentioned table based on the heading that's selected in A1.
for example, if i select Customer name in A1, then the table in (B2:E12) is going to be sorted by customer names (alphabetical)....or if i select Revenue in A1, then the table will be sorted by revenue values...etc.
Is it possible to have dynamic sorting in a table?
What I am after is a scoreboard type effect where as results are entered, the teams are sorted automatically, rather than entering all of the details and then doing a sort afterwards.
I have attached a table where I would like the sorting of columns B - E done in the following order (All decreasing order):
Comp Points first then by Sets % then by Points %
I am drawing this info from another table. Will this have an effect on if it can be sorted dynamically? Hoping that as i enter a result in the other table, it will bring across the current comp points, set % and points %, and then sort the table into the rankings all at once.
Currently I get a 1004 error. "Method of Range of Object _Global Failed" on "Range(Selection).Sort.SortFields.Clear"
Code: Sub sortBacklog() Dim appExcel As Excel.Application Dim myWorkbook As Excel.Workbook Dim myWorkSheet As Worksheet Set appExcel = CreateObject("Excel.Application") Set myWorkbook = appExcel.Workbooks.Open("C:Backlog.xls") Set myWorkSheet = myWorkbook.Sheets(1)
I am attempting with a simple spreadsheet he can use on the golf course on a Treo (so no macros I believe can be used). I am want him to be able put the order next to a list of people's names (the same 8 play) and then a dynamic sort will take place listing who goes first. Everything works great except it one or more people do not play then the sort lists them as "N/A". Instead of "N/A" I want it to show up as a blank.
I regularly get a sheet that contains about 20 columns. I need to delete about 14 of them, and re-order the columns to a fixed order. The columns always have the same headers, but are ofter in different positions (so I cannot hardcode the column location). I have tried using the following, but fail on trying to use the result of the find in the selection afterwards. I also do not know how to loop this through a list of column names (as I said, the input always has the same column names, and I always have to delete the same columns, and sort the remaining).
I have a question on the above but can't seem to find a solution. There are two ways that I can find for dealing with dynamic lists via data validation:-
1) Offset and match, cavet being the data must be sorted a-z 2) Have lists for each potential selection
Is there any way to get around 1 without having to do 2? E.g. Got two columns of data, unsorted, and a list from which the user can choose from. The user chooses from the list in first cell, in the second cell require the dynamic list to return all the values accordingly?
I am trying to write a macro to paste the data that has been copied onto the clipboard into my worksheet. However I want to paste it in different ways depending on what data is there.
This is what I have so far:
Sub ImportTissue() Sheets("Tissue").Select If "dimensions of clipboard data are 5 cells by 5 cells" Then
I am trying to auto size cells depending on the lenght of the data in them. I have added some code in the work book on the attached file, this works on cell that i maunally add data to any where on the active page (template) but the cells that are auto updated with currencey data dont expand?
If you double click column H a list appears select a test and this auto populates the charges accociated with it
Is there a way to do this as it just fills with #### ???
The code in this workbook is below
[code] Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Target As Range) Dim actCol, curCol
I have got many files lets say 30 files ,in each file there are 3 columns of data but the no: of rows varies for each file .for eg 1 file has got 6 rows and 3 columns then next file has 3 rows and 3 columns etc .i was not able to append it since its size varies.i need 2 write all these data in an excel sheet also . As u can from the excel sheet for clearance which i have done manually for a single speaker.like dis i have got many speakers so i need 2 code it
I have a Data Validation list of names but the font is very small and hard to read...is there anyway of making the font larger so as it is more readable.
I have all of my pivots all set up and all of my charts running from them.
My challenge is in pasting in the new data every day into the master Sheet BECAUSE, in order to group the dates (from format 20/02/08 into Month and Years in the pivot) you have to 'WRAP' the data, instead of simply blocking in all of the columns......
The challenge is that everyday, the data size differs, e.g; one day it might be 2000 rows, and the next 22,000 rows.
SO,,,,, BECAUSE I HAD TO 'WRAP' THE DATA (IN ORDER TO GET THE DATE FORMAT I NEEDED) >>>>> HOW DO I GET THE PIVOTS TO CALCULATE ONLY USING THE CHANGING AMOUNT OF ROWS
How to turn off or default the warning box message ...
"Data on the Clipboard is not the same size and shape as the selected area. Do you want to paste the data anyway"
I hate having to click yes on this every time I paste something. I do it hundreds of times a day.
My tables is centered and merged because my external date changes sizes each time I paste to the table.
I have looked all over to try and find an "OFF" warning/message button for this. I just want to know how to turn it off or default it to never pop up again!
I have noticed that all the cell dropdown lists i have (created by using Data | Validation | Allow | List) are very difficult to read its as though the font is size 8 or 6 or even less, is there a way of displaying the dropdown contents in a larger format? i have tried changing the font size of the named range and of the cell holding the dropdown but to no avail.
Using excel 2007, I have a worksheet with numerous data validation drop down lists. I was making a few changes to the worksheet and then I restarted the code. The font size of all my drop down lists displayed as a smaller font (maybe 2 sizes smaller) than had been displayed before. I went back to a previously saved copy of the code and the font size in the drop downs is back at the original size. Did I do something to cause this or is it excel 2007?
I'm using Excel 2010 and I applied a Data Filter to a simple table. I then messed around with the drop downs in each column, sorting the data by different criteria. After doing this, is there a simple way to get the table to revert back to its original order/form?
