Excel 2007 : Search and Return Previous Non Zero Values

[samtoucan]

Hi All

First Up, thanks for all your help in the past, its been most valuable.

OK, onto the problem. See attached. The problem comes in 2 parts. I need help with section1. Section 2 I can probably do.

1) sheet CP1, Column A has data with gaps in it. What I want is...
In column B, if there is a gap, return the previous non zero value.
Looking around...
http://www.excelforum.com/excel-gene...cent-rows.html
it looks like an index and match combination should do the trick (donkey Ote to the rescue again!). Should be easy but its not quite working.

2) just to complicate things...I only need the above result returned if there is a result in Column A of the Analysis sheet. In the attached s/s Analysis!A2 = CP1, so go to sheet CP1, and return the value in column B row 2.
SO, was thinking that the search formula should be on the Analysis page.

I currently use the below formula (on row 203) to return data from CP1,ColB,row203, but it does not search previous rows.
=IF($A203<>"F",INDIRECT(ADDRESS(ROW(),2,,,$A203),TRUE),"F")
(sorry used 203 as its the first time it appears and did not want to modify/wreck it).
I will modify this to include your answer to section 1.

Thankyou for any help in advance

Cheers

Sam

BORING STUFF...
Number of rows to search
Searching 'back/up' 20 rows should be heaps.

Data Notes
values in CP1 are numerical. blanks are tabs. The data is imported from a tab separated CSV file.

Sheet Notes
There are multiple CPx sheets, and 10,000+ rows per sheets, so doing the search on the CP sheets is going to bog down. SO, thought it best to only do it when required. IE when the Analysis sheet says so.

[samtoucan]

and now with bonus attachments !

[DonkeyOte]

If I've understood...

Please Login or Register  to view this content.

INDIRECT is Volatile though - see link in sig. for more info.

[samtoucan]

You're a legend Donkey Ote! Solved 1 and 2 in one fell swoop. THANKYOU