Return cell value 30 cells after reference cell

**shshsh** · 04-01-2014, 10:20 PM

Hi I want to find the cells 30 past the reference cell and the corresponding value:

ie =Sheet1!D312 to =Sheet1!D342 (=Sheet1!D(312+30))

or

=Sheet1!D312 to =Sheet1!E312

is there a way to automate this without having to manually edit each formula?

**sktneer** · 04-01-2014, 10:33 PM

May be this.....

Please Login or Register  to view this content.

In this case D312 is the referenced cell and the value is returned from the cell D342.
Is this what you want?

**shshsh** · 04-01-2014, 10:35 PM

That is PERFECT!! Thank you so much!!!

**sktneer** · 04-01-2014, 10:37 PM

Glad to help you. Thanks for the feedback.

**shshsh** · 04-01-2014, 10:46 PM

Just one more question, how would I refer to the formula in the cell? ie

lets say my cell shows :

19/03/1999 the formula being =Sheet1!D312

How would I use =offset in this situation? If I do =offset(F4,-1,0) then it will use the current sheet but I want it to refer to the reference of F4? Does that make sense?

So without having to =offset(Sheet1!D312,-1,0) each time manually?

**sktneer** · 04-01-2014, 10:52 PM

I am not clear, what do you want to do exactly?
The formula =offset(F4,-1,0) will return the value of cell F3. In the formula F4 is the referenced cell.

**shshsh** · 04-01-2014, 10:54 PM

Capture.JPG

I want to automate that.

**sktneer** · 04-01-2014, 11:12 PM

Better if you upload a workbook rather than an image. Highlight the cell which have formulas and the expected values in them with an explanation about how do you expect those values in them.
Because in the image I don't think that your referenced cell are in a particular or set order.

**shshsh** · 04-01-2014, 11:57 PM

TLS data.xlsx
I want to be able to automate the formula so that I don't have to manually type in =offset(Sheet1!D720,-1,0)

I would like to be able to drag the formula down to complete for the rest of the data set as well.

Please see sheet 5

Thanks in advance