I have three columns, A, B, & C. column C is empty, except for a 1 in a
single cell. I need to find a way to reference the cell next to the
cell in column C that has a 1. For example, Cell C35 currently has a
1, and all other C cells are empty. I need cell A1 to be equal to Cell
B35. However, i can't just reference it because the "1" in column C is
constantly moving. When I enter new date elsewhere the 1 may be in
C72, and i now need A1 to equal C72. Any ideas? Thanks in advance.


--
flyingmeatball
------------------------------------------------------------------------
flyingmeatball's Profile: http://www.excelforum.com/member.php...o&userid=37302
View this thread: http://www.excelforum.com/showthread...hreadid=570144

flyingmeatball wrote...
I have three columns, A, B, & C. column C is empty, except for a 1 in a
single cell. I need to find a way to reference the cell next to the
cell in column C that has a 1. For example, Cell C35 currently has a
1, and all other C cells are empty. I need cell A1 to be equal to Cell
B35. However, i can't just reference it because the "1" in column C is
constantly moving. When I enter new date elsewhere the 1 may be in
C72, and i now need A1 to equal C72. Any ideas? Thanks in advance.

In the second case, presumably you mean A1 should equal B72 rather than
C72.

If there'd only ever be just one 1 in column C, and if it were a
number, try

A1:
=INDEX(B:B,MATCH(1,C:C,0))

Hi!

I need cell A1 to be equal to Cell B35.
i now need A1 to equal C72

So, which is it? A1 to equal column B or A1 to equal column C?

This is for column B:

=INDEX(B1:B100,MATCH(1,C1:C100,0))

Biff

"flyingmeatball"
<flyingmeatball.2cakru_1155159087.2904@excelforu m-nospam.com wrote in
message news:flyingmeatball.2cakru_1155159087.2904@excelfo rum-nospam.com...

I have three columns, A, B, & C. column C is empty, except for a 1 in a
single cell. I need to find a way to reference the cell next to the
cell in column C that has a 1. For example, Cell C35 currently has a
1, and all other C cells are empty. I need cell A1 to be equal to Cell
B35. However, i can't just reference it because the "1" in column C is
constantly moving. When I enter new date elsewhere the 1 may be in
C72, and i now need A1 to equal C72. Any ideas? Thanks in advance.


--
flyingmeatball
------------------------------------------------------------------------
flyingmeatball's Profile:
http://www.excelforum.com/member.php...o&userid=37302
View this thread: http://www.excelforum.com/showthread...hreadid=570144

The below formula should work for you if I understand your example. Let me
explain what I understand. In the first example you reference a 1 in C35,
thereby making A1 = B35. However in the next example you don't reference
column B. Instead you reference C72. Not sure if there is a typo there or
not. What I think I understand is that A1 is going to equal a cell in column
B when column C has a 1 in the cell, ie A1 = B100 if C100 has a value of 1.
The only question I have is will there ever be more than one cell in column C
that has a value of 1 in it?

Try this formula (based on my understanding) and place it in A1

=LOOKUP(1,C:C,B:B)

Goodluck,
Les

"flyingmeatball" wrote:


I have three columns, A, B, & C. column C is empty, except for a 1 in a
single cell. I need to find a way to reference the cell next to the
cell in column C that has a 1. For example, Cell C35 currently has a
1, and all other C cells are empty. I need cell A1 to be equal to Cell
B35. However, i can't just reference it because the "1" in column C is
constantly moving. When I enter new date elsewhere the 1 may be in
C72, and i now need A1 to equal C72. Any ideas? Thanks in advance.


--
flyingmeatball
------------------------------------------------------------------------
flyingmeatball's Profile: http://www.excelforum.com/member.php...o&userid=37302
View this thread: http://www.excelforum.com/showthread...hreadid=570144

I meant A1 = B72, thanks guys, saved my life.


--
flyingmeatball
------------------------------------------------------------------------
flyingmeatball's Profile: http://www.excelforum.com/member.php...o&userid=37302
View this thread: http://www.excelforum.com/showthread...hreadid=570144