Remember Me?

 amc422 Posts: n/a Returning a Value to a Cell Based on a Range of Uncertain Size

I am trying to return a value to a cell based on specified conditions in a
range that increases every day by one or more rows. I want return the value
of a cell which will positionally change daily (but will always be in the
same column) as new data is entered (and likely be the last cell of the
active spreadsheet) to another cell which will remain constant.

To illustrate:

Column A Column B Column C
Row 1

Row 2
Row3
Row4
Ect...

I know I need to use conditional logic, but can't get the syntax. I want
the value in Column C returned to another defined cell if the following
conditions are met:

There is numerical data in column A
AND
There is no data in column B

But here's the rub (for me): it must be the value of the last "active" cell
in column C which meets the specified conditions. Since a new row(s) of data
is entered to the spreadsheet on a daily basis, the row of the last active

Hope this makes sense.

Anna If A1 houses a number and B1 is empty, return the last numerical value
that is in C. Is this what you want?

amc422 Wrote:
I am trying to return a value to a cell based on specified conditions in
a
range that increases every day by one or more rows. I want return the
value
of a cell which will positionally change daily (but will always be in
the
same column) as new data is entered (and likely be the last cell of
the
active spreadsheet) to another cell which will remain constant.

To illustrate:

Column A Column B Column C
Row 1

Row 2
Row3
Row4
Ect...

I know I need to use conditional logic, but can't get the syntax. I
want
the value in Column C returned to another defined cell if the
following
conditions are met:

There is numerical data in column A
AND
There is no data in column B

But here's the rub (for me): it must be the value of the last "active"
cell
in column C which meets the specified conditions. Since a new row(s)
of data
is entered to the spreadsheet on a daily basis, the row of the last
active

Hope this makes sense.

Anna

--
------------------------------------------------------------------------

 amc422 Posts: n/a Oops... forgot one more condition. In addition to those I specified in my
previous post:

If there is numerical data in Column A
AND
There is numerical data in Column B
Return a value of "0" to the defined cell

Thanks,
Anna

"amc422" wrote:

I am trying to return a value to a cell based on specified conditions in a
range that increases every day by one or more rows. I want return the value
of a cell which will positionally change daily (but will always be in the
same column) as new data is entered (and likely be the last cell of the
active spreadsheet) to another cell which will remain constant.

To illustrate:

Column A Column B Column C
Row 1

Row 2
Row3
Row4
Ect...

I know I need to use conditional logic, but can't get the syntax. I want
the value in Column C returned to another defined cell if the following
conditions are met:

There is numerical data in column A
AND
There is no data in column B

But here's the rub (for me): it must be the value of the last "active" cell
in column C which meets the specified conditions. Since a new row(s) of data
is entered to the spreadsheet on a daily basis, the row of the last active

Hope this makes sense.

Anna

 amc422 Posts: n/a Yes, however it won't always be A1 and B1... the row will advance by at least
one every day. So it would be A1 and B1 today; A2 and B2 tomorrow; possibly
A5 and B5 the following day (as more than one row of data could be added in a
given day).

Can this be done?

Thanks,
Anna

If A1 houses a number and B1 is empty, return the last numerical value
that is in C. Is this what you want?

amc422 Wrote:
I am trying to return a value to a cell based on specified conditions in
a
range that increases every day by one or more rows. I want return the
value
of a cell which will positionally change daily (but will always be in
the
same column) as new data is entered (and likely be the last cell of
the
active spreadsheet) to another cell which will remain constant.

To illustrate:

Column A Column B Column C
Row 1

Row 2
Row3
Row4
Ect...

I know I need to use conditional logic, but can't get the syntax. I
want
the value in Column C returned to another defined cell if the
following
conditions are met:

There is numerical data in column A
AND
There is no data in column B

But here's the rub (for me): it must be the value of the last "active"
cell
in column C which meets the specified conditions. Since a new row(s)
of data
is entered to the spreadsheet on a daily basis, the row of the last
active

Hope this makes sense.

Anna

--
------------------------------------------------------------------------

 Domenic Posts: n/a I'm not sure if this is what you're looking for, but try the
following...

If the values in Column C are text...

D1:

=MAX(IF((ISNUMBER(A2:INDEX(A:A,MATCH(REPT("z",255) ,C:C))))*(B2:INDEX(B:B,MATCH(REPT("z",255),C:C))=" "),ROW(A2:INDEX(A:A,MATCH(REPT("z",255),C:C))) ))

E1:

=IF(N(D1),INDEX(C:C,D1),"")

If the values in Colum C are numerical...

D1:

=MAX(IF((ISNUMBER(A2:INDEX(A:A,MATCH(9.99999999999 999E+307,C:C))))*(B2:INDEX(B:B,MATCH(9.99999999999 999E+307,C:C))=""),ROW(A2:INDEX(A:A,MATCH(9.999999 99999999E+307,C:C)))))

E1:

=IF(N(D1),INDEX(C:C,D1),"")

Hope this helps!

amc422 Wrote:
I am trying to return a value to a cell based on specified conditions in
a
range that increases every day by one or more rows. I want return the
value
of a cell which will positionally change daily (but will always be in
the
same column) as new data is entered (and likely be the last cell of
the
active spreadsheet) to another cell which will remain constant.

To illustrate:

Column A Column B Column C
Row 1

Row 2
Row3
Row4
Ect...

I know I need to use conditional logic, but can't get the syntax. I
want
the value in Column C returned to another defined cell if the
following
conditions are met:

There is numerical data in column A
AND
There is no data in column B

But here's the rub (for me): it must be the value of the last "active"
cell
in column C which meets the specified conditions. Since a new row(s)
of data
is entered to the spreadsheet on a daily basis, the row of the last
active

Hope this makes sense.

Anna

--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785

 amc422 Posts: n/a The values in Column C are numerical, so I applied the formula you listed
under D1. It does return a value of "0" if there is data in Column A and
Column B (horray!). However, it does not return the value in Column C if
there is data in Column A and no data in Column B.

Is the formula you listed under E1 for that condition? If so, I need to
have the entire formula for all the conditions in one cell (e.g., return "0"
if appropriate condition is met, or return the value in Column C if
appropriate condition is met to the same cell).

Does that make sense?

Anna

"Domenic" wrote:

I'm not sure if this is what you're looking for, but try the
following...

If the values in Column C are text...

D1:

=MAX(IF((ISNUMBER(A2:INDEX(A:A,MATCH(REPT("z",255) ,C:C))))*(B2:INDEX(B:B,MATCH(REPT("z",255),C:C))=" "),ROW(A2:INDEX(A:A,MATCH(REPT("z",255),C:C))) ))

E1:

=IF(N(D1),INDEX(C:C,D1),"")

If the values in Colum C are numerical...

D1:

=MAX(IF((ISNUMBER(A2:INDEX(A:A,MATCH(9.99999999999 999E+307,C:C))))*(B2:INDEX(B:B,MATCH(9.99999999999 999E+307,C:C))=""),ROW(A2:INDEX(A:A,MATCH(9.999999 99999999E+307,C:C)))))

E1:

=IF(N(D1),INDEX(C:C,D1),"")

Hope this helps!

amc422 Wrote:
I am trying to return a value to a cell based on specified conditions in
a
range that increases every day by one or more rows. I want return the
value
of a cell which will positionally change daily (but will always be in
the
same column) as new data is entered (and likely be the last cell of
the
active spreadsheet) to another cell which will remain constant.

To illustrate:

Column A Column B Column C
Row 1

Row 2
Row3
Row4
Ect...

I know I need to use conditional logic, but can't get the syntax. I
want
the value in Column C returned to another defined cell if the
following
conditions are met:

There is numerical data in column A
AND
There is no data in column B

But here's the rub (for me): it must be the value of the last "active"
cell
in column C which meets the specified conditions. Since a new row(s)
of data
is entered to the spreadsheet on a daily basis, the row of the last
active

Hope this makes sense.

Anna

--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785 D1:

=IF(ISNUMBER(A1),IF(B1="",LOOKUP(9.99999999999999E +307,C:C),1-ISNUMBER(B1)),"")

amc422 Wrote:[color=blue]
Yes, however it won't always be A1 and B1... the row will advance by at
least
one every day. So it would be A1 and B1 today; A2 and B2 tomorrow;
possibly
A5 and B5 the following day (as more than one row of data could be
given day).

Can this be done?

Thanks,
Anna

If A1 houses a number and B1 is empty, return the last numerical

value
that is in C. Is this what you want?

...

--
------------------------------------------------------------------------

 Domenic Posts: n/a Or is this what you're looking for...

=IF(ISNUMBER(A1)*ISNUMBER(B1),IF((LOOKUP(2,1/A1:A10,A1:A10))*(INDEX(B:B,LOOKUP(2,1/A1:A10,ROW(A1:A10)))=""),LOOKUP(9.99999999999999E+ 307,C:C),0),"")

or, maybe this one...

=IF(ISNUMBER(A1)*ISNUMBER(B1),IF((LOOKUP(2,1/A1:A10,A1:A10))*(INDEX(B:B,LOOKUP(2,1/A1:A10,ROW(A1:A10)))=""),INDEX(C:C,LOOKUP(2,1/A1:A10,ROW(A1:A10))),0),"")

--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post DocuMike Excel Discussion (Misc queries) 3 January 5th 05 08:46 PM Dewayne Excel Discussion (Misc queries) 4 December 29th 04 03:23 PM mpierre Charts and Charting in Excel 2 December 29th 04 01:01 PM Josh7777777 Excel Worksheet Functions 2 November 2nd 04 07:42 PM Cameron Stewart Excel Worksheet Functions 2 November 2nd 04 01:36 AM

All times are GMT +1. The time now is 02:37 AM. Copyright ©2004-2020 ExcelBanter.