I have looked everywhere for help. This should be a simple formula,
but never works for me. I have a main worksheet that I entered dated
data into. I want another spreadsheet to pull data specific to my
criteria. Basically I'm trying to pull If Cell A:1 is greater than or
equal to cell: B:2 and cell A:1 is less than or equal to Cell B:3 then
true.

It's dates that I am working with. If this date is = 8/4/06 and is <=
8/11/06 then true.

I can't get it to work... I would love love love any help someone can
offer.

Thanks in advance :)

Rondia

IF(AND(A1=B2, A1<=B3), "True", "False")


--
Bearacade


------------------------------------------------------------------------
Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
View this thread: http://www.excelforum.com/showthread...hreadid=572736

=if(or(a1=b2,a1<=b3),true,false)

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Rondia" escreveu:

I have looked everywhere for help. This should be a simple formula,
but never works for me. I have a main worksheet that I entered dated
data into. I want another spreadsheet to pull data specific to my
criteria. Basically I'm trying to pull If Cell A:1 is greater than or
equal to cell: B:2 and cell A:1 is less than or equal to Cell B:3 then
true.

It's dates that I am working with. If this date is = 8/4/06 and is <=
8/11/06 then true.

I can't get it to work... I would love love love any help someone can
offer.

Thanks in advance :)

Rondia

Cell A:1 is greater than or
equal to cell: B:2 and cell A:1 is less than or equal to Cell B:3 then
true.

Its not entirely clear what you want but
and(a1=b2,a1<=b3) is what you require but you may need to put the
other sheet names in the formula

or maybe if(and(a1=b2,a1<=b3),a1,"")

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=572736

I really appreciate everyone's help - still not working :(

Here's more detail:

Sheet 1: Cell A1:A2500 (date column ranging from 8/1/06-8/31/06)
Sheet 2: Cell D1 (a specific date 8/4/06)
Sheet 2: Cell D2 (a specific date 8/11/06)
Sheet 2: Cell A8 (this is the first of cells that I want a formula
that will give me the data from Sheet 1 Cell A1:A2500 if the date in
that cell is = 8/4/06 (Sheet 2: Cell D1) AND <=8/11/06 (Sheet 2: Cell
D2)

I then have 5 additional columns I want brought over if the data in
Column A matches the criteria requested.

Thanks :)
Rondia

I think I know what you need.

How comfortable are you with Macros?

I can set one up for you that will filter out what you want, copy those
rows and bring over to the second sheet.

If my understanding is correct. In Sheet1, you have 2500 rows of data,
and in A1 is that date of these data. and you want it to be filtered out
so that only rows that are within certain dates gets copied over to
sheet2.

This can be easily accomplished with a Macro.

If that is indeed what you need, go ahead and tell me and I will whip
one up for you. I do need to know what 5 column other columns you need
and if those are the only 5 columns with data.


--
Bearacade


------------------------------------------------------------------------
Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
View this thread: http://www.excelforum.com/showthread...hreadid=572736

Oh thank you Bearacade!!

very comfortable with macros - didn't even think of that... You are
correct. I have a main spreadsheet of data - I need to pull the rows
of data over for lines that fall in the specific requested dates. The
dates will change each week. I do billing thru each Friday....

What do you have in mind?

Thank you :):)
Rondia

Oops forgot to give you the info. you needed....

I am currently using Columns A - H. Each week the dates will change
that I need to pull out.
I do the billing on Salt Water Disposals. I enter data from tickets
then pull the week I need into an invoice separate & then export it to
a pdf for a more professional looking invoice. I currently have one
well but really need to tighten up my formulas & process b/c I will
have 8-10 wells by end of year. It currently takes 8-10 hours a week
on this well the way I am currently doing it... I then pull the data
by truck & date into a supplemental sheet just as a courtesy to the
trucking company for their billing benefit. Then I pull the data into
another sheet for Railroad Commission reporting (by lease). So I pull
data by date for invoicing, by truck & date for supplemental reporting
& by lease for RRC reporting...

I know you didn't want all of that. If you can help me with the first
macro - I may be able to figure out working the other 2 I need....

Thank you again....
Rondia

This is what I have worked out.. you will have to tweak it a bit.

The basis is this, the dates are in Sheet1!A and Sheet1!B:F has the
rest of the data (and assuming that there are no other data), it
actually doesn't matter cause this will pull the entire row out. If
you need to pull specific columns, tell me.

It will take the date from Sheet2!D1 and Sheet2!D2 to apply the filter

and it will starts Pasting in Sheet2!A8

Sub Macro2()

Sheets("Sheet1").Select
Cells.Select
Selection.AutoFilter Field:=1, Criteria1:="=" &
Range("Sheet2!D1").Value, Operator:=xlAnd, Criteria2:="<=" &
Range("Sheet2!D2").Value
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Sheet2").Select
Range("A7").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Selection.AutoFilter
Range("A1").Select

End Sub


--
Bearacade


------------------------------------------------------------------------
Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
View this thread: http://www.excelforum.com/showthread...hreadid=572736

Thank you so much!! I guess I'm not as comfortable as I thought with
macro's. I edited your macro to fit my sheets names & cells correctly
- not working.... I feel terrible you're taking the time to help me &
I didn't give you what you needed from the start.

Better example:
I have a sheet 'August Tickets Log' that I enter all tickets in for
that month.
I have a second sheet 'Matthew's - August' that pulls over all Matthews
tickets.
I have another sheet "Marsh - August' that pulls over all Marsh
tickets, and so forth.
And then, I have a sheet 'Matthew's 08-2006-Invoice' that I use to pull
that weeks' tickets for Matthew's from the 'Matthew's - August' sheet.
And the same for all the other trucking entities.

My goal was to have a sheet in the front with button commands: 'Enter
tickets' to take me to the main sheet; 'Matthew's Invoice' pulling that
weeks tickets for Matthew's... etc. [week 1, week 2, etc.]

I'm probably making this more complicated than it needs to be, but I
want to be prepared as I take on more leases. I've looked up functions
and researched books - I can't seem to make anything work with the
dates.

I don't blame you if you don't reply again, but if you do - thank
you...

Thanks,
Rondia

How exactly are tickets entered into August Tickets Log.

Better question is, how exactly do you distincts Matthew's ticket to
Marsh's tickets?

Is there a column that has their names in it?

How many columns total are there in the inital log? I am guessing 9...
=)


--
Bearacade


------------------------------------------------------------------------
Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
View this thread: http://www.excelforum.com/showthread...hreadid=572736

There is a sheet 'August Ticket Log' - A:3 Header is 'Truck Co.', B:3
Header is 'Operator', C:3 = 'Lease, D:3 = 'Date', E:3 = 'Ticket #', F:3
= 'Truck #', G:3 = 'BBLS', H:3 = 'Type', J:3 = 'Amount' (I:3 is
skipped).

I begin entering data on A:4.

I want to insert a sheet that has control buttons: 1 - Enter tickets,
2 - Invoice Matthews Week 1, Week 2, Week 3, etc. and then one for
each trucking co., then a button for supplemental by truck #, and last
one for supplemental by lease.

Unless you might have a better way?

You are so nice for taking so much time with me.... Really I do
appreciate it. I have looked for an advanced excel book, but haven't
found one I like yet. Do you recommend any? I can pick one up this
w/e.

Thanks,
Rondia

I am heading to a meeting right now.. I will work on this a bit more
when I come back.. but I do have some ideas I will share with you.


--
Bearacade


------------------------------------------------------------------------
Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
View this thread: http://www.excelforum.com/showthread...hreadid=572736

Thank you....

And Matthew/Marsh is the operator? right?


--
Bearacade


------------------------------------------------------------------------
Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
View this thread: http://www.excelforum.com/showthread...hreadid=572736

No Matthew, Marsh, etc are the trucking companies. There are several
trucking companies, several operators, and hundreds of leases...


Bearacade wrote:
And Matthew/Marsh is the operator? right?


--
Bearacade


------------------------------------------------------------------------
Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
View this thread: http://www.excelforum.com/showthread...hreadid=572736

Ok, that's what I wanted to know, we are sorting by Truck Co.

I am working on a Macro where it will automatically take your top
sheet,

Seperate out the Truck Co, and then weeks in the month.

Running into a little snag with the weeks, give me a little time =)


--
Bearacade


------------------------------------------------------------------------
Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
View this thread: http://www.excelforum.com/showthread...hreadid=572736

You are very sweet. Thank you.


Bearacade wrote:
Ok, that's what I wanted to know, we are sorting by Truck Co.

I am working on a Macro where it will automatically take your top
sheet,

Seperate out the Truck Co, and then weeks in the month.

Running into a little snag with the weeks, give me a little time =)


--
Bearacade


------------------------------------------------------------------------
Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
View this thread: http://www.excelforum.com/showthread...hreadid=572736

Ok.. here is what I have so far. I have included a zipped excel file
for you to look at. The code could probably be written tighter.. I am
not the best coder around, just taking a stab at it.

Basically what I have done is this: I included two macros, one is
called SortByCompany and the other is call SortByWeek

SortByCompany will take your master sheet as break down and filter out
as many companies as you have, so if you have 15 companies, it will
create 15 new sheets with their names on it and their data.

SortByWeek can then be used in either those sheets or in the
mastersheet. It basically takes the date and break it down into weeks,
each week begins with Saturday and ends with Friday.

The assumtion is that all the entries will be of the same month.
Strange things will happen if they are not..

Here are the codes or you can download the sheet. You would have to
come to excelforum to download it:
http://www.excelforum.com/showthread...hreadid=572736

Good luck, We can continue to tweak it as you need, I hope this helps
out.


Sub FilterByCompany()

Dim MyUniqueList As Variant, i As Long, sName As String

'stores the ActiveWorkbook.Name so we can return to it
sName = ActiveSheet.Name

'Sort the Data for Filter
Rows("4:2500").Select
Selection.Sort Key1:=Range("A4"), Order1:=xlAscending,
Key2:=Range("D4"), Order2:=xlAscending, Header:=xlGuess,
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal

MyUniqueList = UniqueItemList(Range("A4:A2500"), True)

For i = 1 To UBound(MyUniqueList)

'Sort out the Data
Rows("3:2500").Select
Selection.AutoFilter Field:=1, Criteria1:="=" &
MyUniqueList(i)
Cells.Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets.Add 'Create New Sheet
ActiveSheet.Paste 'Paste the Data

'Get month of Data, this is the fastest way I can think of
without going thru a lot of codes...
Range("A1").FormulaR1C1 = "=(TEXT(MONTH(R[3]C[3])*29,
""MMMM""))"

'Rename the Sheet
ActiveSheet.Name = MyUniqueList(i) & " - " & Range("A1")
Range("A1") = Null

'Switch Back to orginal Sheet and deactivate autofilter
Sheets(sName).Activate
Selection.AutoFilter
Range("A1").Select

Next i



End Sub

Sub FilterByWeek()

Dim i As Long, sName As String, mStart As Date, mEnd As Date, mTemp As
Date, mTemp1 As Date

'Set up the Weeks
Range("A1").FormulaR1C1 =
"=(DATE(YEAR(R[3]C[3]),MONTH(R[3]C[3])+1,0))"
mEnd = Range("A1").Value
Range("A1").FormulaR1C1 =
"=(DATE(YEAR(R[3]C[3]),MONTH(R[3]C[3]),1))"
mStart = Range("A1").Value
Range("A1").FormulaR1C1 = Null

mTemp = NthDayOfMonth("Fri", CDate(mStart), 1)
i = 1

Do While mTemp <= mEnd

If i = 1 Then
mTemp1 = mStart
Else
mTemp1 = mTemp - 6
End If

sName1 = ActiveSheet.Name
Rows("3:25").Select
Selection.AutoFilter Field:=4, Criteria1:="=" & mTemp1,
Operator:=xlAnd, Criteria2:="<=" & mTemp
Cells.Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets.Add
ActiveSheet.Paste
ActiveSheet.Name = sName1 & " - Week " & i
Sheets(sName1).Select
Selection.AutoFilter

i = i + 1
mTemp = mTemp + 7

If mTemp mEnd Then

sName1 = ActiveSheet.Name
Rows("3:25").Select
Selection.AutoFilter Field:=4, Criteria1:="=" & mTemp - 6,
Operator:=xlAnd, Criteria2:="<=" & mEnd
Cells.Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets.Add
ActiveSheet.Paste
ActiveSheet.Name = sName1 & " - Week " & i
Sheets(sName1).Select
Selection.AutoFilter

End If

Loop

End Sub


Private Function UniqueItemList(InputRange As Range, HorizontalList As
Boolean) As Variant
Dim cl As Range, cUnique As New Collection, i As Long, uList() As
Variant
Application.Volatile
On Error Resume Next
For Each cl In InputRange
If cl.Formula < "" Then
cUnique.Add cl.Value, CStr(cl.Value)
End If
Next cl
UniqueItemList = ""
If cUnique.Count 0 Then
ReDim uList(1 To cUnique.Count)
For i = 1 To cUnique.Count
uList(i) = cUnique(i)
Next i
UniqueItemList = uList
If Not HorizontalList Then
UniqueItemList = _

Application.WorksheetFunction.Transpose(UniqueItem List)
End If
End If
On Error GoTo 0
End Function

Private Function NthDayOfMonth(Which_Day As String, Which_Date As
String, Occurence As Byte) As Date
Dim i As Integer
Dim iDay As Integer
Dim iDaysInMonth As Integer
Dim FullDateNew As Date
Dim lCount As Long

Which_Date = CDate(Which_Date)

Select Case UCase(Which_Day)
Case "SUN"
iDay = 1
Case "MON"
iDay = 2
Case "TUE"
iDay = 3
Case "WED"
iDay = 4
Case "THU"
iDay = 5
Case "FRI"
iDay = 6
Case "SAT"
iDay = 7
End Select


FullDateNew = DateSerial(Year(Which_Date), Month(Which_Date), 1)

iDaysInMonth = Day(DateAdd("d", -1, DateSerial _
(Year(Which_Date), Month(Which_Date) + 1, 1)))

For i = 0 To iDaysInMonth
If Weekday(FullDateNew + i) = iDay Then
lCount = lCount + 1
End If
If lCount = Occurence Then
NthDayOfMonth = FullDateNew + i
Exit For
End If
Next i

End Function


--
Bearacade


------------------------------------------------------------------------
Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
View this thread: http://www.excelforum.com/showthread...hreadid=572736

forgot to attach the file. Here it is:


+-------------------------------------------------------------------+
|Filename: sorter1.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5235 |
+-------------------------------------------------------------------+

--
Bearacade


------------------------------------------------------------------------
Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
View this thread: http://www.excelforum.com/showthread...hreadid=572736

Hi Bearacade... Thank you so much. I spent a lot of time yesterday
with this. I wish I knew/understood more on macros - I'm going to buy
a book.... Because the sort by company macro works great. I'm just
trying to figure out how to tweak it to create the look I need.

I can't get the sort by week to work properly... I'm determined to
learn & understand what & how you created these. I really want to be
that good with it.

I can't thank you enough for the time you spent helping me.....
really 'thank you'...

Not a problem, if you sort by week within the sheets that the companies
were sorted into, it will then break it down by sheets.

The things is that if you have 5 companies, and you broke it down by
computer, then by week, you are looking at extra 25 - 30 sheets on top
of your master sheet.

It's better if you created a new workbook, then inserted sheets as you
go.

If you need any additional help, don't hesitiate to ask.


--
Bearacade


------------------------------------------------------------------------
Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
View this thread: http://www.excelforum.com/showthread...hreadid=572736