Look up or matching data

[Joey]

Hi

I have a formula that works well for transferring data but I need it t
recognise another cell (it has the month)

Attached is a spreadsheet of basically what I'm looking to do.

In Sheet2 when a tech # is entered that matches the data in sheet1 the
the result of what I'm looking for appears in Sheet2 B14 to D27.

The only problem now is that these results should be listed accordin
to the data in Sheet2 G2 (Month dropdown) as well, which refers t
Column H in Sheet1

The formula currently in use in the results area is
=IF(ROW()-13>COUNTIF(Sheet1!$A$2:$A$30,$D$2),"",OFFSET(INDEX(Sheet1!$A$2:$A$30,MATCH($D$2,Sheet1!$A$2:$A$30,0)),ROW()-14,7))

Is it possible to modify this formula to include the data in G2?


Many thanks all
Joe

Attachment filename: timesheet test 1.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=57141

 

[Jim Rech]

It might be difficult to do what you want with formulas. Excel does have
some querying capabilities built-in though that are convenient to use with
macros. I'll email you an example.
--
Jim Rech
Excel MVP

| Hi
|
| I have a formula that works well for transferring data but I need it to
| recognise another cell (it has the month)
|
| Attached is a spreadsheet of basically what I'm looking to do.
|
| In Sheet2 when a tech # is entered that matches the data in sheet1 then
| the result of what I'm looking for appears in Sheet2 B14 to D27.
|
| The only problem now is that these results should be listed according
| to the data in Sheet2 G2 (Month dropdown) as well, which refers to
| Column H in Sheet1
|
| The formula currently in use in the results area is
|
=IF(ROW()-13>COUNTIF(Sheet1!$A$2:$A$30,$D$2),"",OFFSET(INDEX(Sheet1!$A$2:$A$30,MATCH($D$2,Sheet1!$A$2:$A$30,0)),ROW()-14,7))
|
| Is it possible to modify this formula to include the data in G2?
|
|
| Many thanks all
| Joey
|
| Attachment filename: timesheet test 1.xls
| Download attachment:
http://www.excelforum.com/attachment.php?postid=571416
| ---
| Message posted
|