Excel Formula help if cell is blank

[Beh162]

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

Please Login or Register  to view this content.

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

[Pete_UK]

Instead of zero, make it "" like this:

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

Hope this helps.

Pete

[Beh162]

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

Please Login or Register  to view this content.

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?

[Pete_UK]

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

[Beh162]

Worked perfectly!

[Beh162]

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:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

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

[FDibbins]

To reference sheet names that change as you copy the formula down, you need to use the INDIRECT() function.
upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[Beh162]

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?

[The_Snook]

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

[Beh162]

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

[The_Snook]

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

[Beh162]

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_Snook]

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.

[Beh162]

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

[The_Snook]

Hi Beh162,

Without seeing the actual workbook it's difficult to diagnose. Given that it's an invalid cell reference error I'm assuming that your tab names aren't structured like 'Week1', 'Week2' etc? The INDIRECT formula is converting INDIRECT("Week"&ROW()-2 into the tab name Week1 (if the formula is held in the third row). The formula takes the text "Week" and adds the row number on the end minus 2, e.g. INDIRECT("Week"&ROW()-2 converts to "Week"&3-2 ---> "Week1".

Hope this helps.

Snook

[Beh162]

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

[The_Snook]

Happy days, good to hear.

[Beh162]

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?

[Pete_UK]

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