You are here: HomeExcelTutorials

# function like INDIRECT to return a range?

## function like INDIRECT to return a range?

Price: FREE

I'm using an array formula that will only accept a finite range as a parameter (e.g. A2:A99). The array formula will not accept a named range or column reference (e.g. A:A). I'd like to specify the range dynamically rather than hardcode it, for instance by using MATCH to find the first and last rows with relevant data.
Something like INDIRECT would be perfect, but INDIRECT can only return a cell reference, not a range (right?).

Visit publisher's web-site: function like INDIRECT to return a range?

Category and Tags: ExcelTutorials : : Functions

## Related Tutorials

### sums of range within sumproduct

Free: Yes
Tags: Excel ›

It's a bit complicated. I have the following function:
=SUMPRODUCT((INDIRECT(B1)=1)*(INDIRECT(\$A\$1)=2)*C1 )
I'd like to calculate a sum of the SUMPRODUCT(.) while varying B1 and C1 together (B1 references the name of a range, C1 references a number), and then apply the same formula to a new cell that replaces the named range \$A\$1 without having to retype everything. For example:
=SUMPRODUCT((INDIRECT(B1)=1)*(INDIRECT(A1)=2)*C1)+ SUMPRODUCT((INDIRECT(B2)=1)*(INDIRECT(A1)=2)*C2)+S UMPRODUCT((INDIRECT(B3)=1)*(INDIRECT(A1)=2)*C3)+SU MPRODUCT((INDIRECT(B4)=1)*(INDIRECT(A1)=2)*C4)
And then that sum repeated for A1-A4. But without having to retype

### INDIRECT function to reference a named range

Free: Yes
Tags: Excel ›

I receive errors when I try to use the INDIRECT function to reference a named range. For example, my formula {INDIRECT("RANGE"&A1)} returns an error when the same formula {=RANGE1} does not. For clarification, cell A1 has the number 1 in it. Is this simply an Excel flaw or can I use a work around. The function should be able to be dragged down so that the numbers in row A are combined with the name "RANGE" to reference a set of named ranges.

### How to create a range address with ADDRESS function?

Free: Yes
Tags: Excel ›

I can use this formula, =INDIRECT(ADDRESS(10,2)), but I haven't figured out how to specify a range as in =INDIRECT(ADDRESS(10,2) & ":" & ADDRESS(30,6)). I can put the range address into a cell and then reference the cell with INDIRECT, but it would be nice to limbed the range address calculation inside the INDIRECT function and save the step.

### Is there an INDIRECT function for a RANGE?

Free: Yes
Tags: Excel › Functions

I'm trying to find the equivalent of the INDIRECT function, but instead of a cell location, I'd like to "build" a Range. As an example, I'd like to convert the following text, "CONCATENATE("Sheet","'!A1:B4") to a range that Excel can understand. If this were simply a cell reference, then the text string could be the argument for the INDIRECT function. Is there a way to do

### Help with the INDIRECT function.

Free: Yes
Tags: Excel ›

Using the INDIRECT function I'm trying to reference data in another Workbook. However, if that workbook is not open the function returns a #REF! value. Open the Workbook it's referencing and it happily returns the value I need. I understand it's one of the volatile functions in Excel, but I don't know why it won't return return the value if the other sheet is not open.
Any ideas to help at all please?

## Related Applications & Scripts

### Range Search by location

Free: No
Tags: PHP › Excel Geolocation Web Search

This script return all cities in selected range and address, is useful when searching nearest city or point of interest like shop. Simply pass a location and the search range and it will return cities and distance. The script use Yahoo API for geocoding location, the pack include also sql table for local database, you must insert geolocation data into it.

### Multi-thread Simulation in PHP

Free: Yes
Tags: PHP › Excel Classes Performance

This class can emulate the execution of program threads using separate HTTP requests to the same script. It establishes an HTTP connection to the same Web server to execute the same PHP script. It sends a request passing the name a function to execute and an argument to be passed to that function. The requested script executes some code that detects the thread execution request and calls the specified function. When the thread request script ends, the return values of the called function is returned as a serialized string. The calling script can execute other tasks while the thread script runs. The results may be collected later when the thread script ends.

### PHP Map + Location Plugin

Free: No
Tags: PHP › Excel Geolocation

This is a collection of location based functions that can get a users location based on their ip address using the IPInfoDB api, return advanced details on a specific location with the Yahoo PlaceFinder api, or dynamically create maps (as images or with javascript) using the Google Maps api.
This function interacts with the IPInfoDB.com API to get a users location based on their IP address. It can return city, state, country, latitude & longitude and is accurate within a 25 mile radius. This function accepts an IP Address and returns an array of location based values.

### Bookmark Popup

Free: No

Do you want your visitors to return to your site and share your message with friends, family, and coworkers? The easiest way to keep visitors coming back is to encourage them to bookmark your site. By bookmarking a website visitors are reminding themselves that they want to return to your site.
How can returning visitors help your web site?
Not only will this increase your sites basic but it also increases your sites popularity. Many web site owners do not realize that a sites true popularity is based upon its repeat visitors. The number of repeat visitors shows that your site is so appealing that it not only attracts many unique visitors, but it also encourages these visitors to return time and time again. If you are someone that is selling a product the majority of sales and conversions are made because of return visitors. It often takes many visitors more than one visit to a web site to decide whether or not they are going to make a purchase. I myself will often visit an online store several times before committing myself to an actual purchase.

### ASPajax: AJAX Framework for Classic ASP

Free: Yes
Tags: Ajax › Excel ASP Frameworks

AJAX is an increasing expectation of modern websites. However many of the world best developers still code in Classic ASP - without the use of heavy web frameworks.
ASPajax bridges the gap - providing .Net Ajax style functionality and "Update Panels" to VBScript ASP.
ASPajax also allows you to easily call any ASP server function from JavaScript and return results without refreshing the page.
Lightweight, simple and fast - ASPajax is free.
ASPajax Basic Concepts
ASP functionality can now be accessed without refreshing the using 2 new AJAX paradigms.
UpdatePanels - can allow any form or link to work seamlessly without the page refreshing. UpdatePanels lead to efficient development - and encourage strong adherence to Accessibility and W3C standards.
LiveASP - a Remote Method Calling technology - that allows you to use any ASP function from client JavaScript - without refreshing the web page. The results from the ASP function is returned - and Session and Cookie states are kept alive.