• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Offset formula returns 0, can i just have it as blank cell

C

chintu49

New Member
Hi Chandoo


I keep reading your blogs and also download useful excel formats for reporting and they are really amazing.


I am using a formula =OFFSET('Requirement Data'!E6,Calculation!$D$5,0,1,1), it returns the value "0" if the cell is blank in the Requirement Data tab, could you please suggest some function in between the above formula so that it will ignore "0" and will only return blank cell?


thanks


rgds


Chandresh
 
Can you just change the format of cell to repress the 0 value? Something like:

#;-#;;@

would work.


Otherwise, could do:

IF(OFFSET('Requirement Data'!E6,Calculation!$D$5,0,1,1)=0,"",OFFSET('Requirement Data'!E6,Calculation!$D$5,0,1,1))
 
Thanks Luke, it works i mean I tried #;-#;;@, only problem is when I want to display the actual value "0" from the Requirement data tab, it will display nothing, any workaround for this?


thanks appreciate your help
 
Also is there a way that i can display the same format that is font size, etc in the summary tab, i.e where i am using the offset formula?
 
If there is no further calculation with this output, you can use T() function. This will give blank if cell is a number, so if add &"" this will converted to text. If you have calculations ie, SUM AVERAGE etc.. with this data, this output will not work, because output is in TEXT FORMAT.


=T(OFFSET('Requirement Data'!E6,Calculation!$D$5,0,1,1))


OFFSET is a volatile function, i would recommend to use INDEX, which will give you the same result.


=INDEX('Requirement Data'!E6:E65536,Calculation!$D$5+1)
 
Thanks Haseeb for your response, i tried Index formula and it works for me, for now i will show blanks for the numbers zero in other tab.


Also is there a way that i can display the same formats, color, fonts from the other tab into the tab where i am using your index formula?
 
As far as I know, there are no formula to return the format. ie Color, Font etc. This will require VBA. I am zero in VBA :) Hope some one will help you.
 
If you just want to suppress a null value, could do:

=IF(OFFSET('Requirement Data'!E6,Calculation!$D$5,0,1,1)="","",OFFSET('Requirement Data'!E6,Calculation!$D$5,0,1,1))


As Haseeb said, there is no way via formulas to transfer formats.
 
You have to use a text box!

Format the cell anyway you need. Then make a text box. Click the text box. Go up to the formula bar. type =the cell u formatted. BAM!
 
you may try this to return a blank instead of 0

=if(ISBLANK('Requirement Data'!E6),"",OFFSET('Requirement Data'!E6,Calculation!$D$5,0,1,1

))
 
You must log in or register to reply here.
Back
Top