Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup part of a cell's contents and return value of entire cell
I have a schedule of 16 teams over 17 weeks. Right now the weeks are in rows
and matches are in columns, i.e. A B C D E 1 01/31 01 - 02 03 - 04 05 - 06 07 - 08 2 02/07 04 - 01 06 - 03 08 - 08 10 - 07 3 02/14 05 - 14 09 - 02 12 - 04 16 - 13 Therefore, cell B2 states on Feb. 7th, Team 4 plays against Team 1. Since one cell contains two numbers, is there a formula that will for one of the two numbers and return the entire cell to another worksheet? So I can pull all of one Team's schedule onto a separate worksheet and into one column, putting formula in column B? A B 1 01/31 03 - 04 2 02/7 06 - 03 3 02/14 03 - 08 4 02/21 10 - 03 Hope this makes sense, and Thanks a Bunch in advance T |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup part of a cell's contents and return value of entire cell
Is a macro ok?
This does assume that each team can only play one game per date: Option Explicit Sub testme() Dim CurWks As Worksheet Dim RptWks As Worksheet Dim iRow As Long Dim oRow As Long Dim FirstRow As Long Dim LastRow As Long Dim TotalTeams As Long Dim tCtr As Long Dim SchedRng As Range Dim FoundCell As Range Set CurWks = Worksheets("sheet1") Set RptWks = Worksheets.Add oRow = 0 TotalTeams = 16 With CurWks FirstRow = 1 'no headers??? LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For tCtr = 1 To TotalTeams 'add some headers oRow = oRow + 1 With RptWks.Cells(oRow, "A") .Value = "Team: " & Format(tCtr, "00") .Font.Bold = True End With If oRow 1 Then RptWks.Cells(oRow, "A").PageBreak = xlPageBreakManual End If For iRow = FirstRow To LastRow Set SchedRng = .Range(.Cells(iRow, "B"), _ .Cells(iRow, .Columns.Count).End(xlToLeft)) With SchedRng Set FoundCell = .Cells.Find(what:=Format(tCtr, "00"), _ after:=.Cells(.Cells.Count), _ LookIn:=xlValues, lookat:=xlPart, _ searchorder:=xlByColumns, _ searchdirection:=xlNext, _ MatchCase:=False) End With If FoundCell Is Nothing Then 'team isn't on this row, so do nothing Else oRow = oRow + 1 RptWks.Cells(oRow, "A").NumberFormat _ = .Cells(iRow, "A").NumberFormat RptWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value RptWks.Cells(oRow, "B").NumberFormat = "@" 'text RptWks.Cells(oRow, "B").Value = FoundCell.Value End If Next iRow Next tCtr End With End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Terri wrote: I have a schedule of 16 teams over 17 weeks. Right now the weeks are in rows and matches are in columns, i.e. A B C D E 1 01/31 01 - 02 03 - 04 05 - 06 07 - 08 2 02/07 04 - 01 06 - 03 08 - 08 10 - 07 3 02/14 05 - 14 09 - 02 12 - 04 16 - 13 Therefore, cell B2 states on Feb. 7th, Team 4 plays against Team 1. Since one cell contains two numbers, is there a formula that will for one of the two numbers and return the entire cell to another worksheet? So I can pull all of one Team's schedule onto a separate worksheet and into one column, putting formula in column B? A B 1 01/31 03 - 04 2 02/7 06 - 03 3 02/14 03 - 08 4 02/21 10 - 03 Hope this makes sense, and Thanks a Bunch in advance T -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup part of a cell's contents and return value of entire cell
See this screencap:
http://img524.imageshack.us/img524/9229/lookup1jk1.jpg The formula used is an array formula. Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Terri" wrote in message ... I have a schedule of 16 teams over 17 weeks. Right now the weeks are in rows and matches are in columns, i.e. A B C D E 1 01/31 01 - 02 03 - 04 05 - 06 07 - 08 2 02/07 04 - 01 06 - 03 08 - 08 10 - 07 3 02/14 05 - 14 09 - 02 12 - 04 16 - 13 Therefore, cell B2 states on Feb. 7th, Team 4 plays against Team 1. Since one cell contains two numbers, is there a formula that will for one of the two numbers and return the entire cell to another worksheet? So I can pull all of one Team's schedule onto a separate worksheet and into one column, putting formula in column B? A B 1 01/31 03 - 04 2 02/7 06 - 03 3 02/14 03 - 08 4 02/21 10 - 03 Hope this makes sense, and Thanks a Bunch in advance T |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change another cell's contents from current cell??? | Excel Discussion (Misc queries) | |||
copying part of a cell's contents (formatted as dd:hh:mm) | Excel Worksheet Functions | |||
can I use a cell's contents as part of a custom header? | Excel Discussion (Misc queries) | |||
Return cell contents based on conditional lookup | Excel Worksheet Functions | |||
How do I return entire rows in a lookup of one sheet to another? | Excel Worksheet Functions |