Allow User To Input Cell Reference

[lmatonement]

I have a sheet with some data on it. I have another sheet to calculate a report from the data on the other sheet. To calculate the report, I need a “starting cell reference” whence other important cells can be referenced. How can the user input a “starting cell reference”? If the user types = then clicks the cell in the other sheet, I’m not sure how to get, for instance, the value of the cell below that cell (etc.). Perhaps the user needs to type the cell reference ("c6") directly? Then I can prepend the sheet name, dereference the cell, and construct neighbor cell references from thence? But I’m not sure how to deconstruct the cell reference. For instance, from “c6”, how do I get “c”? I tried =row(c1) (c1 is the cell containing "c6") and it returns 1!

What’s the best way to do this?

[Lupp]

I didn’t actually understand the question clearly, but suppose that the OFFSET() function is what you need to use.
Read the help and come back with additional questions if I missed the point.

[fpy]

@Lupp

what

… or maybe ADDRESS, INDIRECT, OFFSET, INDEX - Apache OpenOffice Wiki

[lmatonement]

Say you take user input in your report sheet on cell B1. The user enters C6 into B1 instructing the report sheet to begin its calculations at C6 of the data sheet.
To get the value of C6 on the data sheet, you would

=indirect(address(row(indirect(b1)), column(indirect(b1)), 1, 1, "data")

To get the next row down:

=indirect(address(row(indirect(b1))+1, column(indirect(b1)), 1, 1, "data")

etc.

offset sounds better, but I’m not sure how to make it work like this.

[lmatonement]

I think I got it:

=offset(indirect("data!" & b1), 1, 0)

will get the value of the next cell down on the data sheet! This is also nice because it’s very easy to create ranges from OFFSET: =sum(offset("data".c6, 0, 0, 3, 1)) sums all the values in data.c6 and c7 and c8.