using cell content to reference a worksheet tab name in second workbook

**Conor** · 08-28-2007, 02:02 PM

Hello all,

I have two separate workbooks with an equal number of worksheets. In workbook 1, each sheet has a person's name (John Doe, Susan Smith, etc.) in cell c6. In workbook 2, each sheet is named after one of those people. That is, workbook 2 has worksheet tabs John Doe, Susan Smith, etc. The worksheets in workbook 1 do not have cooresponding names, but they could have if it made the solution simpler.

I want to perform the same set of calculations on each sheet in workbook 1, by including in the formulas the related data from workbook 2, based on the match between the name in cell c6 of each sheet in workbook 1 and the tab name in workbook 2.

I would prefer a link formula, but would be willing to use a macro. I do not think I need anyone to write the macro for me. Whether link or macro, I only need to know how, if possible, the reference is coded.

I don't know if what follows will help or confuse, but essentially, I want to incorporate a set of calculations into each sheet of workbook 1 of the following nature:

content of wb1 sheet 1 cell m20: = value of wb1 sheet 1 cell m7 * value of wb2 cell a20 on sheetname with value of wb1 sheet 1 cell c6.
content of wb1 sheet 1 cell m21: = value of wb1 sheet 1 cell n7 * value of wb2 cell a21 on sheetname with value of wb1 sheet 1 cell c6.
content of wb1 sheet 1 cell m22: = value of wb1 sheet 1 cell o7 * value of wb2 cell a22 on sheetname with value of wb1 sheet 1 cell c6.

content of wb1 sheet 2 cell m20: = value of wb1 sheet 2 cell m7 * value of wb2 cell a20 on sheetname with value of wb1 sheet 2 cell c6.
content of wb1 sheet 2 cell m21: = value of wb1 sheet 2 cell n7 * value of wb2 cell a21 on sheetname with value of wb1 sheet 2 cell c6.
content of wb1 sheet 2 cell m22: = value of wb1 sheet cell o7 * value of wb2 cell a22 on sheetname with value of wb1 sheet 2 cell c6.

Thanks in advance to anyone who can help.

Conor

**Paul** · 08-28-2007, 02:30 PM

In cell M20 on WB1/Sheet1 you can probably use the following formula filled downward:

Please Login or Register  to view this content.

In M20, this will look at cell M7 (offset=0,0) and multiply that by the value in Book2 + value in C6 + cell A+current row (since it looked like M20 would multiply by A20's value, M21 by A21's, etc.).
In M21, it would look at cell N7 (offset=0,1) and multiply by value in Book2 + value in C6 + cell A+current row (21).
etc.

Give it a try and let us know if you have questions.

**boylejob** · 08-28-2007, 02:41 PM

Connor,

Here are the items I believe you would need.

Please Login or Register  to view this content.

You can use your Set statements within a loop grabbing a new tab name with each pass. I would use the Set xxxx = Nothing before assigning a new tab to be sure your variable is cleared. If you are changing workbooks, you could also use a variable for that also.

I don't feel like I have given you very much, but it felt like you wanted to do the coding. Hopefully, this will get you started. If you need some additional help after you get started, let me know.

**Conor** · 08-28-2007, 03:43 PM

Paul,

You have the approach I want to use, but I have inadvertantly made the question too complex. It was only by coincidence that the row references I gave for the two workbooks are the same. That is M20 * A20; M21 * A21, etc.

I really want to multiple wb1 sheet 1 cell m7 * wb2 sheet(name in c6) cell whatever - let's say q14. After I get the first one right, I want to drag or copy the formula down the column so that m8 refrences q15, etc.

So, I can see the part I need (the workbook and $c$6 part) but I am confused by the offset (I don't think I need that part) and the indirect, since I think I just want to say Q$14 rather than trying to sync up the row references.

And Boyle, thanks to you too for your input.

Would you mind trying again with a simplified version? Thanks so much in advance. I know you have the answer I need, but I just cannot edit it correctly to get the right result.

**Paul** · 08-28-2007, 04:02 PM

So you want M7*Q14, M8*Q15, M9*Q16, etc..?

You need to keep the INDIRECT function, since that's how you combine the workbook name+sheetname via cell reference+cell reference on other sheet.

The OFFSET function is used because you're filling a formula downward, but multiplying M7, then N7, then O7 -- moving across. If you're going to move downward - not across - then OFFSET isn't needed.

Obviously, you couldn't go past M19*Q26, because M20 would be results from M7*Q14.. make sense?

In this case, in M20 use this formula and fill down to M31:

Please Login or Register  to view this content.

**Conor** · 08-29-2007, 09:32 AM

Paul,

Dude! That is awesome! I have done a fair amount of Excel but that is the most complex syntax I have ever seen in it. I need to study this for a while to digest it. But it is EXACTLY what I needed thanks tons.

Conor

using cell content to reference a worksheet tab name in second workbook

LinkBack

Thread Tools

Rate This Thread

Display

using cell content to reference a worksheet tab name in second workbook

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions