Help w/ Worksheet Function

[nrage21]

Good afternoon experts.

I've been hitting my head against an iron pole the last couple of days looking for the best way to create a worksheet function that will allow me to copy and paste some cells from another sheet within the same workbook to create a transmittal form. I have zipped an excel file to try to explain my goal.

Basically what I want is the user to type the number of the "row" (i.e 6) on the transmittal sheet and the worksheet function to pull certain info from all the rows below the one typed (i.e. 7, 10, 12, etc) in the User sheet in which column E is equal to "y."

I bet someone can do this in like 5 mins.

Thanks in advance guys and gals.

- Nrage -

[Ron Coderre]

Why use a row number reference?
It can be done with row refs, but isn't there something more
appropriate for the user the enter? Perhaps a date?

[nrage21]

Well, users could review 50 applications on a timeframe of 3 days to a week before a transmittal is necessary. Can this be done with several worksheet functions? or is it more appropriate to use VBA code?

- nrage -

[Ron Coderre]

Ok....using the row ref approach and your sample file....try this:

I made this example purposely "sloppy" so you can see all of the working parts. We can clean it up after you're comfortable with it.

The below cells contain the column numbers that the below formulas will use to pick data from the User sheet.
B5: 4
C5: 3
D5: 2

Please Login or Register  to view this content.

Copy that formula into B7:D16
(that will list a values associated with matches)

You could then hide Row_5 and Col_E.

Is that something you can work with?
Post back with more questions.

[nrage21]

Thank you Ron.

Your suggested approach is perfect. In additiona, I am going to follow your recommendations and hide row 5 and colum E on the transmittal form and protect those cells so they cannot be deleted accidentally.

< I have stopped hitting my head on the iron pole >

- Nrage - <= happy camper