Inputting cell co-ordinates into cells

[eBopBob]

Hi there

I've a quick question. To keep the example sweet and simple, I'll use the =sum formula as an example.

Is it possible to have values in the A1:A10 cells, and then in B1 have =SUM(A1:A10) but instead of that, actually have say 'A1 in cell B2 and 'A10 in cell B3, but then as time goes on, more values are added to column A, so it goes from A1:A20 say, and then I simply change cell B3 to 'A20 so that my SUM function will now take into account all new values.

Is this at all possible?

I'm actually hoping to do something more complex, but don't really want to use VBA/macros to do it.

Cheers
eBopBob

[Ron Coderre]

What do you *really* want to do?
...reference a range that expands and contracts?

[eBopBob]

Thanks. That is, I think, what I'm after. (Sorry, my Excel knowledge isn't great!)

Essentially I have a lot of data and will accumulate more as months go on, and will be calculating a number of metrics based on this data. However ideally I don't want to update every formula as I get more data, I'd rather modify a cell reference or something.

I hope this clears it up?

Many thanks
eBopBob

[Pepe Le Mokko]

If there is nothing else in col A why not =SUM(A:A) ?

[eBopBob]

If there is nothing else in col A why not =SUM(A:A) ?

Thanks for the reply.

It's because I was only using that as an example.

In one tab I have lots of data. Products horizontally and prices vertically. As months go on, I get more pricing data and add values. The A column on this tab is dates. Maybe as time goes along I'll get more products too.

Then on another tab, I want to run different metrics, and especially I'm looking at calculating a whole load of metrics. Rather than create a new page per product, I'm hoping I can create one page, and then in the top left hand corner input some variables that allow me to change which product I'm calculating metrics on, and then as I add more data, either the formulas auto update (using offset function somehow) or I change a parameter in the top left hand corner of this tab which will then feed through.

[Pepe Le Mokko]

Perhaps named dynamic ranges ?

[llamafarmer]

I like named dynamic ranges and they are fairly easy to adjust, but with lots of data can become cumbersome.

Assuming I have understood what you are after....

Another option would be to have a dummy line at the bottom of the sheet that the formula also adds (as there is nothing in this it will not affect and final numbers. The reason for this is that you can right click on that line and insert a new line, all your formatting will be adjusted and this will be included in any new totals.

see attached

[Pepe Le Mokko]

I like named dynamic ranges and they are fairly easy to adjust, but with lots of data can become cumbersome.

Could you please explain how a dynamic range :
1 Should be adjusted
2 Why it is cumbersome ?

[llamafarmer]

Dynamic ranges are a cell or group of cells that you give a specific name to.

if you select a group of cells then in the top right of the cell grid you will have a cell reference, type a name in there and you will have a name range.

1) To change this name range you will need to goto the forumlas tab and from there you click on name manager. From there you need to double click on the name range you wish to change and adjust the refer to refences to suit.
2) Name ranges will always start with locked name ranges $ prefix, meaning for every row you insert it will automatically exclude this from the name range, you will need to go into the name manager remove the $ prefix and you can adjust the name range dynamically by inserting rows and having them included. They become cumbersome when you constantly changing your ranges from this stand point as you will be constantly having to check to ensure that they have actually been included in your calculations.

I use name ranges all the time as part of my work but typically for large areas that are not likely to change therefore no issues with mis-calculations.

Name ranges are excellent when it comes to formulas across multiple workbooks as it helps reduce the size of the formula in the formula bar.

[Pepe Le Mokko]

@llamafarmer

You seem to be confusing "named ranges" and "named dynamic ranges".
As the latter's name suggests, they are dynamic and adapt automatically to any change in the length and/or width of the range.
Have a look at the link I provided to see the various techniques that can be used.

[llamafarmer]

@ Pepe

WOW, thank you very much for that, I see now why checked that was talking about the same thing, you are completely right I did get the 2 confused.

Thank you.