Excel Formula help if cell is blank

I need a formula that will go in A2 and it will say if A1 has a 1 in it, then leave A2 blank. If A1 is blank then put a 1 in A2

I was using this in A2

=IF(A1=1, 0,1)

But I don't want it to put a 0 but just leave it blank

Instead of zero, make it "" like this:

=IF(A1=1,"",1)

Hope this helps.

Pete

Originally Posted by Pete_UK

Instead of zero, make it "" like this:

=IF(A1=1,"",1)

Hope this helps.

Pete

I have a weird question is there a way to wrap an IF into this formula to hold off the results until A1's formula actually runs? Basically I autofilled the formula down and it's putting all 1's down the column because all of A's column is technically blank because A's formula hasn't happened yet. Future weeks data isn't keyed in yet. Does that make sense?

You can do it like this:

=IF(OR(A1="",A1=1),"",1)

or like this:

=IF(A1="","",IF(A1=1,"",1))

then copy down.

Hope this helps.

Pete

Yup that did it! I have another question long the same line. This is a formula I have in cell S3

IF(R3+P3=O3,"Push", IF(R3+P3>O3,Q3,N3))

Before the data is keyed into O3 & R3 I want S3 to be blank and not have results, but only calculate the formula when there is values in 03 & R3. Is that possible?

You can do that like this:

=IF(OR(O3="",R3=""),"",IF(R3+P3=O3,"Push", IF(R3+P3>O3,Q3,N3)))

(changes in red - don't forget the close bracket at the end).

Hope this helps.

Pete

Lastly, I have a Main tab and then I have a few worksheets attached labeled Week 1 - Week 10. In this main tab it has 10 rows and it pulls data from each of the Week tabs. Is there a way to copy a formula easily so it moves from tab to tab. The data is in the same exact spot on each sheet.

For example:

IF(COUNTIF(WEEK2 NEW::TEST::N24:N39,"Bears")=0,"",COUNTIF(WEEK2 NEW::TEST::N24:N39,"Bears"))

IF(COUNTIF(Week 3 NEW::TEST::$N$24:$N$39,"Bears")=0,"",COUNTIF(Week 3 NEW::TEST::$N$24:$N$39,"Bears"))

It's the same exact formula just each week goes to it's Week # tab

Worked perfectly!

Okay I've attached the sample book. You can see the two formula's I've manually keyed in B3 & B4. There are two things I'd like to be able to do

1. Drag the formula down and have it move to Week 3, Week4, Week5. Right now I've been manually referencing it to the new tab.
2. When I copy the formula to D3 I have to also manually remove the team that was typed in B3 and type in the team associated with D3. Is there an easier fix to that or just manual?

Hi Beh162,

Copy this into cell B3:

=IF(COUNTIF(INDIRECT("Week"&ROW()-2&"!$A$2:$A$10"),B$1)=0,"",COUNTIF(INDIRECT("Week"&ROW()-2&"!$A$2:$A$10"),B$1))

Regards,

Snook

Hey Snook,

That formula worked great. Now when I translate that to my actual sheet do I need to make sure the Master sheet is directly followed by all the week 1-10 tabsa

Hi Beh162,

The order of the tabs won't matter but you'll need to ensure that they exist (week 1-10) otherwise you'll return an error message.

Snook

I'll actually have 17 total weeks, I'm assuming I will need to alter the formula to that? Just not sure where in the formula. I will be testing it on my actual sheet later today.

The formula will work for as many weeks as you need. You just need to drag it down the necessary number of rows. Test it out and if you have any problems just give me a shout.

For some reason when I move this to my actual sheet i'm getting a invalid cell reference error. I'm typing the first formula into B4 which is week #1. When I trace the error it points to the cell that says the team name. "C2"

=IF(COUNTIF(INDIRECT("Week"&ROW()-2&"!$N$24:$N$39"),C$2)=0,"",COUNTIF(INDIRECT("Week"&ROW()-2&"!$N$24:$N$39"),C$2))

I got it working now. Looks good, works great!

Happy days, good to hear.