Small Excel Question Not Worth Its Own Thread

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

factorialite: Mar 3, 2008; by Lowtax

I have a crazy question.

I've created an excel file with the each nfl team's schedule on it, and on each page I also have a ranking system. In this file, I've created a tab called Alpha Tab that has a column for each team and a column for ratings (so you can put your own rating system there). I want to use a function to lookup the name of whatever tab I'm in and pull the value to the right of that name in the tab "alpha tab."

For example, I am in the Miami tab. I want to write a function that says "I am in miami, let me go to the alpha tab and return the value just to the right of miami."

Is this possible? The spreadsheet makes heavy use of calling value from other tabs, so I know that can be done. However, I did it using brute force, and it'd be nice to learn how to do this.

Thanks in advance!

# ¿ Oct 9, 2010 00:14

Adbot: ADBOT LOVES YOU

# ¿ May 5, 2024 22:05

factorialite: Mar 3, 2008; by Lowtax

TheAngryDrunk posted:

You need a custom function to spit out the worksheet name on a worksheet.

Here's how to do it: http://www.ozgrid.com/VBA/return-sheet-name.htm

Once you have that, you can use that in your lookup functions.

This is a lot of help, but I'm not there yet.

If I wanted to grab detroit's rating while on the chicago tab, I would enter

='Detroit'!a13.

Now, I need to use the LOOKUP function to grab the value to the right of whatever the tab name that sheetname() gives me. I've got the sheetname() function but I don't know which lookup to use. I thought it'd be VLOOKUP but I can't specify across sheets with that, can I? I'm imagining that INDIRECT will come into play, but I'm not sure how to combine the two successfully. Thanks again for both your expedient response and any other help you might give!

# ¿ Oct 9, 2010 00:57

factorialite: Mar 3, 2008; by Lowtax

Thanks for the help guys, I'm very nearly there!

My next problem is a really weird one.

I've got the sheetname(a1) function defined, that's no problem.
I think I've got the vlookup function correct (I use =vlookup(sheetname($a$1,true),'Master'!$a$1:$b$33,2). This actually does pull the data I want it to... most of the time.

If I have this data in master:

pre:

Chicago	        1
Detroit	        2
Minnesota	3
Green Bay	4
NY Giants	5

The vlookup returns the following results:

pre:

Chicago         1
Detroit         2
Minnesota       4
Green Bay       4
NY Giants       5

If I extend the data a little further:

pre:

Chicago	        1
Detroit	        2
Minnesota	3
Green Bay	4
NY Giants	5
Dallas          6

It becomes:

pre:

Chicago         1
Detroit         2
Minnesota       6
Green Bay       6
NY Giants       6
Dallas          1

I don't really have any idea why it does this. Any ideas?

# ¿ Oct 14, 2010 05:58

factorialite: Mar 3, 2008; by Lowtax

esquilax posted:

Try changing your formula to:

=vlookup(sheetname($a$1,true),'Master'!$a$1:$b$33,2, false)

When you don't specify a last argument for a vlookup, it defaults to "True". This makes it look for an 'approximate' value, which gives you weird results if your list isn't in alphabetical order. Setting it as 'False' makes it look for an exact match, and you don't have to reorder anything.

false gives me the N/A error.

If I do sheetname(a1,false), it still works, so it likely isn't the sheetname function that screws me up. If I have to alphabetize, it's not optimal but it's doable for sure.

# ¿ Oct 15, 2010 04:30

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread