?
Solved

Get row number from adjacent formula row reference

Posted on 2011-10-05
29
Medium Priority
?
243 Views
Last Modified: 2012-05-12
=+'Names for Roster'!$C$3

I am printing a membership directory for my club.  Now that it is all laid out for 200 members my board wants to add a birthday that is saved on the 'Names for Roster' worksheet in column H in a format like 8/16 for August 16.  I thought I would use:  =CONCATENATE("Birthday: "& TEXT('Names for Roster'!H3,"mm/dd")).

What is the easiest way to enter this changing the row reference in the formula for all 200 members?  I thought I could get the row number from the exiting formula shown at the beginning of my question.  This formula is up one row and to the left one column from where the birthday cell will be.

0
Comment
Question by:rsknow
  • 15
  • 8
  • 6
29 Comments
 
LVL 8

Expert Comment

by:twohawks
ID: 36922133
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.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 36922134
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.
0
 
LVL 1

Author Comment

by:rsknow
ID: 36922214
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?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 26

Expert Comment

by:redmondb
ID: 36922229
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.
0
 
LVL 1

Author Comment

by:rsknow
ID: 36922352
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
0
 
LVL 26

Expert Comment

by:redmondb
ID: 36922361
Hence my request for a sample. Thanks!
0
 
LVL 26

Expert Comment

by:redmondb
ID: 36922406
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
0
 
LVL 1

Author Comment

by:rsknow
ID: 36922452
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?
0
 
LVL 26

Expert Comment

by:redmondb
ID: 36922463
Em... nothing? The formula not only works for those columns, I actually included it for them.
0
 
LVL 26

Expert Comment

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

Cheers,
Brian.
0
 
LVL 1

Author Comment

by:rsknow
ID: 36925489
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'.  
0
 
LVL 26

Expert Comment

by:redmondb
ID: 36926044
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
0
 
LVL 8

Expert Comment

by:twohawks
ID: 36926255
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?
0
 
LVL 8

Expert Comment

by:twohawks
ID: 36926315
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.
0
 
LVL 8

Expert Comment

by:twohawks
ID: 36926336
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).
0
 
LVL 1

Author Comment

by:rsknow
ID: 36932232
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
0
 
LVL 26

Expert Comment

by:redmondb
ID: 36932333
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.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 36932369
rsknow,

Please see attached.

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

Brian.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 36932372
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
0
 
LVL 26

Accepted Solution

by:
redmondb earned 1000 total points
ID: 36932486
rsknow,

If you just want to get the formula from a cell then, AFAIK, you'll need a macro...
Function xCellFormula(c As Range)

xCellFormula = c.Formula

End Function

Open in new window


So, if I enter "=xCellFormula(E3)" in G3 this returns "=CONCATENATE('Names for Roster'!$C$2&" " & 'Names for Roster'!$B$2 &". . . . . . . . . . . . . . . . . .")"

Or, if you'd really prefer the row no. from E3 you could use the following formula...
Function xCellFormula(c As Range)

xCellFormula = c.Formula

End Function

Open in new window

Brian.
=MID(xCellFormula(E3),FIND("$",SUBSTITUTE(xCellFormula(E3),"$"," ",1),1)+1,FIND("&",xCellFormula(E3),1)-FIND("$",SUBSTITUTE(xCellFormula(E3),"$"," ",1),1)-1)*1

Open in new window

0
 
LVL 26

Expert Comment

by:redmondb
ID: 36932507
Apologies, got my embedding mixed up.
0
 
LVL 1

Author Comment

by:rsknow
ID: 36932987
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
0
 
LVL 26

Assisted Solution

by:redmondb
redmondb earned 1000 total points
ID: 36933051
Susan,

If the macro isn't in that file then you need to reference it, e.g.
"=Personal.xlsb!xCellFormula(E3)"

Brian.
0
 
LVL 1

Author Comment

by:rsknow
ID: 36933253
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
0
 
LVL 1

Author Closing Comment

by:rsknow
ID: 36933286
Thanks for sticking through and understanding my desired result.  I will use this function many times.  Great!
0
 
LVL 26

Expert Comment

by:redmondb
ID: 36933292
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.
0
 
LVL 8

Expert Comment

by:twohawks
ID: 36933383
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
0
 
LVL 26

Expert Comment

by:redmondb
ID: 36933657
Thanks twohawks. Although all this reminds me again of the difference between "want" and "need"!
0
 
LVL 8

Expert Comment

by:twohawks
ID: 36934799
That's funny. I heard that ;^)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question