How can I copy desired cell content ?

[colwyn]

I'm using Excel 2007 and s/s is 325501 rows deep. It consists of series of ranges between 4 and 30 rows deep.

What I want to do is locate the next appearance of a name and copy its accompanying number.
Doing this manully is not feasible, given the large size of the s/s .

I enclose a small attachment showing what I am trying to achieve. For those who don't like opening attachments the wording in it is :

The desired objective is to place in column Q the next appearing number in column L of the name in column C.
The VLOOKUP formula in column Q presents the desired number but (problem!) presents a zero when next appearance = blank.
When this happens I want the formula/code to repeatedly lookup the next appearance until it finds a number.
Examples of where next numbers appear are given here in column R.


If anyone can help me to this end I would be most grateful.
Big thanks.
Colwyn.

[NBVC]

Try:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER not just ENTER. You will see { } brackets appear around the formula.

Then copy down the column.

[royUK]

Your post does not comply with Rule 5 of our Forum RULES. We have Seven question forums: Miscellaneous, General, Programming, Worksheet Functions, Charting, Excel 2007 Help and New Users. Please choose the appropriate forum, and post your question in ONLY one forum.

[colwyn]

NBVC - thanks but it isn't working.Shows #VALUE! instead of desired result.
Big thanks.
Colwyn.

[NBVC]

Did you confirm the formula with CTRL+SHIFT+ENTER after entering the formula?

[colwyn]

royUK - ???
Am requesting programming help (code).
What do you suggest I post on??
Colwyn.

[colwyn]

NBVC -yes.

[NBVC]

NBVC -yes.

See attached... It works fine for me.

[colwyn]

NBVC - thanks again.
Yes I can see but look, eg, row 119 "Betty" has 6 (Q119) when not appearing after row 119. This should show a blank. It's just when a final appearance is made that it goes wrong and shows a number instead of a blank. I think it refers back to first appearance?
Big thanks.
Colwyn.

[NBVC]

Try:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER and copied down

[colwyn]

NBVC - perfect!!
Big thanks.
Colwyn.

[NBVC]

Great!

Can you please now mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save