A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

put formula results into a different cell if it is empty



 
 
Thread Tools Display Modes
  #1  
Old February 11th 05, 03:03 AM
PutFormula
external usenet poster
 
Posts: n/a
Default 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
Ads
  #2  
Old February 11th 05, 03:30 AM
Oliver Ferns via OfficeKB.com
external usenet poster
 
Posts: n/a
Default

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 (built-in 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  
Old February 11th 05, 03:31 AM
Biff
external usenet poster
 
Posts: n/a
Default

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
>.
>

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I prevent incorrect formula results appearing in cell? Marc Todd Excel Worksheet Functions 2 January 26th 05 07:57 AM
inserting data from a row to a cell, when the row number is specified by a formula in a cell [email protected] New Users to Excel 2 January 6th 05 07:18 AM
I want the results of a formula to show in cell, NOT THE FORMULA! ocbecky Excel Discussion (Misc queries) 4 December 10th 04 08:39 PM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 07:16 PM
can i colour a cell on basis of results of a formula e.g clour bl. K Excel Worksheet Functions 3 November 4th 04 06:18 PM


All times are GMT +1. The time now is 02:38 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.