Applying a formula to a dynamic range

You may not really need that third dynamic named range. With no context here, I'm guessing at what you're trying to accomplish, but once you have defined two adjacent identical ranges, those names behave in a way you might be able to create the "values" that would have been in the third range without them actually existing at all.


I created MyNum1 and MyNum2 as ranges with your formulas. Then here's a simple formula that adjusts itself to provide what the value of the third column would be if it existed:

http://screencast.com/t/fM1ENjRF

---------- Post added at 06:48 PM ---------- Previous post was at 06:44 PM ----------

Ok, I think I see your actual goal now, to have a named range with values in it that don't exist in the sheet. That's doable. All you need are the values in A and B, and your first two named formulas. Then a third named formula that creates an array:

http://screencast.com/t/jAu5qLng5ZAV


In this pic you see the third named range formula, and an example in a cell showing how the third value in that array is being pulled out.

Why aren't you putting your formulas in cells? A convoluted path is not better than a simple one. Cells are free, extra sheets....no charge.

I wonder why people persist in creating dynamic ranges ( and using volatile functions to define them) when XL2010/2007 provides the Table facility? ( especially in this case)

That's not how tables work. Once you're table is created it typically only has a few rows in it and all the formulas and formatting are applied, then you add more rows to the bottom of the table as needed and all the formatting/formulas add themselves to these added rows. There would be no "500 blank rows" because that's not how you do it.

mind you why use offset at all?use index
A2:INDEX($A$2:$A$5000,COUNT(Sheet1!$A$2:$A$5000))
if its the sum of all a*b then use that with sumproduct

This latest question seems completely unrelated to the thread topic. I would suggest posting a programming question in the Programming forum, possibly with a sample document to illustrate to the helpers what you're trying to achieve.

It appears this thread topic has been resolved, please select Thread Tools from menu above and set this topic to SOLVED.

Originally Posted by willist

That was my mistake for opening another thread. Apologies.

So looking at the other post you made...

"You have XL2010 ? Then use the Table feature - Named ranges will not be necessary

First select the range formed by col A and B - Select the Insert Ribbon - Click "Table" and follow the instructions
Next, in col C add a header if needed - in the cell next to 1st row of data col A and B, enter your formula, it will expand automatically to the last value of col B
Now when you add or delete values to or from col B, the formula in col C will follow without you having to do anything"

This works very well, but if my goal is to graph the data, then there will be alot of extra entries that will mess up the data in the graph. That is why I wanted to have ranges.

For instance, assume I make a table that allows for up to 1000 entries and it has three columns, the third of which is calculated.

If the user enters 500 entries into column A & B, the the results in column C are A+B, then the second half of column A will be filled with all 0's, messing up the graph.

This is why I wanted to know how to apply a formula to a dynamic range.

Thanks,
Willis

I suggest you create a simple table with according graph to see how it works, instead of assuming...