How to display data from one sheet if blank or another if filled

[Krillephar]

Hi,

I need help to display data from one sheet if blank (or other value if that helps) and data from another sheet if the cell has been filled.

I hope it make sense : )

Thank you.

Kind regards
Krillephar

 

[Special-K99]

"...display data from one sheet if blank...and data from another sheet if the cell has been filled."


in A1

=IF(B1="",Sheet1!C1,Sheet2!D1)

Looks at B1, if it's blank displays data from one sheet (Sheet1!C1) or data from another sheet (Sheet2!D1) if it has been filled (not blank)

Adjust the formula as per your requirements.

 

[Krillephar]

"...display data from one sheet if blank...and data from another sheet if the cell has been filled."

in A1

=IF(B1="",Sheet1!C1,Sheet2!D1)

Looks at B1, if it's blank displays data from one sheet (Sheet1!C1) or data from another sheet (Sheet2!D1) if it has been filled (not blank)

Adjust the formula as per your requirements.

Amazing, thank you so much for the quick reply.

Any way it can be done like this: For the example, my main Sheet for collecting data is Sheet1 A1. To get the data I need, I use this simple line in Sheet1 A1: =('Sheet2'!A1). Now, if there is no data in Sheet2 A1, I need it to go to Sheet3 A1.

Cant get the above formular to work this way.

Kind regards
Krillephar

 

[Special-K99]

in Sheet1!A1
=IF(Sheet2!A1<>"",Sheet2!A1,Sheet3!A1)

 

[Krillephar]

You are absolutely amazing :D Thank you. Would you know of any way the color could be changed based on what sheet the formula presents data from? E.g. all data, regardless of value or cell, from Sheet3 needs to be red?

 

[Special-K99]

Use Conditional Formatting

Select the range that you want the formatting to apply to, so not just the cell Sheet1!A1 the range, so maybe Sheet1!A1:A1000

Conditional Formatting
New Rule
Use a formula to determine...

Just use the same logic in the formula but put it like this
You need to specify formula slightly differently in Conditional Formatting, you only specify when the result of the formula is true, just when it is true.
So it should be

=(Sheet2!A1="")

since if Sheet2!A1="" then the data is retrieved from Sheet3 as per the original formula

Format as red

Data retrieved from Sheet 2 will not change the color of the cells.
If you want to highlight data from Sheet 2 specify this additional formula

=(Sheet2!A1<>"")

and then format in a color thats not red.

 

[Krillephar]

...sorry for the wait : )

I keep getting everything in red or everything in black. For some reason it wont distinct between where the data is coming from.

 

[Special-K99]

So what are the formulas you entered in Condtional Formatting?

 

[Krillephar]

It seems to work on a single cell, but not if I try and use it on a range of cells or on the sheet.

Entered in Condtional Formatting: =('Exsisting & Allocation'!$B$2="")





<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px 'Lucida Grande'; color: #000000}</style>

 

[Special-K99]

Remove all $ from the formula
You dont see any $ in my formula do you?