rsknow

asked on

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

Last Comment

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

ASKER

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?**

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

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.

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.

ASKER

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

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.

ASKER

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?

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.

Cheers,

Brian.

ASKER

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

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

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.

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?

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?

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.

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

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

ASKER

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.

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.

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.

Please see attached.

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

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

Oops.

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

Brian.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

Apologies, got my embedding mixed up.

ASKER

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

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

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

ASKER

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

Thank you!

Susan

ASKER

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.

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

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

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

That's funny. I heard that ;^)

Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K

Questions

--

Followers

--

Top Experts

Get a personalized solution from industry experts

TRUSTED BY

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.