I have a blank template spreadsheet that is copied every time I need to work on some data exports from another system (the data needs to be kept, hence the copying of the original blank template first).
I have set up conditional formatting using;
=MATCH($A1,range,0)
This formula is always in the entire column of row A in Worksheet1 (for the sake of argument)
Problem is in Worksheet2 where the range it's using to compare is always a different number of rows deep...it can be 70, it can be 2000. I always call the range 'interest'.
Is there anyway (in the blank template) of setting column A as a dynamic range called 'interest' so that after data import it automatically sets the range at the desired size?
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 currently have two tables in one worksheet showing the sales of different region.
The problem is, when I sort the data in the table (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??
**Attached, please find the example spreadsheet, I have only written the code for the first table.
I am using OFFSET to make dynamic named ranges- the only problem is that I'm having trouble using it with linked data-
The Situation:
-Cells A1:H500 are linked to another sheet, with each cell containing a formula like =IF(ISBLANK('C:...[workbook.xlsx]worksheet'!A1),"",'C:...[workbook.xlsx]worksheet'!A1)
-Currently only cells A1:H100 have data, but this is dynamic and will change
-I want a named range that will only select the cells with data in them...the problem is that COUNTA counts the link formula even if the cell is "blank" (because it isn't blank).
-I hate hard-coding ranges...the solution for the named range =OFFSET($A$1,0,0,500-countblank($A$1:$A$500),8) is ugly!
Any solution to making dynamic named ranges with linked data that may or may not be "" ?
I am unable to use dependent data validation lists using the INDIRECT function when the initial named range is a dynamic one.
See attached.
The named ranges 'Men','Women','Children' are all dynamic based on number of entries in each column order to accommodate a growing list, whilst also not having blanks in the dropdown (hence I haven't used entire column ranges). The named range 'test' is a static one.
Column H has a dependent data validation based on entry in Col G. This works for the static list, but not the dynamic one!
I'm working on trying to maintain an inventory management sheet where i basically enter all the spare parts issued to vehicles. Now there are more than 700 parts dat form the components of a vehicle, so i decided to split this list into 12 spare groups(Gear, Engine, Body, Axle, etc etc..) and then based on wotever spare group being selected my sheet wud pull dat specific spare group list. Since i've worked with indirect function before i thought this was a piece of cake!
However when i actually sat working on getting my sheet together i realised its not gonna be dat easy! Basically my spare group lists are a work in progress and so new items would/may be added to these lists as we start working on this sheet. So i decided to make them dynamic by using the offset function. But list validation fails when i use Indirect function on dynamic ranges!
A lot of the Workbooks that I design for use by myself and colleagues require data to be copied in from external data sources. To avoid named ranges from failing, I always use the following method:
Calculate the length of the data set:
Code: =COUNTA(INDIRECT("'Data Sheet'!"&"$A:$A"))
(There will be no gaps in the data, hence a count is fine.) This named range is called DSROWCOUNT.
I use INDIRECT to ensure my named ranges do not fail if the data is deleted (accidentally or intentionally), as #REF! errors will occur.
The problem with this method is that it automatically makes the Workbooks volatile because of the use of OFFSET and INDIRECT, hence the Workbooks always needs to be in manual calculation mode to be usable.
loop that can calculate the median of dynamic ranges. I need to run through all columns and for each column find the the ranges (there are more ranges and they are seperated with a blank row) with numerical data and then calculate the median and thereafter continue down to find the next range with numerical data in that row and so on.
I have a range which will change in size & in content, & I want this to be a Named Range at whatever size it is.
Reason I want to is because I want to make a Validation List with this dynamic range. I also want a Validation list which lists the content of 2 or more dynamic ranges which may or may not be on the same worksheet - is this possible?
i.e. First dynamic range: called "Milestones" at A11 Second dynamic range: called "Activities" at A25 & make a Validation list that will list content of both
I would like to replace the blue bit of this Dynamic Named Range (DNR) with an INDIRECT formula in order to modify the starting point for the DNR: =OFFSET(DATA!$C$60,0,0,20,1). Unfortunately, I can’t seem to get my INDIRECT formula to work in order to use it to replace the blue bit above: INDIRECT("'"DATA"'!&ADDRESS(MATCH('SHEET1'!AC8,DATA_Date,0)+22,3)"). The orange bit of the formula above returns a value of 60, therefore the ADDRESS formula should return $C$60, that can then feed into the INDIRECT and act as the equivalent of DATA!$C$60. But it doesn’t.
right now this code exports to another sheet, and only exports the active cell. I want to export to a new spreadsheet with a new name, and a given area instead of just the active cell.
I want to create dependent lists using data validation. The lists need to be created from ranges that will be growing as users add more data. I think the best way to create the list is a VLOOKUP. However, I am not sure how to use the VLOOKUP when the range is changing.
I have attached an example. I have a list on sheet "Vlookup" called "FRUIT" with "apple", "orange", "banana". Then to make the depedent lists I have created three other lists called: "APPLE", "ORANGE", "BANANA". I want to pull the COST from sheet "VALUES" into the lists "APPLE", "ORANGE", "BANANA". Users will be adding costs next to the FRUIT they purchase.
I'm trying to make ranges in a SumProduct formula dynamic, but getting errors #N/A. I think this is because the top two rows are headers, throwing off the range count.
Q. How do I adjust the range definition to compensate., e.g.
Range =Offset(Sheet1!$D$3,0,0,COUNT(Sheet1!$D:$D),1) where column D is numeric, and
Range = Offset(Sheet1!$T$3,0,0,Match("*",Sheet1!$T:$T,-1),1) where column T is text?
I had assumed that the offset value 3 would do this, but I suspect the functions count and match are not doing so.
I am trying to create a vlookup to get a count of trouble tickets techs completed daily within a table I created, I am using a table since its dynamic. For example I need to see how many tickets Joe completed in a day. See below...
Tech Ticket# Comments Status Joe 1234 Replaced HD Closed John 3212 Replace Motherboard Closed Joe 5678 Installed OS Closed
Above is just an example (my table has 40 columns). I need to vlookup joe and get the count of the closed tickets.
So I was looking for reassurance or validation more than anything. From what I can tell you can in order to build a chart that is dynamic throughout a range, you use the offset and count or counta function - 1. That part isn't a problem. My question is once you created that for your charts do you just normally plot your chart range or do you have to reference the named range directly into the chart range?
I have monthly sales, and i wanna be able to sum the last-twelve month sales, when i select the month from a drop-down list. For example, when i select Feb14 from the drop-down, I want the formula to sum the sales between mar13-feb14. how can i accomplish that?
I have a stock chart that I want to update dynamically if the data record length changes. From the source data menu, I tried to use the following data ranges:
=OFFSET(Candles!$AB$8,1,0,COUNTA($AB:$AB)) ---> Date, x values =OFFSET(Candles!$AD$8,1,0,COUNTA($AD:$AD)) ---> High =OFFSET(Candles!$AE$8,1,0,COUNTA($AE:$AE)) ---> Low =OFFSET(Candles!$AF$8,1,0,COUNTA($AF:$AF)) ---> Close
When I place any of these in the data range box under 'x axis labels or values', I receive the message "This function is not valid".
The MS webpage at http://office.microsoft.com/en-us/ex...098011033.aspx describes using these formulas but they don't appear to work.
I am creating a consolidation worksheet. It contains a macro that automatically copy data from other sources and paste it into my DB sheet. It already works, but I want to improve it. The source worksheet databases has been defined with the name “SOURCE”. Is there any way that I can make my macro look for this dynamic ranges directly, copy and paste it into my DB sheet. I am attaching an example of what my current macro is doing.
I've got a spreadsheet that I download a list of information into. The information relates to activities that are taking place e.g. start time, end time, type of activity (e.g. meeting) and duration (which identifies the length of the activity in 30 min slots e.g. 9.30am - 11.30am = 4).
The rest of the columns are labelled to represent the time in 30 min slots starting from 9am. What I would like excel to be able to do is to identify the first cell in the range which would be the cell that represents the start time in the row. Then from this cell fill cells in the row up to the end time with something - it doesn't matter what as long as the cell can be differentiated from the other blank cells in the row.
I don't think that I've explained myself that well so I've attached the spreadsheet for you to look at.
I'm having a fit with this formula; maybe the gurus will show me what I'm doing wrong. I have a list of area numbers along with dollar amounts that go with these area numbers. I have created a named range named "Areas" and another one named "MO_PD".
At the bottom of the worksheet I am doing a recap that takes the area number and sums all the entries in the MO PD column. This approach has been done before with Thomach's help. I'm trying to mimic it, but it's not working for me.
NOTE - I know that I can get around this using a pivot table, but I would like to use this approach for now. I would at least like to know what I'm doing wrong. Also, I'm not totally sure if I did the dynamic named ranges correctly so that may need to be fixed.
I've been working a macro that seems pretty simple, but has been giving me loads of trouble. I have about 20 worksheets in a Workbook, which all have a similar template (i.e. the data I'm interested in starts on row 14), but have different amounts (rows) of data in them. I want a macro that sorts all the data in ascending/descending order according to column Q. The problem that I am running into is not that the macro only works for the sheet that is active when I run the macro as opposed to every single sheet in the workbook ....
I have just upgaded to Excel 2007 and previously used a Dynamic Range Wizard addin in Excel 2003 but it does not work in 2007. I think it originally come from Robert Bruce but can not find it on the web to get an update.
Worksheet A - I've set up a dynamic range to display the last 12 row entries of 2 columns Worksheet B - pivot table of the range
The dynamic range is working perfectly, it displays the correct rows I can set up the data source once... the results on the pivot table display corectly
Problem When i press refresh, the data source "dissapears" and i have to rebuild the pivot table.
