How can I count downward a specific number of cells to retrieve data?

Based on some other unrelated stuff in the sheet I need to tell XL to go down a column, a certain number of cells, and retrieve the contents.

FORUM BOOK UPLOAD.xlsx

Originally Posted by Ace_XL

try

=index(Your_column,number_of_cells)

so for instance

=Index(A:A,B1)

where B1 contains the the value of the number of cells you wish to go down to and Column A is the relevant column

Index is a good start, but I would need to generate on the fly the range in the column from which to start.

See attached book.

B:B is the pairing names. They can be 1 or many rows deep. They can start on any row. So, tagging cell references will be hard.

In the case of B6 I've already calculated it is 7 rows deep. Col A actually has a function working to include the pairing number for some other reasons.

I can test how many rows have both the B6 name and a certain value in P:P across from it.

What I don't know how to do is tell it to find the LAST occurrence and stop there.

I only need the last occurrence of Col P that has certain values.

Once i get that I'll move over to the pay column and do some work there.

Confusing enough?

Originally Posted by FDibbins

perhaps it would be easier to help you if you uploaded a sample workbook, showing what data you are working with, a few examples of what your expected outcome would be, and how you would arrive at that (remove any confidential info if necessary).

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

Uh, it was the first line in post #3

Originally Posted by Ace_XL

Try this array formula for H3A31

Please Login or Register  to view this content.

Adjust ranges and column A values accordingly

Remember to confirm the formula with Ctrl+Shift+Enter and not just Enter

Does this give you desired results?

Well that got it to the last line, but if the last line isn't one of the values in (W3, X3, Y3, AA3) I need it to back up one until it reaches one of those. One of those four needs to be the last line.

I'm new to the ctrl-shift-enter idea. No VBA experience. Sorry.

Originally Posted by FDibbins

ok thanks rcane - if you look at the times, I posted 2 minutes after you, i didnt see that you had replied before

Yeah, but I should've put it at the bottom like everyone else. I didn't see it until after posting.