This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

=+'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.

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.

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/d

..may be what you are looking for.

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.

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,FI

I guess I just answered how to enter a formula I can use to solve my immediate problem.....

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.

SAMPLE-ROSTER.xls

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)/

Cheers,

Brian.

SAMPLE-ROSTER-V2.xls

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?

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'.

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

Here's an example...

If I paste the following into F9 of Roster List-Book Collated sheet

=CONCATENATE("Birthday: "&TEXT(VLOOKUP(LEFT(OFFSET

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?

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.

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

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.

SAMPLE-ROSTER2.xls

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.

Please see attached.

="Birthday "&TEXT(OFFSET('Names for Roster'!$I$1,(COLUMN()-6)/

Brian.

Oops.

="Birthday "&TEXT(OFFSET('Names for Roster'!$I$1,(COLUMN()-6)/

Brian. SAMPLE-ROSTER2-V2.xls

I created a VB module and pasted the function into it.

I entered "=xCellFormula(E3)" in G3 this returns "

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

Susan

If the macro isn't in that file then you need to reference it, e.g.

"=Personal.xlsb!xCellFormu

Brian.

Thank you!

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.

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

=LEFT(RIGHT(xCellFormula(E

..gets the whole cell reference. $C$2

So to get the Row Number only, you can do this...

=RIGHT(LEFT(RIGHT(xCellFor

result = $2

OR

=RIGHT(LEFT(RIGHT(xCellFor

result = 2

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

If you just want to get the formula from a cell then, AFAIK, you'll need a macro...

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...

Open in new window

Brian.Open in new window