Reference a the first cell when.... (not sure how else to explain this)

Sorry for the bad title. I'm a little lost but my image example should help clarify:

I need a formula in Column C that will: (see image for example)
1) Reference column A and display the value for the first number in that column only. Repeat that first number and ignore the other numbers until we get a number in column B.
2) Reference column B and display the value for the first number in that column only. Repeat that first number and ignore the other numbers until we get a number in column A.

EXAMPLE: (Attached image should explain it clearly)
Excel Example.jpg

6StringJazzer Your Great!! Thanks a tone.

I made one small mistake. Your problem works perfect for the example I gave but if a cell happens to have a formula in it, instead of a blank cell, then I do not get the same result. This time I attached the file itself. I hope I get another quick reply. Thanks again. File attached:

Excel Forum Question 2 - May2,14.xls

I have tried for hours to solve this last glitch. I hope your still willing to help as your great at this.

Final Problem:
Everything works great except in one scenario. If I have a ZERO in the last row of a column prior to switching to the other column, then I do not get the desired result. Instead of the new number being displayed in C, the same C value repeats. Again, the spread sheet is probably MUCH more clear than I can explain it. (Also I made quite a few changes as I did solve on other problem, with zero for a result, on my own. I just could not solve this one.)Excel Forum Question 3 - May2,14.xls

THANKS A TONE!!

Man your FAST! Check out the attachment. I greyed out the part we can't work with:
Excel Forum Question 5 - May2,14.xls

Well... hmmm... This is how I would verbally explain it.

Column H always displays the first result in G until we get the first result in F and vice versa. Does this answer the question? The previous suggestions were super, super close with just a few glitches. Does this help?

Yes kind of. More of a test. I was just trying different combinations to test the formula and I forgot to delete thatfinal. KLMN can be totally ignored as they were created simply to help make the final formula accurate.

I just want to use P&Q in the R column formula.

Thank you. I hope someone can figure it out. I think it should be possible but I just don't have enough tools in my brain to fix it. Thanks for passing it on. I hope to hear something soon.

In the meantime can anyone help?

Originally Posted by Winon

Hello Mr_Fancy_Pants,

Please see Sheet1 of the attached sample Workbook.

Regards.

Please see my SHEET 2 of the attached sample workbook. Let me know if this is not clear.

Excel Forum Question 6 - May2,14.xls

While I wait for a few that may not be online for a while, can anyone else help here? My last example should be very clear.

Can anyone help with my formula request? I hate to start over in another forum. I'm hoping we can solve this here.

Originally Posted by FDibbins

OK I *think* I have it now. In R20, copied down...
=IF(P20=0,IF(P19=0,R19,Q20),IF(AND(IF(Q20=0,P20,0)<>0,IF(Q19=0,P19,0)=0),IF(Q20=0,P20,0),R19))

SO CLOSE! The problem is when there are zero's in both I&J. Please check out the attachment: (I hope we can fix it)
Excel Forum Question 8 - May2,14.xls

in column C use this formula, based on your example file, it was placed in C4 and copied/pastd down

Please Login or Register  to view this content.

i'll attach the file in a sec

i think i just lost the plot

but here's an example file of what i thought this guy wanted

might need to make adjustments to the formula to account for zero values rather than "" in cells

Originally Posted by bebo021999

How about this:
=IF(OR(SUM($I$20:I20)=0,SUM($J$20:J20)=0),$I$20+$J$20,INDEX($I$20:$I$41+$J$20:$J$41,MIN(LOOKUP(2,1/($I$20:I20>0),ROW($I$20:I20)-MIN(ROW($I$20:I20))+1),LOOKUP(2,1/($J$20:J20>0),ROW($I$20:I20)-MIN(ROW($I$20:I20))+1))+1))

Thank you very much for trying to but it doesn't work just yet. The error occurs when there are zero's one row prior to switching to a new column. I attached the workbook highlighting the error make it very clear. I hope you can think of a way to fix this. Thanks again.
Excel Forum Question 8 - May2,14 (B).xls

Originally Posted by ThirtyTwo

i think i just lost the plot

but here's an example file of what i thought this guy wanted

I appreciate your effort but it seems to not work when "formulas" instead of absolute values are used for inputs. Earlier in this thread we had to address this and we have been having problems finding a solution ever since. I have posted a lot of example files to try to demonstrate exactly what I'm looking for... it has been tough! I've attached another one here.
Excel Forum Question 5 - May2,14.xls

Originally Posted by FDibbins

I almost have it, but the variations of values and zeros in P throw me off every time

	P	Q	R	S
24	5	0	5	5
25	0	0	0	5
26	7	0	7	7
27	0	0	0	7
28	3	0	3	3

I changed it to this...
=IF(AND(P20=0,Q20=0),S19,IF(P20=0,IF(P19=0,R19,Q20),IF(AND(IF(Q20=0,P20,0)<>0,IF(Q19=0,P19,0)=0),IF(Q20=0,P20,0),R19)))

but is still kicks out if there is a value in A after a value/zero combo

I really seem to have posted a doozy. I'm still looking for that solution. Are you still trying or have you thrown in the towel? I really appreciate your effort.

Can anyone help? Can anyone modify these close attempts to solve their glitches?

Originally Posted by Winon

Hello Mr_Fancy_Pants,

This is a difficult one. Maybe as per the revised sample on Sheet 2 of the Workbook attached?

Regards

I attached the glitch we get with the file you attached. Zero's on both sides cause a problem with the result.
Excel Forum Question 6 - May6,14-Winon (B).xls

Originally Posted by romperstomper

Maybe like this?

I'm getting excited but a little confused. When I open the spreadsheet it works as displayed. If I click on the formula and hit enter (without making any changes) I get the "#VALUE!" error. In other words the formula appears to be working until I tried to inspect it (but without making changes). Thoughts?
Excel Forum Question 5 - May2,14 (B).xls

Originally Posted by romperstomper

Maybe like this?

I'm getting excited but a little confused. When I open the spreadsheet it works as displayed. If I click on the formula and hit enter (without making any changes) I get the "#VALUE!" error. In other words the formula appears to be working until I tried to inspect it (but without making changes). Thoughts?
Attachment 316400

Duplicate post by accident. Sorry. I'd delete it if I could.

Originally Posted by romperstomper

It's an array formula so you have to use Ctrl+Shift+Enter to enter it in a cell, not just Enter.

Thanks. This may sound silly but how do I copy the formula down? I get the error message "you can not change part of an array".