Calling any Excel gurus (Davitt?)

[cmduke]

I have this formula in a cell
=VLOOKUP('Category Summary'!$A30,'1'!$A$34:$B$45,2,FALSE)

Question focuses on the reference to '1' and $A$34:$B$45. I'm using this formula in a large number of cells; those two values change depending upon which column/row they're being used in. When I need those value to change, I'm currently having to change each cell separately.

What I'd like to do is to use a cell reference instead of '1' and $A$34:$B$45 - if I could point those values in the formula to a cell reference I can copy the formula more liberally and only make changes to the cells that contain the reference... BUT, I can't figure out the proper syntax... (combination of quotes and ampersands)

Somethign along the lines of
=VLOOKUP('Category Summary'!$A30,' & $B$1 & '! & $C$1 & ,2,FALSE)

Anyone help?

-Chris

[LongDuckDong]

Could you explain a little more what you're trying to do?? I admit Im not following, but I'd be glad to help once I understand what you're trying to do.

[cmduke]

I have a CBS Sportsline H2H league. Each week, I copy the weekly results to an Excel worksheet labelled by the week number (1, 2, 3 etc).....

I have another sheet where I'm trying to pull together the stats in a particular category.... So, I have the team names in column A, week 1 in column B, Week 2 in column C etc... The formula in Week 1 uses VLOOKUP to look at the Team Name ($A30 in the formula below) then looks at the worksheet for that week ('1' below), finds the range of that spreadsheet that contains that particular categories standings ($A$34:$B$45 below), and returns the category standings for the team for that week.

=VLOOKUP('Category Summary'!$A30,'1'!$A$34:$B$45,2,FALSE)

The problem is that I can't simply copy the formula into the Week 2 column and have it find the same information from the Week 2 worksheet; I have to manually change the '1' (the name of the weekly standings sheet) to '2'. And, to setup the same information for the categories, I also have to manually change the $A$34:$B$45 - which defines the area of the weekly worksheet where that particular category standings can be found.

What I'd like to do is to modify the formula above that uses a reference to the top cell in the column that the formula is in, instead of '1' - then when I copy the formula from the Week 1 column to the Week 2 column, it will find "2" at the top of the column rather than "1" and will be looking on the week 2 standings worksheet. The same goes for the category informatoin; instead of manually modifying each category formula, I can have tha tportion of the formula point to a cell where I define the location of the category standings....

Basically, I know that if I have two cells...
A1 has the text "Team"
A2 has the value "2"
I can create a third cell with the formula =A1 & "_" & A2
to return the value of "Team_2" - it takes cell A1 combines with the underscore and then places the text of A2 after that...

I'd like to do the same with VLOOKUP formula above... instead of having '1' (the weekly standings worksheet name), I'd like to tell it to look in C1 (for example)... Since that's a relative cell name, when I copy that formula in the cell to the right (the next week's standings) it will look in D1 for the weekly standings worksheet name - which in that case will be set in D1 as 2... That way, I can copy the formula all the way across the page through Week 23 of the season and only have to change the week of the season in one location, once.. rather than having to do it so many times...

I'm guessing the first part of the formula should look something like this...
=VLOOKUP('Category Summary'!$A30,'' & B1 & ''!

but Excel doesn't like that.. I can't figure out the syntax to do this...

Hope that helps...

-Chris

[LongDuckDong]

Sorry I made you type all that out. I just went through my bag of tricks quickly and I don't know how to copy across a workpaper reference.

[RobR]

Check out the ADDRESS and INDIRECT functions. Generally it works something like this:

=INDIRECT(ADDRESS(r,c))

SheetName is a named range of a cell holding the target sheet for the lookup.

r and c are the row and column numbers you want converted to a $A$1 style. For my CBS lookups, I use the ROW and COLUMN functions pointing to cells on my raw CBS data sheet to get r and c for each team.

So when I want to sum up a team's AB, it looks something like

=SUM(INDIRECT("SheetName"&"!"&ADDRESS(ROW(start),COLUMN(start))&":"&ADDRESS(ROW(end),COLUMN(end))))

EDIT: I think this is the formula you want:

=VLOOKUP('Category Summary'!$A30,INDIRECT(ADDRESS( $B$1 & '!' & $C$1 & ":" & $D$1)) ,2,FALSE)

Hope that helps. PM me w/ and email address for a sample sheet.

[GoldenEagle]

Duke, Have you tried this:

Make your "usual" copy from one column to the next, knowing that it will have improper column references.
Then go to the new range you just created (with the "bad" references) and keep it highlighted.

The go to the menu and click on Edit, then on Replace and in dialog box where it says Find What, and enter '1' and where it says Replace With, and enter '2' and then click on Replace. (I'd include the single quotes in the box as it makes it "more unique" because you don't want to replace every single 1 digit in the formula, only the part where the column reference appears).

If you don't like what it did, try an Edit Undo and you can try the find/replace one step at a time.

It's a "two-step" workaround but is sometimes faster than trying to think up the ideal formula syntax for copying under any and all circumstances...

[PD@HQ]

I do it Golden Eagle's way, but the INDIRECT function can be made to work. I seldom use it because in a large workbook it can RRRReallly slow down your sheet.

[cmduke]

Thanks for the help! Tried INDIRECT & ADDRESS without much luck... took a look at the absolute and relative cell references and adjusted those so I could use the Find/Replace All function more easily...

-Chris