Posts by junho lee

Re: Sort Dates Linked To Formulas Also Returning Text

fixtures are like this?a,b,c,d ; teams
b - - a
c - - a
d - - a
a - - b
c - - b
d - - b
a - - c
b - - c
d - - c
a - - d
b - - d
c - - d
regards,

Re: Sort Dates Linked To Formulas Also Returning Text

i didn't ponder this problem.
maybe helped by code. or function.
sorry

Re: Sort Dates Linked To Formulas Also Returning Text

i think "ref error" is the problem that dates and players are messed up.
this is a sorting error or inputting changed dates error.i think
regards,

Re: Sort Dates Linked To Formulas Also Returning Text

if date in fixtures is changed ,must also manually change date in "copied area".
and when you are sorting dates must select range "a1:e12".
regards,

Re: Sort Dates Linked To Formulas Also Returning Text

sorry,in function :change sheet1!g1 to sheet1!g8
regards,

Re: Sort Dates Linked To Formulas Also Returning Text

it would be better copy "date range" (or original fixture)to other place ex. "column j" and use vlookup function to the grid then will not be changed whahever change date in the fixtures.if date is changed,also change copy "date range" .
for example
=vlookup(fixture!date in column j,fixture area,3 or 4("score area"),false)
regards,

Re: Changing Calendar Start And Stop Dates

ii make date list and fiscal date and use vlookup function,
regards,

Re: Sort Dates Linked To Formulas Also Returning Text

just copy date part to other area and use lookup function will also good.
regards,

Re: Changing Calendar Start And Stop Dates

http://www.exceltip.com/st/PivotTable_report_-_Grouping_the_Date_Field_by_Quarters_in_a_Fiscal_Reporting_Year/749.html
eg,f2 has date
="Q"&(MOD(CEILING(22+MONTH(F2)-9-1,3)/3,4)+1) : quater
=IF(MONTH(F2)<=9,YEAR(F2),YEAR(F2)+1) : year
regards,

Re: Missing Punches

hi,i hope this code help your work.

Sub subst()
l1 = Cells(2, 1).End(xlDown).Row
l2 = Cells(2, 5).End(xlDown).Row
k = 3
For i = 3 To l2
For j = 3 To l1
If Cells(j, 1) = Cells(i, 5) And Cells(j, 2) = Cells(i, 6) Then
Cells(j, 4) = 3
End If
Next
Next
For i = 3 To l1
If Cells(i, 4) = "" Then
Range(Cells(i, 1), Cells(i, 2)).Copy Cells(k, 10)
k = k + 1
End If
Next
Range(Cells(3, 4), Cells(l1, 4)).ClearContents
End Sub

thanks,

Re: Sort Dates Linked To Formulas Also Returning Text

Sub iiii()
k = 1
For i = 1 To 4
For j = 1 To 4
If i = j Then
ElseIf Not i = j Then
Cells(j + 1, i + 7) = Cells(k, 1)
k = k + 1
End If
Next
Next
End Sub

activate,sheet"fixtures",and run
I think variables are dates.hence need the date_grid contains dates. and make grid using lookup function. then will be o.k after sorting date.
regards,

Re: Sort Dates Linked To Formulas Also Returning Text

when sorting dates,select "not header row"
regards,

Re: Multiple Logical Test

=HLOOKUP(D10,G5:M6,2,FALSE)*B10
or
=IF(D10=G5,SUM(B10*G6,0),(IF(D10=H5,SUM(B10*H6,0),(IF(D10=I5,SUM(B10*I6,),IF(D10=J5,SUM(B10*J6,0),IF(D10=K5,SUM(B10*K6,0),IF(D10=L5,SUM(B10*L6,0),IF(D10=M5,SUM(B10*M6,0),0)))))))))
regards,

Re: Date Sorting

as i know,when sorting dates make assure that select selection range expansion.
regards,

Re: Organizing Fractions

Right click on the cell>> click format cell>> on the pop up menu go to number >> choose from categor>> click on fraction>> then choose form you want from right side menu. i know it will help you ~from yahoo

Re: Date Sorting

i just tried,copy fixtures from priemier site,managed this data
range select>right click>cell format(text line:no check,merge:no check):repeat
sorry not helped,

Re: Date Sorting

range>cell format>text line,merge(no checking):repeat
date:tools>data>split text and sort date
regards,

Re: Coloring X,y,z Values

owing to http://www.ozgrid.com/Excel/excel-charting-7.htm
xy_scatter chart,solve.
activate "sheet1",and run
regards,

Re: Vba Print Conditional

if you have code ranges in f1,g1 cells(AAA),then i think, modifying like this in "print all"

Set wsAtt = ThisWorkbook.Worksheets("AAA")
    wsAtt.Select
    Dim minr As Long, maxr As Long
l = Cells(4, 2).End(xlDown).Row
For i = 4 To l Step 1
    If Cells(i, 2) = Cells(1, 6) Then
    minr = i
    Exit For
    End If
    Next
    For j = l To 4 Step -1
    If Cells(j, 2) = Cells(1, 7) Then
    maxr = j
   Exit For
   End If
    Next
    Set rng = wsAtt.Range(Cells(minr, 3), Cells(maxr, 3))
    Set wsPay = ThisWorkbook.Worksheets("BBB")

REGARDS,

Re: Coloring X,y,z Values

a=o,b=0,c=0
if there display this with three dimensions?
regards,