Get row number from adjacent formula row reference

use the OFFSET function to return the value of a cell that is a specified number of rows and columns away from a cell or range of cells that you referenced in an adjacent range

How to Use the Offest Function inExcel
http://support.microsoft.com/kb/324991

For instance the following formula sums the values of the two adjacent cells above-left, and above, with the one above itself:
=SUM(OFFSET(D7673, -1, 0), OFFSET(D7673, 0, -1))

Once this formula is set in Cell D7673, it can be copy/pasted into any cell and retrieve the same relative sum.

=CONCATENATE("Birthday: "& TEXT(OFFSET(formula),"mm/dd"))
..may be what you are looking for.

Hi, rsknow.

I'm not sure I've understood your question correctly as your "Birthday" formula should work (assuming it's in cell G2). So, enter it in G2, then copy and paste that cell to the cells in Column G as far as the last used row.

As any change in the sequence of either sheet would break this, a lookup would be a better option. That would require some unique information (e.g. membership no.) to be on each sheet.

Regards,
Brian.

I can see that I've not been clear.

The "Birthday" formula does work.  In my example a member name is in cell C3.  The same member's birthday is in cell H3.  I have laid out my membership directory with 3 rows per member, 10 member's data in column A & B, then another 10 members in column's C & D, etc.  across the worksheet.  The next group may be dozens of rows away because of the particular layout for the booklet.  

So, I was hoping for a way to get the row referenced in the member name and use that same row number in the Birthday formula.  I can see it in the address bar but don't know how to extract the row number from the formula.  With the row number referenced in the member name formula I can use a Lookup to get the value for the birthday.

It's pretty clunky but this formula just worked:
=CONCATENATE("Birthday: "&TEXT(VLOOKUP(LEFT(AU3,FIND(".",AU3)-1),NamesForRoster,9,FALSE),"mm/dd"))

I guess I just answered how to enter a formula I can use to solve my immediate problem.....but I still don't know how to get the row number referenced in the first formula of my question.  Is there a way?

rsknow,

Not sure I'm there yet, but the following would return 3...
=Row('Names for Roster'!$C$3)

Any chance of a sample file, please?

Thanks,
Brian.

Wouldn't I have to already know what the referenced row is in order to use your formula?  A sample file is attached.
SAMPLE-ROSTER.xls

Hence my request for a sample. Thanks!

rsknow,

I'm even less confident that I'm giving you what you want, but the attached doesn't use Lookup.

The formula is ...
="Birthday "&TEXT(OFFSET('Names for Roster'!$I$1,(COLUMN()-6)/2*10+(ROW()-1)/4,0),"mm/dd")

Cheers,
Brian.
SAMPLE-ROSTER-V2.xls

In the sample workbook the formula works with the Roster List which is all in columns E & F.  

In my actual workbook their are multiple columns such as E&F and G&H and I&J, etc.  The offset formula only works for E&F.  What would need to be changed to make it work with the other column sets?

Em... nothing? The formula not only works for those columns, I actually included it for them.

I'm afraid that's me finished for the night, but I will be back later this morning.

Cheers,
Brian.

Hi Brian,

I went to bed too -- probably same time zone. I'm in central coast of CA.

My Wild Blue satellite box went dowm this a.m. so this is from my Droid.

I copied the sample file, col. E and F to col. I and J.  Your formula in cell F5 works.  It does not look up the same row in cell J5.  It returns the birthday for row 22 on 'names for roster'.  

rsknow,

Hi again. Apologies that it's still not right for you. The attached file is exactly the same as V2 except that I've changed the dates on "ROSTER LIST-BOOK COLLATED" so that they run from the 1st of August to the 26th to make it clear what my formula was trying to do.

It's doing exactly what I wanted so obviously my understanding is still incorrect. Could you look at the attached, please, and show me a couple of the entries that are wrong and what they should be?

Thanks,
Brian.
SAMPLE-ROSTER-V2b.xls

Why doesn't the information I provided work for you?

Here's an example...
If I paste the following into F9 of Roster List-Book Collated sheet
=CONCATENATE("Birthday: "&TEXT(VLOOKUP(LEFT(OFFSET(F9, -2, -1),FIND(".",OFFSET(F9, -2, -1))-1),NamesForRoster,9,FALSE),"mm/dd"))

Then I can copy/paste that into any other intended birthday cell on the sheet where the name of the intended person is up two rows, and to the right two columns.

Isn't that what you are after?

Note about copy-populating this on your sheet... the idea is that in using a relative reference in the cell hosting the forumla (in my example that begins with F9), the key is that it is a reltive refence to itself.

So now, wherever that formula is copied to on the sheet, it will maintain the relative reference explicitly from the host cell you are pasting into.

This is commonly used in situations where you have to add or remove rows or columns or cells surrounding an equation-cell without disturbing the precise relative relationship of the calling function (within it) to a cell range that is intended to be always x/y moves away from the host equation-cell.

Hope that helps.

Correction... "where the intended person is up two rows, and to  the right two columns" should read
"where the intended person is up two rows, and to  the left two columns" (...from the new equation-host-cell you are pasting into).

Get row number from adjacent formula row reference

The title of my problem explains what I wanted to know.  It may not be possible.

If a formula includes a row reference such as $C$3 I want to be able to get the number "3" out of the formula.  Does Excel have a way to get that information?  

In my example the number is shown up 2 rows and left 1 columns from the formula cell.  I should be able to copy the data in the first 2 columns and get the same birthday when these columns are pasted in another location.  See the Sample-Roster2 attached.  Your formula does not return the same birthday.

Look at my comment on 10/5 at 8:34 pm.  " .....but I still don't know how to get the row number referenced in the first formula of my question.  Is there a way?"
SAMPLE-ROSTER2.xls

rsknow,

You mentioned above that "multiple columns such as E&F and G&H and I&J,". That's what my formula was set for. You latest example uses E&F and H&I hence the formula needs to be changed.

I'll do that and repost.

Brian.

rsknow,

Please see attached.

="Birthday "&TEXT(OFFSET('Names for Roster'!$I$1,(COLUMN()-6)/3*10+(ROW()-1)/4,0),"mm/dd")

Brian.

rsknow,

Oops.

="Birthday "&TEXT(OFFSET('Names for Roster'!$I$1,(COLUMN()-6)/3*10+(ROW()-1)/4,0),"mm/dd")

Brian. SAMPLE-ROSTER2-V2.xls

Apologies, got my embedding mixed up.

Hi Brian,

I created a VB module and pasted the function into it.
I entered "=xCellFormula(E3)" in G3 this returns "#NAME?".  How do I get the program to recognize the function?

This seems to be on the right track for what I was wanting.

Susan

By golly!  It worked.  Now I can parse the formula to get the number reference I want.  As the number changes so will the answer. Yahoo!

Thank you!

Susan

Thanks for sticking through and understanding my desired result.  I will use this function many times.  Great!

Susan,

Glad to hear that solution pushed your buttons.

If that spreadsheet's more than a one-off, you should think seriously about  a Lookup (or, better, a Match) solution.

Brian.

Good job identifying the need for cellformula, red ;^)

Using redmondb's celformula, rsknow, you could try this...


=LEFT(RIGHT(xCellFormula(E3),LEN(xCellFormula(E3))-FIND("!",xCellFormula(E3),1)),4)
..gets the whole cell reference. $C$2

So to get the Row Number only, you can do this...
=RIGHT(LEFT(RIGHT(xCellFormula(E3),LEN(xCellFormula(E3))-FIND("!",xCellFormula(E3),1)),4),2)
result = $2

OR
=RIGHT(LEFT(RIGHT(xCellFormula(E3),LEN(xCellFormula(E3))-FIND("!",xCellFormula(E3),1)),4),1)
result = 2

Thanks twohawks. Although all this reminds me again of the difference between "want" and "need"!

That's funny. I heard that ;^)