Find variable - Return cell address

When text variable is entered (or chosen by data validation?) into cell I want formula in cell above to look up next previous occurance in same column and give cell address of that previous occurance.

Example:
"Apples" is entered (or selected by data validation) in A2, A6 & A10.
"Pears" is entered in A4 & A12.

Cell A1 would return blank (no previous "Apples")
Cell A3 would return blank (no previous "Pears")
Cell A5 would return A2 (previous "Apples")
Cell A7 & A8 would remain blank (no entry in A8 to look for so no output in A7)
Cell A9 would return A6 (previous "Apples")
Cell A11 would return A4 (previous "Pears")

Additional info:

I prefer formula search for any variable entered in cell below rather than contain the actual search words ("Apples", "Pears").

Also, eventually I'll want to continue looking back to previous worksheet (previous year of data) so first occurance on active worksheet would not be blank (unless variable never occured in that column on any worksheet in workbook).

maybe this:

Please Login or Register  to view this content.

If you want it in next column here is example:

thanx for your efforts thus far zbor, unfortunately I do not understand how to write code so it would be a steep learning curve to understand how to modify the code for my intended uses. I did play with the formula in your second example, though I don't understand why it fills in two cells at B5, B6 but only one cell at B2. However,I think I failed to explain properly what I wanted.

I want to return the cell address not the variable text in the cell. For example the formula in B6 should return "A2" because Apples was entered in A6 so it would look for next previous occurance and find it in A2 and show the cell reference "A2". This is just a piece of the puzzle, I want to use that cell address in a larger formula.

Maybe this:

Ok thanks zbor, now I see how to grab a cell address. If you feel you can help me fine-tune this a bit I would be grateful.

Sample spreadsheet is behaving strangely in four ways:

1. Any text that is entered in A1 returns blank cell in column B, from B1 on down whenever matching text is entered into any cell in A column.

2. A10:A19 returns the number 0-9 respectively; if first occurrence is listed in that range any occurrence below will return that number from initial entry.

3. A2:A9, A20:A23 return cell address of first matching occurrence reading from top down, and all occurrences following still return the same initial cell address (e.g. if apples is entered in A2 then all following occurrences will return $A$2). My goal is to show address of first previous occurrence above (scanning upward from last occurrence to next one above) - that is, each entry would give cell address of the matching entry just prior.

4. Anything after the first occurrence returns the cell address not only immediately to the right of the entry but shows duplicates address one cell above also. That is if Apples is entered in A2 and A7 it will show Apples once in B2, but twice in B6 & B7.

Since the formula looks consistent all the way down it seems strange there are such inconsistencies in behavior. I am working on parsing out what it's doing but any help is appreciated.

I must admit I'm slightly loss (or is still to early ) among all of this...

can you upload example workbook, put some input and desired output and comment it as you did above...

Also, do you want your result in new column or over existing column?

Note that in existing column (that would need a macro) in few steps all of your blanks will dissapear.

Arrgh, the post window timed out and I lost everything I just wrote…

Thank you zbor for your continued interest and patience with my ignorance. Sorry, I’m not explaining my goal properly. I tried to ask for the “whole enchilada” (solve every challenge) on an earlier post and I think I scared everyone away. Not sure how to link my other post but the title is “MATCH previous occurrence of text in column, use OFFSET to get input variable”. I will attach my test spreadsheet to this post also.

Purpose:
My spreadsheet is for tracking batches of distilled solvent. My specific challenge is tracking days elapsed between drums of waste I receive so that the days elapsed are specific to the source of the waste. Imagine if we received red drums from company A and blue drums from company B, I want to track elapsed days between red drums and elapsed days between blue drums automatically and display the elapsed days in the same column no matter what order they may come. Instead of “red” or “blue” I would be using text like “Mits”, “844” or “M-600”.

History:
I started out manually tracking elapsed days between drums of incoming waste in column B to spot trends (drums filling sooner or later than usual). Later I added a formula to automatically track elapsed days. This worked fine until we started running drums from a 2nd source and I need to track elapsed days for each source independently. I added conditional formatting to hi-light “Mits” batches and rewrote the elapsed days formula to ignore the “Mits” source (see how B11 & B17 respond to “Mits” in B10 & B16 on spreadsheet – try removing “Mits” and see the elapsed days shown in cells below change), this only figures elapsed days automatically between “normal” sources (I must manually enter the elapsed days for “Mits”). This was working OK also.

Current goal:
Now I want to fully automate column B so anyone can use spreadsheet with minimal work and less chance for errors. To accomplish this turned out to be more difficult than I thought. B16 is a sample cell using data validation to offer 5 (one blank) valid sources to reduce user input errors; data validation would be ideal but not absolutely necessary.

My plan (that didn’t work):
My original thought was, if I could just scan upwards to first previous occurrence just above current entry and return that cell address I could use that result inside a formula (along w/ OFFSET?) to locate correct date in C column and figure the elapsed days between entries. However my formula used the word “Mits” in it so formula “found” “Mits” in every cell containing formula (even though cell showed blank). I liked your approach in that your formula did not specifically use the text variable but rather matched whatever text was entered. Unfortunately the formula seemed to do unexpected things so I was still stuck.

Another consideration:
The only other twist is that sometimes I need to run a double batch. This means in addition to the normal elapsed days (between 1st barrel of current batch and last barrel of previous batch), I will also need to show elapsed days between 2nd and 1st barrel of current batch (shown by dates in C21 & C22 with “? 2 ?” in B22 – 2 being the correct number of days the cell should show). This means the normally blank even numbered cell (B22) that occasionally shows non-normal source name (e.g. “Mits”) would instead show elapsed days. Although this could be a real challenge to write a formula, this is not a problem in use of spreadsheet because there will never be a double batch of non-normal source requiring text input so the even numbered cell would be blank anyway (if it weren’t for the double-drum batch).

There, that’s the whole picture. If this is more confusing than helpful just let me know and I will try to explain in different way.

I’m sure my post title does not represent my question well either, if anyone understands what I’m trying to do well enough to help me provide a better title for my post I would really appreciate that also.