hi max,
Need you help again. I tried your fomula and did what you instructed and it
worked on a sample worksheet. However when i tried applying to my real
worksheet, it doesn't work. I get 0 results even though there are matching
rows from the source table. Alright here's what i have:
the source table is in, let's say worksheet A:
the entries start at row 8 so i have:
A B C D
1
X
7 Name Date Status
8 mel1 4/8/2008 New
9
so on and so forth...
now the results table is in, let's say worksheet B
I changed the formula so it will refer to B8 to check for the date in
worksheet A. I dont get any errors however my results table looks like this
A B C D
E
..
..
..
38 Name Date
Status
39 Date(Input) 39 0 0
0
40 40 0 0
0
41
this is what happens...this is with 2 matching rows on the source table.
Can't figure out whats wrong. Can you also explain the formula to me as well
so i can better undertand how to use it? Is that ok? I know im asking a lot
here...but please i do need help. With the formula you gave, this is my
understanding...
=IF($A$2="","",IF(AND(A!B2=$A$2-7,A!B2<=$A$2),ROW(),""))
-if value of B2 is in between $a$2 and $a$2-7 then get row? cnt seem to
understand what row() does?
=IF(ROWS($1:1)COUNT($B:$B),"",INDEX(A!A:A,SMALL($ B:$B,ROWS($1:1))))
- really cant get this one, dont know how it relates to the result. :-(
help!
thanks,
--
"excel newbie"
"Max" wrote:
Assume your source table is in sheet: A, cols A to C (say)
data from row2 down, where real dates are in B2 down
In your results sheet,
Assume a specific date will be input in A2
In B2:
=IF($A$2="","",IF(AND(A!B2=$A$2-7,A!B2<=$A$2),ROW(),""))
Leave B1 empty. Col B is the criteria col.
In C2:
=IF(ROWS($1:1)COUNT($B:$B),"",INDEX(A!A:A,SMALL($ B:$B,ROWS($1:1))))
Copy C2 to E2. Select A2:E2, copy down to cover the max expected extent of
data in A. Format col D as dates, minimize/hide away col B. Cols C to E will
return the source lines from A with dates within 7 previous days of the date
specified in A2 (inclusive), as desired.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"melmac" wrote:
I'm hopeful that someone can help me with my excel problem. Here's my
dilemma: I'm trying to do a results table where results will come from a
worksheet that has different columns, one of which is a date column(let's
call it worksheet A). Now the results table is in another worksheet in the
same workbook, and before results are generated, I'd like the user to specify
a date and then the results table will be populated with entries from
worksheet a that matches the specified date and dates from the previous week.
im not quite sure if it's possible in excel, however if it is possible, what
type of control should i use as the results table. Please help...
thanks in advance,
--
"excel newbie"