if statement

Highlighted
Contributor

i am trying to right an if statement that would cover if I put a value in G15 then cells G16-p16 would be blank.

then if I put a value in G16 then cells g15, and h16-p16 would be blank as you worked your way across the row

is the possible, i thought nesting if statements but could not get to work

5 Replies
Highlighted

I'm having a hard time understanding what you are trying to do.  Your statement doesn't seem to have a consistent pattern:

first comment is basically hide the next row: "value in G15 then cells G16-p16 would be blank.

second comment is hide previous cell and rest of this row: "then if I put a value in G16 then cells g15, and h16-p16 would be blank as you worked your way across the row"

Also, won't there be data in more than 1 cell and have circular reference trying to hide each other?

I also assume by hide you mean something like make the text color white would work?  Because you could do that via conditional formatting (but I still don't understand what the formula would be). 

Highlighted

@mtarler whAT i WOULD LIKE is my secretary be able to put a number G15 and not in H15-p15.

later that day she gets a payment and has to put it on the next row, and she has to put in H16, how ever now would like to block G16, and also H16-p16. Another words she can put only one entry per row, but it may be in different columns. 

I hope this a better explanation 

Highlighted

@wjallen14  so your examples and your spreadsheet still don't jive but I think I get what you want.

I think there are a couple of ways you can approach this depending on your specifics.

A) you can use a helper column, let's call it Transaction.  Then columns G:P are calculated by IF statements.  For example if column C is description and if it has CC in it then it should show in column H then col H would have formula like =if(search("CC",H15), [Transaction], "") and so on for each column.  Then the secretary only enters the value in 1 place and the sheet knows where to use it

B) you can use data validation to prevent entry in more than 1 of those cells. to do that highlight the range of cells you want to format (e.g. G15:P100) and you use "custom" under data entry and enter a data validation formula as =(sum($G15:$P15)=G15). (note: this formula assumes G15 is upper left corner of selected range and the columns range from G:P). Then add in the error alert tab of the data validation window add "Only 1 entry per line is allowed" or something to that effect.

 

Highlighted

@mtarler tthank you works perfect

Highlighted

@wjallen14  you're welcome and don't forget to *like* good posts and mark the solution post as *Official Answer*