Creating the OFFSET formula with a dynamic reference cell

**jmillikan** · 05-21-2015, 03:19 PM

Hi,

I have created the formula =OFFSET(A20, 0, 1, 10, 13). The formula works but I want the beginning cell reference, A20, to be dynamic. The cell A20 contains a particular text value (a name) that will always be the same text and would be the point of reference for the OFFSET function. However, that name may appear anywhere in column A. Tomorrow it could be in A25 or A30. What function could I use in the place of a direct cell reference that would find that text value in a specified range of column A and return the cell reference where the text is found?

Thanks!

**nigelbloomy** · 05-21-2015, 03:43 PM

This sheet shows one way that might work. The formula in C22 gets the reference and the formula in E22 shows how your offset formula could use that reference.

Edit: I thought A20 held the text that you would be referencing every time. I read the question wrong. The answers below work much better.

**bhenlee** · 05-21-2015, 03:44 PM

Try this jmillikan:

=OFFSET(INDIRECT("A"&MATCH("TEXT",$A:$A,0),1),0,1,10,13)

Let me know if it works.

**sktneer** · 05-21-2015, 03:45 PM

You may replace A20 in the offset formula with Index/Match to find the text in col. A like this.....

=OFFSET(INDEX($A:$A,MATCH("Name",$A:$A,0)), 0, 1, 10, 13)

Where "Name" is the text you are looking in col. A

**jmillikan** · 05-21-2015, 04:01 PM

Thanks bhenlee and sktneer! Both methods worked. I had tried to use both of those methods and couldn't get it to work, but obviously I had something wrong. I'm just not sure what. Thanks for the quick response! I have added reputation to both!

**sktneer** · 05-21-2015, 04:05 PM

You're welcome. Glad we could help.

Creating the OFFSET formula with a dynamic reference cell

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Creating the OFFSET formula with a dynamic reference cell

Re: Creating the OFFSET formula with a dynamic reference cell

Re: Creating the OFFSET formula with a dynamic reference cell

Re: Creating the OFFSET formula with a dynamic reference cell

Re: Creating the OFFSET formula with a dynamic reference cell

Re: Creating the OFFSET formula with a dynamic reference cell

Thread Information

Users Browsing this Thread

Similar Threads

Dynamic Chart with an OFFSET that has a Dynamic reference gives error

Using Offset formula to change cell reference

Dynamic Reference with Offset

Offset a cell using a dynamic (moving) reference cell

Offset using formula for cell reference

Bookmarks

Bookmarks

Posting Permissions