Home 
Search 
Today's Posts 
#1




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 cell is is always advancing. Hope this makes sense. Can someone please help me? Thanks in advance, Anna 
#2




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 cell is is always advancing. Hope this makes sense. Can someone please help me? Thanks in advance, Anna  Aladin Akyurek  Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165 View this thread: http://www.excelforum.com/showthread...hreadid=278034 
#3




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 cell is is always advancing. Hope this makes sense. Can someone please help me? Thanks in advance, Anna 
#4




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). Also, please note my last post with an additional condition. Can this be done? Thanks, Anna "Aladin Akyurek" wrote: 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 cell is is always advancing. Hope this makes sense. Can someone please help me? Thanks in advance, Anna  Aladin Akyurek  Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165 View this thread: http://www.excelforum.com/showthread...hreadid=278034 
#5




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 cell is is always advancing. Hope this makes sense. Can someone please help me? Thanks in advance, Anna  Domenic  Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785 View this thread: http://www.excelforum.com/showthread...hreadid=278034 
#6




Thank you for your help.
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 cell is is always advancing. Hope this makes sense. Can someone please help me? Thanks in advance, Anna  Domenic  Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785 View this thread: http://www.excelforum.com/showthread...hreadid=278034 
#7




Still uncertain about the task... D1: =IF(ISNUMBER(A1),IF(B1="",LOOKUP(9.99999999999999E +307,C:C),1ISNUMBER(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 added in a given day). Also, please note my last post with an additional condition. Can this be done? Thanks, Anna "Aladin Akyurek" wrote: If A1 houses a number and B1 is empty, return the last numerical value that is in C. Is this what you want? ...  Aladin Akyurek  Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165 View this thread: http://www.excelforum.com/showthread...hreadid=278034 
#8




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 View this thread: http://www.excelforum.com/showthread...hreadid=278034 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Delete Cell Value Based on Another Cell T or F  Excel Discussion (Misc queries)  
View and Cell Sum Range  Excel Discussion (Misc queries)  
make a cell empty based on condition  Charts and Charting in Excel  
returning a text cell based on a number cell  Excel Worksheet Functions  
How to populate column with formula based on value in cell  Excel Worksheet Functions 