Sort with two variables

[Guest]

Hi,
I'm looking for help with a formula.

In a file, report.xls A1, I am trying...
IF([data.xls]car'!A1="7:00",IF([data.xls]car'!B1="Car",'(car.xls!A3:A5)))

The dilemma is the data.xls file will skip some half hour intervals; while
the report.xls adds all intervals, even when there are no numbers to report.
dilemma #2...
Retort.xls has two tabs- truck and car.
Any help or suggestions are greatly appreciated.
Thanks,
Jeff

data.xls (this daily report is 2X100's pages)
7:00 car 65 98 78
7:30 truck 5 16 35
8:00 car 45 87 99
8:30 car 44 89 122
10:00 truck 15 79 342
10:30 truck 16 72 45
10:30 car 85 24 57

report.xls (car tab after formula)
7:00 car 65 98 78
7:30
8:00 car 45 87 99
8:30 car 44 89 122
9:00
9:30
10:00
10:30 car 85 24 57

retort.xls (truck tab after formula)
7:00
7:30 truck 5 16 35
8:00
8:30
9:00
9:30
10:00 truck 15 79 342
10:30 truck 16 72 45

 

[Max]

One interp / way ..
(Link to a sample file is provided below)

Assume the source data below is in a sheet: Data, A1:E7

7:00 car 65 98 78
7:30 truck 5 16 35
8:00 car 45 87 99
8:30 car 44 89 122
10:00 truck 15 79 342
10:30 truck 16 72 45
10:30 car 85 24 57

In sheet: Data
Put in F1: =TEXT(A1,"hh:mm")
Copy down

Click Insert > Name > Define
Put under "Names in workbook:": WSN
Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")
))+1,32)
Click OK
(The above defines WSN as a name we can use to refer to the sheetname in
formulas. It auto-extracts the sheetname implicitly. Technique came from a
post by Harlan)

In sheet: Car
Put in A1: 7:00, in A2: 7:30
Select A1:A2, fill down to A48 to label the 48 half-hourly time segments for
a day

Put into the formula bar for B1,
array-enter (i.e. press CTRL+SHIFT+ENTER):

=IF(ISNA(MATCH(TEXT($A1,"hh:mm")&"_"&WSN,Data!$F$1:$F$100&"_"&Data!$B$1:$B$1
00,0)),"",INDEX(Data!B$1:B$100,MATCH(TEXT($A1,"hh:mm")&"_"&WSN,Data!$F$1:$F$
100&"_"&Data!$B$1:$B$100,0)))

Copy B1 across to E1, fill down to E48

The above will extract the data for rows with "Car" in sheet: Data and
return these nicely matched in the correct time segment, viz. you'll get:

7:00 car 65 98 78
7:30
8:00 car 45 87 99
8:30 car 44 89 122
9:00
9:30
10:00
10:30 car 85 24 57

Now just make a copy of the sheet: Car, and rename the copy as: Truck.
You'll get the corresponding returns for rows with "Truck" from sheet: Data

7:00
7:30 truck 5 16 35
8:00
8:30
9:00
9:30
10:00 truck 15 79 342
10:30 truck 16 72 45

Here's a sample file with implemented set-up:
http://flypicture.com/p.cfm?id=55269

(Right-click on the link: "Download File"
at the top in the page, just above the ads)

File:Jeff_newusers_1.xls
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

Hi,
I'm looking for help with a formula.

In a file, report.xls A1, I am trying...
IF([data.xls]car'!A1="7:00",IF([data.xls]car'!B1="Car",'(car.xls!A3:A5)))

The dilemma is the data.xls file will skip some half hour intervals; while
the report.xls adds all intervals, even when there are no numbers to report.
dilemma #2...
Retort.xls has two tabs- truck and car.
Any help or suggestions are greatly appreciated.
Thanks,
Jeff

data.xls (this daily report is 2X100's pages)
7:00 car 65 98 78
7:30 truck 5 16 35
8:00 car 45 87 99
8:30 car 44 89 122
10:00 truck 15 79 342
10:30 truck 16 72 45
10:30 car 85 24 57

report.xls (car tab after formula)
7:00 car 65 98 78
7:30
8:00 car 45 87 99
8:30 car 44 89 122
9:00
9:30
10:00
10:30 car 85 24 57

retort.xls (truck tab after formula)
7:00
7:30 truck 5 16 35
8:00
8:30
9:00
9:30
10:00 truck 15 79 342
10:30 truck 16 72 45

 

[Max]

Here's a new link to the sample file
with the implemented construct:
http://www.savefile.com/files/9806240
File: Jeff_newusers_1.xls