IF, ISTEXT, INDIRECT formula

[kimprc]

New to forums, so bear with me, please!

These are the things I would like to accomplish with this Excel formula. #3 and #5 are not accomplished at this time. I included all goals, as when I keep trying different formulas and solve one problem, it seems to cause other goals to not be accomplished.
1. Look at another sheet's cell and check to see if the cell has text (goal is accomplished with formula).
2. If that cell does have text, insert the text in the cell with this formula (goal is accomplished with formula).
*3. If it does not have text, leave the cell blank (goal is NOT accomplished with this formula, even though I have the [value_if_false] as "").
4. When I insert or remove columns from the worksheet "Treatment attendance," I want this formula to remain exactly the same (goal is accomplished with this formula)
*5. When I copy this formula to the subsequent rows, I would like Excel to automatically change 331 to 332, etc. (goal is NOT accomplished with this formula).

[TABLE="width: 180"]
<colgroup><col width="180"></colgroup><tbody>[TR]
[TD="class: xl69, width: 180"]
=IF(ISTEXT("'TREATMENT attendance'!$C331"),INDIRECT("'TREATMENT attendance'!$C331"),"")
[/TD]
[/TR]
</tbody>[/TABLE]

Thank you in advance for your help!

 

[JoeMo]

Your formula needs some tweaks. Try this:

=IF(ISTEXT('Treatment attendance'!$C331),INDIRECT("'TREATMENT attendance'!$"&C331),"")

 

[kimprc]

Thank you for trying to help!

This formula results in a warning that there is a circular reference, and the cell value is "0" when it should be "TEST 1" (content of cell C331 on worksheet 'TREATMENT attendance'). If 'TREATMENT attendance'$C331 is empty, then the cell with this formula is empty. And, the first $C331 in the formula changes to $D331 when I add a column in front of column C in the worksheet 'TREATMENT attendance'. I tested all goals and 2 and #4 goals are not working properly with this formula.

Your formula needs some tweaks. Try this:

=IF(ISTEXT('Treatment attendance'!$C331),INDIRECT("'TREATMENT attendance'!$"&C331),"")

 

[JoeMo]

Thank you for trying to help!

This formula results in a warning that there is a circular reference, and the cell value is "0" when it should be "TEST 1" (content of cell C331 on worksheet 'TREATMENT attendance'). If 'TREATMENT attendance'$C331 is empty, then the cell with this formula is empty. And, the first $C331 in the formula changes to $D331 when I add a column in front of column C in the worksheet 'TREATMENT attendance'. I tested all goals and 2 and #4 goals are not working properly with this formula.

Well, I didn't look at your goals just the syntax of the formula. After looking at the goals, I don't think you need the INDIRECT function. Maybe:

=IF(ISTEXT('Treatment attendance'!$C331),'TREATMENT attendance'!$C331,"")

 

[kimprc]

That is the formula I originally used and thought everything was working perfectly, but then it ended up changing the formula when I inserted/deleted columns or moved information from one column to another column in the "TREATMENT attendance' sheet. This is when I began searching out a solution for this problem and came across the "indirect" function and thought that might solve the problem.

So, with the suggested formula, all goals are accomplished except for #4. The formulas end up changing when columns are inserted/deleted or information is moved from one column to another.

I did just test it again to be sure.

Thanks!

Well, I didn't look at your goals just the syntax of the formula. After looking at the goals, I don't think you need the INDIRECT function. Maybe:

=IF(ISTEXT('Treatment attendance'!$C331),'TREATMENT attendance'!$C331,"")

 

[JoeMo]

The formula should not change when you add full columns to the sheet the formula is on. It doesn't change for me.

 

[kimprc]

It changes the formula when I add columns in the source worksheet 'TREATMENT attendance'. Even though the C is an absolute with $C, it changes it to $D in the formula (this happens when I highlight column C, right click, insert - again in the source worksheet, not on the worksheet where the formula is located).

The formula should not change when you add full columns to the sheet the formula is on. It doesn't change for me.

 

[JoeMo]

It changes the formula when I add columns in the source worksheet 'TREATMENT attendance'. Even though the C is an absolute with $C, it changes it to $D in the formula (this happens when I highlight column C, right click, insert - again in the source worksheet, not on the worksheet where the formula is located).

Well that's as it should be - Excel is trying to adjust the formula to track the source sheet cell as it moves from column C to column D. If you don't want that tracking then try this on the sheet with your formula (here it's K1 and the reference to the source sheet is in I1);

Excel Workbook
	I	J	K
1	'Treatment attendance'!$C331
Sheet13