Reporting Values Between Sheets/Tabs

[jjbNana]

Hi,

My formula "=IF('Sheet1'!R8="Y",'Sheet1'!A8)" reports back the value of A8 into 'Sheet2' cell A2, as intended, based on the variable in R8 (line 8 is 1st data/non-header row). However, there are merged cells from A8 all the way down to A78. The next value I want reported if true is A79. The problem is all the merged cells in-between report "FALSE." I cannot get rid of the merged cells/rows, and cannot figure out how to display/report only the true values.



Any help would be greatly appreciated.

[Catalin.B]

you are not using all the arguments of the IF function: IF(logical test;value if true;value if false)
Your formula is missing the value if false! If you omit that, excel will display false if the logical test is not true.
You can try: =IF('Sheet1'!R8="Y",'Sheet1'!A8,"")
or: =IF('Sheet1'!R8="Y",'Sheet1'!A8,0)

[jjbNana]

Thank you, very much. Obviously, I missed the forest for the trees in front of me. That worked (specifying "" to close out the full syntax), but I still have now blank spaces to deal with, vice the "FALSE" values I had before your suggestion. I knew about the last variable, I just didn't originally think I needed it. At least I'm now a step closer to where I need to be.

Now, it is converting the merged cells into now blank cells. How do I get the target sheet to auto sort the blank cells out now, to make the final product idiot-proof for my users?

[Catalin.B]

Select your range that needs to be sorted, then go to data-sort, sort by column B, sort on values (if your selection has headers, check that box) then click ok. The rows with no values will be placed at the end of the table.
If this is not what you need, then you better be more specific, maybe uploading a sample of your data it's a good idea.