Home 
Search 
Today's Posts 
#1




put formula results into a different cell if it is empty
It seem that everything is designed with the cell containing a formula as the
destination for the results of the formula. I want to populate a different cell based on the results of a formula. For instance I have a formula in cell A1 that checks cell B1, if it is empty (null) I want to give it the value contained in C1. If B1 contains data I want to leave it alove. I can't put the formula in cell B1 because if I enter any data into it I replace the formula. How can I do this. I'm sure that I just don't have the right vocabulary to figure out the function name (e.g. It took me a while to find the indirect function because I was thinking of the terms "GET" etc. and not "indirect") Thanks 
#2




OK. I think we have a few conceptual issues here...
You wrote.. It seem that everything is designed with the cell containing a formula as the destination for the results of the formula. Spot on. It is not possible to change any other cell (value or formatting) solely by using a formula (builtin or userdefined). A formula only affects the cell it is in. You wrote.. I want to populate a different cell based on the results of a formula. Can't do it, see above. What you want to do is reference Data containing cells with a cell containing a formula, in order to display the referenced data, based on a set of arguments (a formula)... This (imho) is the main principle of a spreadsheet, and answers what is essentially your question here...and you are almost there. You talk of cells A1, B1, & C1. You want to check cell B1 for data and populate with C1 if none exists, right? But you cannot have a formula AND data in the same cell. Excel provides you with 256 columns & 65536 rows, thats 16777216 individual cells on a sheet! So why not have B1 & C1 as Data containers (whether that "data" exists or not) and use A1 to perform your test & display your result (a Formula container, if you like)? So A1 contains a formula that says "If B1 has data, then show the value of B1, and if B1 doesn't have data, show the value of C1" (the formula in A1 would look like this "=If(B1="",C1,B1"). A1 is now the equivelent of B1 in your example. Does this help? Oli  Message posted via http://www.officekb.com 
#3




Hi!
You can't do that but I'm a little confused. If you have a formula in cell A1 then the result of your formula HAS to be in A1. A formula can only return a value to the cell that contains that formula. Biff Original Message It seem that everything is designed with the cell containing a formula as the destination for the results of the formula. I want to populate a different cell based on the results of a formula. For instance I have a formula in cell A1 that checks cell B1, if it is empty (null) I want to give it the value contained in C1. If B1 contains data I want to leave it alove. I can't put the formula in cell B1 because if I enter any data into it I replace the formula. How can I do this. I'm sure that I just don't have the right vocabulary to figure out the function name (e.g. It took me a while to find the indirect function because I was thinking of the terms "GET" etc. and not "indirect") Thanks . 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
How do I prevent incorrect formula results appearing in cell?  Excel Worksheet Functions  
inserting data from a row to a cell, when the row number is specified by a formula in a cell  New Users to Excel  
I want the results of a formula to show in cell, NOT THE FORMULA!  Excel Discussion (Misc queries)  
GET.CELL  Excel Worksheet Functions  
can i colour a cell on basis of results of a formula e.g clour bl.  Excel Worksheet Functions 