Question description:
This user has given permission to use the problem statement for this
blog.
I need a formula to say when a cell in column A is blank then the adjacent cell in column O should be blank also
Solved by T. H. in 24 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
01/08/2018 - 06:44
Hello
User
01/08/2018 - 06:44
Hi
Excelchat Expert
01/08/2018 - 06:44
How are you?
User
01/08/2018 - 06:44
Good thanks
Excelchat Expert
01/08/2018 - 06:44
nice to hear that.
Excelchat Expert
01/08/2018 - 06:44
I read the description you have mentioned.
User
01/08/2018 - 06:44
ok thanks
Excelchat Expert
01/08/2018 - 06:45
so when A is blank corresponding cell in Column O should be blank
User
01/08/2018 - 06:45
Yes thats right
Excelchat Expert
01/08/2018 - 06:46
but when A has value what column O should show
User
01/08/2018 - 06:46
It shows data from another worksheet
Excelchat Expert
01/08/2018 - 06:46
ok. But to create that formula I need to know this.
Excelchat Expert
01/08/2018 - 06:46
Let me explain
Excelchat Expert
01/08/2018 - 06:47
Can you see the preview on the right?
User
01/08/2018 - 06:47
Can I not conditional format the sheet or just colum O
User
01/08/2018 - 06:47
yes i can see it
Excelchat Expert
01/08/2018 - 06:48
Now lets' say I am putting the formula for Column C
Excelchat Expert
01/08/2018 - 06:49
if you check the formula I did put
Excelchat Expert
01/08/2018 - 06:49
=if(A2="",B2,"")
Excelchat Expert
01/08/2018 - 06:49
if A2 is blank, this is the logic.
User
01/08/2018 - 06:50
ok
Excelchat Expert
01/08/2018 - 06:50
if this is true (i.e. if A2 is blank) then it will show B2, if it is false then it will show blank.
Excelchat Expert
01/08/2018 - 06:51
In our case formula will become
Excelchat Expert
01/08/2018 - 06:51
=if(A2="","",B2)
Excelchat Expert
01/08/2018 - 06:51
so if A2 is blank, then we show blank otherwise we will get B2
User
01/08/2018 - 06:52
ok
Excelchat Expert
01/08/2018 - 06:53
if you can tell me or share your file with me then I can devise the exact formula for you
User
01/08/2018 - 06:54
Column O already has IF statements in each cell that's where the data comes from so I want to be able to put a conditional format on the whole sheet if possible
Excelchat Expert
01/08/2018 - 06:54
with conditional formatting what we can do is make the text white so it will look blank.
Excelchat Expert
01/08/2018 - 06:55
but actually it will not be blank.
User
01/08/2018 - 06:55
I did think of that but that doesn't help as I then have to sort Column O in date order
User
01/08/2018 - 06:56
how can I share my file?
Excelchat Expert
01/08/2018 - 06:56
Please use the clip icon next to the chat window.
Excelchat Expert
01/08/2018 - 06:57
then if you can share the formula in Column O, I will amend the formula to account for Column A.
User
01/08/2018 - 06:59
it will not show the file i want to share
Excelchat Expert
01/08/2018 - 06:59
Ok.
Excelchat Expert
01/08/2018 - 07:00
Please share the formula in Column O
Excelchat Expert
01/08/2018 - 07:00
I will amend it
User
01/08/2018 - 07:00
=IF(Comments!O130="","",Comments!O130)
User
01/08/2018 - 07:00
comments is another worksheet
Excelchat Expert
01/08/2018 - 07:01
Ok
Excelchat Expert
01/08/2018 - 07:01
this formula is in cell O130?
User
01/08/2018 - 07:01
yes
Excelchat Expert
01/08/2018 - 07:01
Ok
User
01/08/2018 - 07:02
00/01/1900 is the date that is returned when there is no more data to show
User
01/08/2018 - 07:02
so when I sort by date order these show first
Excelchat Expert
01/08/2018 - 07:02
=if(A130="","",IF(Comments!O130="","",Comments!O130) )
Excelchat Expert
01/08/2018 - 07:03
Please use this formula
Excelchat Expert
01/08/2018 - 07:03
paste it in O 130
Excelchat Expert
01/08/2018 - 07:04
and then copy paste it from there
User
01/08/2018 - 07:05
Can that be used from O2 where the data starts?
Excelchat Expert
01/08/2018 - 07:05
Yes
User
01/08/2018 - 07:05
Great, thank you
Excelchat Expert
01/08/2018 - 07:05
Please paste it in O130 FIRST
Excelchat Expert
01/08/2018 - 07:06
AND THEN COPY O130 and paste it anywhere in Column O
User
01/08/2018 - 07:07
Brilliant I think that's done it and I've copied it down to O500 as there'll never be more than a few hundred rows of data.
Excelchat Expert
01/08/2018 - 07:07
Great!
Excelchat Expert
01/08/2018 - 07:07
Please go ahead and end the session and if you are satisfied with my services please provide good review.
User
01/08/2018 - 07:08
thank you so much
Excelchat Expert
01/08/2018 - 07:08
thank you and have a great day!
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
