#### Dynamic Range Based on Cell Result not Empty Cell

```I use dynamic ranges in excel.  I use them to name ranges that vary i
size due to varyin data.  For example, I may dump data in F2 which ma
span 3 rows or 100 rows.  I use the following formula to name th
range:

=OFFSET(\$F\$2,0,0,COUNTA(\$F2:\$F65536),1)

However, I now want to define the range based on the cell result.  Tha
is assume that I have a constant formula from F2 to F102.  However th
cells in the  column either have a number in them or a "" in them.  Th
formula above will automatically size the range to F2:F102 becuas
there is a formula in every row.  How can i write a formual which wil
size the range based on a formulas result?

Not that the numbers will all be back to back.  They will not b
intermittant.

Thank

--
Message posted from http://www.ExcelForum.com

```
 0
6/11/2004 6:12:18 PM
excel 39879 articles. 2 followers.

3 Replies
872 Views

Similar Articles

[PageSpeed] 10

```Hi
try
=OFFSET(\$F\$2,0,0,MATCH(9.99999999999999E307,\$F2:\$F65536),1)

--
Regards
Frank Kabel
Frankfurt, Germany

> I use dynamic ranges in excel.  I use them to name ranges that vary
in
> size due to varyin data.  For example, I may dump data in F2 which
may
> span 3 rows or 100 rows.  I use the following formula to name the
> range:
>
> =OFFSET(\$F\$2,0,0,COUNTA(\$F2:\$F65536),1)
>
> However, I now want to define the range based on the cell result.
> That is assume that I have a constant formula from F2 to F102.
> However the cells in the  column either have a number in them or a ""
> in them.  The formula above will automatically size the range to
> F2:F102 becuase there is a formula in every row.  How can i write a
> formual which will size the range based on a formulas result?
>
> Not that the numbers will all be back to back.  They will not be
> intermittant.
>
> Thanks
>
>
> ---
> Message posted from http://www.ExcelForum.com/

```
 0
frank.kabel (11126)
6/11/2004 6:52:17 PM
```Hey this works fine!!  Just out of curiosity, how is this working?
What does the 9.99999999999999E307 represent?

=OFFSET(\$F\$2,0,0,MATCH(9.99999999999999E307,\$F2:\$F65536),1)

Thank

--
Message posted from http://www.ExcelForum.com

```
 0
6/11/2004 7:11:40 PM
```Hi
this is nearly the maximum numeric value Excel can process. So it looks
in your column and returns the last numeric value which is smaller or
equal than this large number. So in all realistic cases this returns
the last NUMERIC value in a column

--
Regards
Frank Kabel
Frankfurt, Germany

> Hey this works fine!!  Just out of curiosity, how is this working?
> What does the 9.99999999999999E307 represent?
>
>
> =OFFSET(\$F\$2,0,0,MATCH(9.99999999999999E307,\$F2:\$F65536),1)
>
> Thanks
>
>
> ---
> Message posted from http://www.ExcelForum.com/

```
 0
frank.kabel (11126)
6/11/2004 7:22:27 PM

Similar Artilces:

How to freeze a range in excel 2003and 2007
I want to freeze a group of cells in excel 2007 but when I click on the freeze panes, top row or first column: nothing happens- I mean it scrolls up and down. Also is there anyway I can print an excel worksheet which is several pages long and still have the headings (which is in say the first 3 rows ie A1 - E1 down to A3 - E3) printed on each page? Lets say the headings are on only page 1 but i want it to be on page 2,3,4 to the last page when printed. Can freezing do that? Thanks. Hi In order the Freeze Panes, your cursor needs to be one cell below, and one cell the the right of the Row an...

service calendar in MS Dynamics 4.0
Does anyone knows how we can achieve the following requirement with MSCRM 4.0 In the service calendar in MS Dynamics 4.0 you are able to create new appointments and service activities. Appointments and Service Activities which are created will be present in the Service Calendar. To see all details of such a record[Subject(default Value)] you must open it. But by moving your mouse on the concerning appointment or service activity, a text-box-view appears with primary information. With appointments the content of the following fields is displayed: 'show time as', 'subject...

Insert random number only if cell empty
Hello. I'd like my spreadsheet to place a 7 digit random number into a cell only the cell is empty, otherwise leave the contents as is. I simply don't want the number to change if one is already present. I currently keep the number static by pressing F9 when I enter the RAND formula, but that forces me to manually change the number when I save the spreadsheet to another name. I'd like the spreadsheet to automatically calculate a new number in the cell as soon as I erase the previous number in that cell. Any help on this issue will be appreciated. Thanks. "M.L.&qu...

Relative name range issue
I was looking at John Walkenbach's excel tips page (http://j-walk.com/ss/). In one example, he shows how to create a named range that is RELATIVE, not ABSOLUTE. By clicking on, say, cell A1, then opening the named range dialog from the menu (Insert\name\define...) and defining a name such as "CellBelow" that refers to cell A2, you've created a relative reference. Anytime you type in "CellBelow" - in any cell - the value returned will be the value of the cell directly below the cell you're on. However, this appears to work only when you're on the ...

Macro to switch to another sheet, grab ref to any cell, paste ref in current sheet??
I am looking for a way (either macro or VBA) to perform one simple task: while in *any cell* of your choice in the current worksheet, switch to another specific sheet, choose *any* cell, grab a reference to that cell (not its value), switch back to the first worksheet and slap in the reference of the cell in the other sheet. This would be the automated equivalent of doing the following manually: 1) Select *any cell* (say *F11*) in the current sheet (Call it "*Destination*"). 2) Press "=" (no "ENTER") 3) Click the tab for the other sheet (call it "*Source*&...

Counting number of records based on criteria
Hello, (Try again to get the question in the google-groups. Perhaps not pushed on the send button). For counting records as expression in a report in ms.access I'm using =count(*) which shows all records. Now I also want to know how many of them has registered a specific data in a specific field. I'm trying to use =count([Field1]="999") to count how many of the selection reported has registered data 999. This option isn't work. Can somebody give me the correct solution. thanks, Johan The 999 needs to go in the criteria for the query driving the...

dynamic resource
Hi, Anybody know how to load e.g. bitmap into resource dynamicaly? The bitmap file will be downloaded by application itself , so it has to be somehow inserted into resource and displayed. thanks What do you mean by "into resoure dynamically"? Do you want to load a ..bmp file and display it? If yes, take a look at GDI+, which is by far the easiest way to achieve this. ---------- Ajay Kalra ajaykalra@yahoo.com I misspelled this, yes I'd like to load new bitmap on the fly. But resource file looks like is static. I use ATL/MFC applicatation. "Ajay Kalra" <aja...

Stop named ranges being copied
I want to copy a worksheet from one workbook to another. But I do not want to copy the named ranges associated with the first workbook to the second. How do I prevent named ranges being copied when I move sheets. >>How do I prevent named ranges being copied when I move sheets. You can't. A named range is just as much a part of a sheet as anything else. Delete the name(s) after you copy a sheet. -- Jim <bmatheson@gmail.com> wrote in message news:1183722346.818571.213860@q75g2000hsh.googlegroups.com... >I want to copy a worksheet from one workbook to another. > >...

Dynamic sum in Chart
Hello: So I've got this pivot chart that I've created, and it's great, EXCEPT when I select different categories to show up, I want to show a SUM of the categories, not the values of each category. I would appreciate any help/advice you may have. Thank you! Michelle In the pivot table you can select different thing like count and SUM etc. If you right click in the upper left vacant square and chose field settings, then select what you want to see in your data section. Misha wrote: > Hello: > > So I've got this pivot chart that I've created, and it's great...

Tab seq dynamically...
I have a button which added dynamically to a property sheet. Now for setting the tab sequence with this new button, Am using SetWindowPos() method. Now tab sequence is proper. THE PROBLEM IS Through the arrow keys, I could not navigate to the last button for which I called SetWindowPos. where as through tab keys I could go to the last control. I will appreciate ur help in this. DETAILED DESCRIPTION I have three buttons (OK,Cancel, Help)on the property sheet. I added a button "Default" to the property sheet and positioned between Cancel and Help. I set the tab sequence as ...

How to filter color changed cells
Hi guys, I am writing a macro to find the special char in the Balance column(ie. only Numeric format) Its finds and changed cells interior color on the particular column, whiever cells contained specl. But my question is HOW TO FILTER THE CHANGED COLOR CELLS? Is filter possible to changed color cells? Plz help Advanced thanks Venky On Aug 6, 2:45 pm, mvenkatesa...@gmail.com wrote: > Hi guys, > > I am writing a macro to find the special char in the Balance > column(ie. only Numeric format) > Its finds and changed cells interior color on the particular column, > whiever ce...

Typing in cells: New Paragraph ??
I am fairly new to Excel and this may be a very basic question, so I thank you in advance. I would like to type in a single cell, and be able to start a new line in that cell. Usually this can be accomplished in every other program by either pressing; return, enter, shift+enter, or (less than sign)p(greater than sign). How in the world can this be done in this mysterious Excel program?? I appreciate any help, as I have no idea how to even search for this question. Thanks. Joanna -- Frustrated Word User Try using Alt+Enter -- Rick (MVP - Excel) "Frustrated Word User"...

Truncate number in cell
I want to input a number into a cell say for example: 1,515,199,455 and have Excel automatically truncate the last 6 digits off so the number ends up: 1,515 Can this be done? Thanks, The procedure depends on exactly what you want to do with that "1,515", And if you want it to *really* be 1,515, or just *display* as 1,515, where the original number is still the original value. Also, will you want to revise other numbers with this same procedure? If so, will their "size" be comparable to your example? Just a couple of ways: =--LEFT(A1,5) =TEXT(A1,"#,###,,")...

How to load specific range of html into Excel?
For excel 2003, does anyone have any suggestions on how to load specific range of html into Excel? For example, I would like to load following link for HKB into sheet(HKB), because the page is so long, and I cannot import the whole page into excel with limited rows. http://www.hkex.com.hk/eng/stat/dmstat/dayrpt/dqe100302.htm#HKB I would like to load starting from "CLASS HKB - HSBC HOLDINGS PLC" .... [all texts are included] until the following text is found, then stop loading, and the following text is not included. "CLASS HKG - HONG KONG & CHINA GAS" ...

SharePoint Alternate Access Mapping to my Dynamics GP BP site
Hello, If I have posted this question in the wrong forum please let me know! I’m looking for some guidance on changing the Alternate Access Mapping for my Dynamics Business Portal site. So I can implement Single Sign On via ISA Server which is supported on one web listener. Present configuration: Default Zone: http://geneva:222 I would like to add: Internet Zone: http://dynamicsbp.proclub.com I presently have another SharePoint Collection (The Main Portal) http://geneva configured which listens on Port: 80. I tried to add the Alternate Access Mapping http://dynamicsbp.proclub.com...

dynamic charting #2
Hi I have to update a weekly spreadsheet With week numbers in column B x series and three y series percentages in G,H,I columns. Could someone please help I need these to update automatically. If someone can explain in detail I would appreciate 39 2097 2165 68 1770.7 97 96.86 118.43 40 2443.15 2201 -242.15 1548.93 97 111.00 157.73 41 2162.83 2259 96.17 1600.78 97 95.74 135.11 42 2163.08 2241 77.92 1545.1 97 96.52 140.00 43 2137.5 2271 133.5 1664.6 97 94.12 128.41 44 2160.67 2298 137.33 1455 97 94.02 148.50 Ta Brian ...

Assign value based on sort
Hi Groupies! I am in the final stages of a database for a golf tournament and I would like to automate the flighting, hole assignment and tee time for the final round. I have a query (qrySatFlights) that calculates a total score based on the first two rounds of golf, the field is called SubTotal. Based on the value in SubTotal, I would like Access to assign the correct values in the Flights field, the SatHole field and the SatTeeTime field. Here is what needs to happen: For the first 9 flights there are 16 golfers. The 16 players with the lowest SubTotal go in the Championship Flight...

How to add cells but keep 2 lowest out of solution
Hello, I need help with adding up a column of values in 'X' amount of cells, but I don't want to include the lowest cells in the addition. For example, I want to add cells A1:A5. But I want the answer to eliminate the two lowest cells out of the equation. This is a sheet I update daily and I need the sheet to recognize the lowest cells daily. The lowest cells will always be changing. Please, can someone help? I have Office 2003 for Windows. If you need more information from me, please let me know. =SUM(A1:A5)-MIN(A1:A5)-SMALL(A1:A5,2) -- Jim Cone Portland, Oregon ...

Need a method to Dynamicly change the X axis Scale on a chart. #2
I dynamically change the amount of data on a chart. This causes the X axis Scale to either to either show too few values or too many values ( a solid black line if too many). I would like a method to dynamically change the scale values in relation to the data span. Joe - Do you want to limit the number of points displayed? http://peltiertech.com/Excel/Charts/Dynamics.html http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=246 Or do you want to link the axis scales to something you calculate in the sheet? http://peltiertech.com/Excel/Zips/SwitchXYAxes.zip - Jon ----...

exchange content of cells
If i have 2 cells (or groups of cells) is there a way to exchange the contents or do I have to copy/paste the first contents into empty cell, then copy/paste second contents into first cell, etc, etc.... Thanks. Jeremy -- Message posted via http://www.officekb.com Take a look at: http://www.mvps.org/dmcritchie/excel/swap.htm -- Gary's Student ""jeremy via OfficeKB.com"" wrote: > If i have 2 cells (or groups of cells) is there a way to exchange the > contents or do I have to copy/paste the first contents into empty cell, then > copy/paste second conten...

Dynamically populate a drop down list
Hi, I'm trying to dynamically populate a drop down list in MS CRM (1.2) based on certain parameters. I suppose I could do it using a SQL stored procedure, but I would prefer to use standard/supported CRM customizations. Any assistance in this would be greatly appreciated. Thanks! -Dave- Dave, You can do it in 3.0 pretty easily based upon an example in the SDK... Here are some pretty pictures... http://blogs.msdn.com/midatlanticcrm/archive/2005/12/04/499868.aspx I have not tried it with 1.2, but it could probably work... Thanks Ben "Dave" <renor321@yahoo.com> wrot...

Sum amounts based on date field
I have two columns. Column A is a date time column. Column B is an amount column. I want to calculate the amounts by the hour and place the hourly totals in Column C and the date time of that hour in Column D. Below is an example of the data and how I would like Column C and Column D to look. Column A Column B Column C Column D DATE AMOUNT TOTALS/HR DATE 01/01/2009 01:10:10 50 75 01/01/2009 01:00:00 01/01/2009 01:20:05 25 10 ...

CRM Integration with MBS/Dynamics products
Hi, Anyone know anything about the integration between CRM 3.0 and ...say.... Dynamix AX/Axapta? I'm thinking sharing of customer data and such.... Jeppe Jespersen Denmark There is nothing available yet. You would need to build it. -- Matt Parks MVP - Microsoft CRM "Jeppe Jespersen" <jdj curly jdj dot dk> wrote in message news:OAfVImWLGHA.3276@TK2MSFTNGP09.phx.gbl... Hi, Anyone know anything about the integration between CRM 3.0 and ...say.... Dynamix AX/Axapta? I'm thinking sharing of customer data and such.... Jeppe Jespersen Denmark ...

Dynamics GP 10 and Dynamics AX 4.0
just a half year ago, our company has started to work with Dynamics AX 4.0. Now i am doing some programming in X++ and almost customizing reports. Accidently, i stumpled on Dynamics GP 10.0 and i am wondering what s the difference between AX and GP? What stands GP for? Maybe GP is to replace AX, and our company backs to wrong horse? Does GP 10 contains AX 4.0? I have learnt lot of X++ programming, would it be possible to using X++ in the GT 10 field, too? Great Plains (GP) is another ERP system that Microsoft acquired a few years ago when it acquired the company of the same name. GP ...

Copy cells with data and paste
Anyone, I am trying to do what I hope is a simple task. I need to have a two part macro that will do the following. 1. Copy only the cells in a certain range that have data in the cells. 2. I then need the macro to paste this data into a new worksheet on this first available blank row thanks in advance for anyones help on this. Bill To learn, Record a macro while use data>filter>autofilter to filter the NON blanks>copy>paste> Then clean up the macro To determine the last row use lastrow=cells(rows.count,"a").end(xlup).row+1 Post back with further questions. -- ...