Apr 30 2020 08:13 PM
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
May 01 2020 06:53 AM
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).
May 01 2020 07:07 AM
@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
May 01 2020 08:12 AM
@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.
May 02 2020 03:27 PM
@wjallen14 you're welcome and don't forget to *like* good posts and mark the solution post as *Official Answer*