Easy Excel problem(?): dynamic cell address

[mieskaukaa]

=MATCH(1;E49:E52;0)

Looking for number 1 with the above formula, but since the array changes from time to time, I would like to change the array address as well. Is there a way to manipulate cell addresses so that instead of fixed cell addresses I'd have two inner functions that return the start and end cells of the array.

Like this =MATCH(1;(formulaforcell_1)formulaforcell_2);0).

Even simpler example: if cell's formula is =E48, returning the value of cell E48, how can I manipulate that formula that it returns the value from row E and column of my choice? Like =E(formula_for_column_number) ...

Thanks a lot in advance, this one really bugs me.

MK

 

[SimonDavis]

Quick answer - use range names.

I did an FAQ on it a while ago, have a look at

http://www.tek-tips.com/faqs.cfm?spid=68&sfid=597&CFID=27155566&CFTOKEN=1187719

If you need specific help, just post back.

 

[mieskaukaa]

Ok, thanks. It looks like the right stuff, I just don't get how I can grow the range. How could I add a cell(either up or down) to a, say, single column range of five cells? I need to do that with a formula or function, not manually.

Thanks again,

MK

 

[pcdaveh]

What your talking about I term or call it a Dynamic Range. I personally have only found one way to deal with the issue. Hopefully you and I both may run into someone else that has a better idea. But this is how I handle it. I always Define the borders of my Dynamic range with certain values or labels. You for instance can have a label that is called "Top" and another that is called "Bottom". You would then step through that particular column or row or seek/ find those labels. Kwowing there position defines the range. You then can delete within or insert. But always adjusting the boundaries of the range accordingly. That's the only way I know how to handle it.

 

[pcdaveh]

http://www.beyondtechnology.com/geeks007.shtml

Named ranges are among the most powerful features of Excel, especially when used as the source range for list controls, PivotTables, or charts. A problem arises, however, when the contents of a list change often. It would be a problem to have to redefine your named ranges everytime a table has records added or removed. The solution is to create a range that will automatically adjust based on the number of items in the list.

First, create a list in column A of a worksheet. From the worksheet's Insert menu choose Names then the Define.... Enter a name for your new range, such as MySheet!rngDynamic. Then, in the Refers to: box, enter the following:

=OFFSET(MySheet!$A$1,0,0,COUNTA(MySheet!$A:$A),1)


How It Works:

The first argument for the OFFSET function is the cell on which you want to anchor it. Everything else will be set relative the this cell address. Typically, you will want it to be either the header for the first field in your source data table or its first record.

The second argument indicates how many rows to move from the anchor address to begin the range. In this case, we used 0 which would include my header row. If we substituted 1 then the header row would be skipped and the range would begin on row 2 of the table.

The third argument indicates how many columns to move from the anchor address to begin the range. In this example we used 0, meaning to begin the range on the same column as the anchor address provided in the first argument.

The fourth argument tells how many rows the range should extend. They key here is to substitute the COUNTA function for your primary field, instead of hardcoding a value. This way if you add or remove items from that field, the range will grow or shrink accordingly. You also may need to subtract 1 from the COUNTA result to account for the elimination of a header row. It is important that you do not have any superfluous data beneath the table range you intend to evaluate.

The fifth, and final, argument is how many columns wide you want for the dynamic range to be. In our example, this range includes only a single column, therefore the argument provided was simply 1.

This is a very flexible technique for defining your named ranges. The best thing to do is to experiment with some variations of the sample formula provided and you will soon find that Dynamic Named Ranges will become an indispensable tool you will want to use throughout your Excel work.

Note:

Dynamic named ranges can only be referenced in an open workbook, because they are dependent upon a workbook's ability to calculate.