I am running into an issue in Excel when trying to create an automated spreadsheet. I have most of the data ready to go already, but this one thing is giving me some serious trouble.
I am trying to create a data tracking file. Most data will be entered manually, in which I will have certain data to be tracked (column A) and criteria to track that data for (row 1). The data to be tracked will always be the same, but the criteria will be changed depending on the needed numbers. The same sheet will contain multiple sets of this one table to track the same data over different time periods. I would like to make entering the criteria for all tables easier, by having Excel automatically copy the contents of row 1 into the header rows of the other tables if data is present in row 1. If a cell is blank in row 1, I would like the corresponding cells in the other tables to also be blank. I thought there was a way to do that, but I can't remember how. Also, I would like to achieve this without using macros. I have been searching here and elsewhere online, but all I could find were ways to display pre-determined text or values, if a cell contains specific text, i.e. "if cell x contains apple, return TRUE in cell y" or "if cell x contains apple, sum value of cell y in cell z".
Example:
IF B1 =/= empty THEN B14=B1; IF B1=empty THEN B14=empty
Right now the only option I found was the formula =B1 in B14, but that returns a 0 if B1 does not contain anything, I would like to have B14 empty though in that case.
Is what I am trying to do at all possible, or am I remembering this wrong? Any help would be really appreciated.
Try the following formula in cell B14:
=IF(B1<>"",B14,"")
The "" in the formula represents blank while <> represents 'is not equal to'. So IF B1 'is not equal to blank', THEN return B14. Otherwise (IF B1 'is equal to blank'), return blank.
You can as well reverse the TRUE/FALSE values of the IF by using:
=IF(B1="","",B14)