Display dynamic result range (2002/Any)

Topic

New Reply

Is there a formula to look at a range of cells “hidden” to the user at the far right side of a spreadsheet and display in column A? At the most basic level, if the data is stored in Z1 and I want to display in A1, I would put the following in A1: =Z1 But the hidden data shrinks and expands in terms of rows, and I’d like to create just one formula if possible. Can this be done? Thanks.

Reply | Quote

Replies

You can create a DYNAMIC range name using OFFSET
eg the refers to can be:
=OFFSET(reference,rows, cols,height,width)

Each of the variables could be static to define a constant column or width or based on a cell or range name.

I can’t give you a specific formula unless you give more details of how you will define this “hidden range”

Steve

Reply | Quote

I cannot get the OFFSET function to do exactly what I want. In simplest form I am trying to do this. Let’s say the values Name, 1, and 2 are sitting in cells C1, C2, and C3, respectively. I want those cells to populate in cells A1-A3 with a single formula in cell A1. Is using OFFSET my best option?

Reply | Quote

The only way I know of populating a range of cells in to copy the cell in A1 and paste it down the column. This can also be done dynamically with a macro. After the macro is run each cell in col A will have it’s own formula which references ( a cell in col C or Z for instance). But to put one formula in cell A1 that populates A2:A5 would be new to me.

Reply | Quote

A formula can not change a cell other than the cell it is in. You can use an array formula to sort of do what you are asking:

1- Select cells A1:A3 with A1 being the active cell.

2- Enter this formula:

=C1:C3

Hold down the Ctrl and Shift keys when you press enter.

That will put an array formula that will put put C1:C3 into A1:A3. You only entered the formula into A1, but technically the formula is in A2 and A3 and will display in the formula bar if you select any of the cells A1:A3.

If we knew what you are trying to accomplish, there might be some other way.

Reply | Quote

(Edited by HansV to correct tags)

I am still unclear what your “goal ” is. A couple of thoughts:

Technically, if you enter the formula into A1:

=C1&CHAR(10)&C2&CHAR(10)&C3

Select A1:a3
Choose format cells – alignment tab
and check “Wrap text” and “Merge cells”
The results are ONLY in A1 (A2 and A3 are blank), though they will LOOK like they are in A1:A3

Another option:
You could also use the camera tool
[To add camera tool to toolbar (it is NOT there by default):
View -Toolbars -customize
commands tab – Tools category
The command “Camera” is near the bottom:
“drag” into onto one of the toolbars. then ]

Once the camera tool is available:
highlight C1:c3, click on the camera tool [C1:C3 will be selected, the cursur will be a “+”]
Put the “+” where you want the upper left corner of the “picture” and drag the size of the shape (it does not matter what size you drag it will be a copy of the size of the the cells you select] you can move this LIVE picture. ANy changes to C1 and C3 will be reflected in this picture (formatting, anything]

If you select the picture you will notice in the formula bar it is labeled:

=$C$1:$C$3

You can change this to include the sheetname or even just change it to a RANGE name. [note if you select the camera tool and when it is the “+” cursor, you GO to a different sheet, the sheetname will automatically be attached].

Including the sheetname or using a range name allows the PICTURE to be linked to cells on a different sheet than the “picture”.

The range name can be a dynamic range name (created with OFFSET) so if you sometime want C1:C5, but when the list has (for example) “#N/As” ror some other indicator in C4 and C5 you only want C1:C3, or ONLY C1 in another instance, the Dynamic formula with OFFSET will automatically fix this

post 257079 has a couple of sites which talk about using dynamic links.

As another aside, this “picture” can be added to chart sheets allowing a “DataTable” of values to be added which can be formatted and can also change as your data changes (even the size can change, dynamically).

Steve

Reply | Quote

You said, “the hidden data shrinks and expands in terms of rows”

Hmmmm… Does that mean you want your formula “=Z1” to stay “Z1” even if you copy it all the way down Column A?
If so, just use “=$Z$1” Dollar signs “lock in” the column and row refs when you copy the formula.

I may have completely missed the point of what you are trying to do, but my gut tells me it can’t be as complicated as it sounds.

Reply | Quote

If you post here with an attached Excel file showing a simplified example of what you want, that would be helpful.
Using a VLOOKUP formula in A might be the answer if your “hidden data” is a lookup table. Instead of referring to a specific cell in Z you’d be “looking up” the right cell in the hidden lookup table by finding the appropriate match by date or customer number or income level or whatever…

Reply | Quote