factorialite posted:

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!

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.

gwar3k1 posted:

Not the most efficient way, but it will do what you've asked:

serewit posted:

It was failing, but I see what the problem is. This should work okay, thanks!

Ok, no, it's still failing. It's giving 'subscript out of range' after a few rows, and it's skipping the first two sheets. I thought that maybe it was checking the object name instead of what the sheet was named, but it doesn't seem to be the case.

gwar3k1 posted:

I've made the assumption that your department sheets are named exactly the same as the department code. Also, I have made the assumption that there is a sheet for every department on your master list sheet.

I bet its saying subscript out of range because a sheet name doesn't match / the intended sheet doesn't exist.

Can you provide an example of department code and sheet name, and say which line is causing the exception (highlighted yellow ehen you click debug).

serewit posted:

stuff

gwar3k1 posted:

It needs to be run from the main sheet. Here's some ammended code:

factorialite posted:

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.

factorialite posted:

false gives me the N/A error.

TheAngryDrunk posted:

Not sure I understand the first question, but the answer to the second one is 'no.' You can stick a combo box on the worksheet and it will fill in the first item on the data validation list that starts with the same letter, but you can't do it with a cell.

Sub Par posted:

Quick and dirty solution in VBA. Create a module, create this sub:

IceHawk posted:

For example:

code:

    Selection.Replace What:=ChrW(&H19B), Replacement:=ChrW(&H29F), LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True

This works well, until I need to use a sequence of characters as input and/or output. The problem is I have no idea how to get Excel to accept a sequence of two ChrW(&Hxxx).

What I need this code to do, it take something like "tɬ" as input, and output "ʟ̣"

gwar3k1 posted:

Does simple concatenation work: "ChrW(&Hxxx) & ChrW(&Hxxx)"

code:

    sFind = ChrW(&H19B) & ChrW(&H20B)
    sReplace = ChrW(&H29F) & "t"
    Selection.Replace What:=sFind, Replacement:=sReplace, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True

If it does, setup a sheet with two columns: Lang A, Lang B and enter your translations as needed, then use the following macro (or a modified version) to translate your sheets:

code:

Sub TranslateIt
  For i = 1 to Sheets.Count
    If Sheets(i).Name <> "TransLookUp" Then
      Sheets(i).Select
      iRow = 1
      Do While Sheets("TransLookup").Cells(iRow, 1) <> ""
        sFind = Sheets("TransLookup").Cells(iRow, 1)
        sReplace = Sheets("TransLookup").Cells(iRow, 2)
        Selection.Replace What:=sFind, Replacement:=sReplace, LookAt:=xlPart, _ 
             SearchOrder:=xlByRows, MatchCase:=True
        iRow = iRow + 1
      Loop      
    End if
  Next
End Sub

IceHawk posted:

Except I don't quite understand what your second macro does (I'm a Linguist and all I have is one year of computer science under my belt), all of my data is formatted so that column C contains the empty fields that I need to put the translation into, and column E contains the source.

gwar3k1 posted:

Sorry, the macro assumes you have a sheet named "TransLookup". On that sheet you have a column (A) which contains your original characters, and a second column (B) which contains the characters that col A should be changed to. I'm assuming your task is as simple as "change xx to yy" without any conditions (i.e. not accounting for "change xx to yy but if xx preceeds xxz then change it to ccc").

The macro loops all the worksheets in your book that aren't TransLookup (which it is using as reference) then it is making the replacement for every replacement you have instructed it to do (all rows on TransLookup).

Does that make sense?

Sub Par posted:

Did you refresh the pivot after clearing the data out of the workbook?

gwar3k1 posted:

Here's a sub that'll do what you want (identify duplicate positive dates):

code:

Sub DupCheck()
  bDuplicate = False
  sDates = ""
  For Each xCell In Range("B:B")
    If xCell.Value = "yes" Then
      If InStr(1, sDates, Cells(xCell.Row, 1)) > 0 Then
        bDuplicate = True
      Else
        sDates = sDates & Cells(xCell.Row, 1) & ","
      End If
    End If
  Next

  'bDuplicate is then the variable you want to check
End Sub

If you're using Excel >= 2007, there is a duplicate removal feature, if that's what this is hoping to do?

IT Guy posted:

Actually, I made a mistake, I need to check array2 to see if every value is the same or unique.

Will that still work?

After Googling around, it appears the FREQUENCY() function may do what I need, I just don't know how to use it properly.

gwar3k1 posted:

No, the sub I posted will store a list of unique values and return true if there are any duplicates. You can validate by doing:

Zhentar posted:

I don't think inColl does what you think it does.