Creating an "F" Statement

[Sher]

Hello -

I need to create an "F" Statement that on one sheet I have spend plan and on
the another sheet I have an ad hoc sheet. I would like a statement that
states if a field is blank on ad hoc than it is blank on spend plan. Right
now when I delete a field on the ad hoc it puts Jan 1900 on the spend plan
field.

Any help would be appreciated.

Thank you,

 

[Max]

Use something like this in say, Sheet1's A2:
=IF(Sheet2!B2="","",Sheet2!B2)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,200 Files:354 Subscribers:53
xdemechanik

 

[Gary''s Student]

=IF(Sheet2!A1="","",Sheet2!A1)

 

[Sher]

Hello Max -

I typed in the "IF" Statement but for some reason the statement just showed
up on both sheets; so instead of making it blank it just place the statement
there.

 

[Max]

Sounds like the cell that you placed the formula in was earlier formatted as
TEXT (unknown to you). Try formatting* that formula cell to either General or
Number first, then click inside the formula bar and press ENTER to re-confirm
the formula.
*via clicking Format > Cells
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,200 Files:354 Subscribers:53
xdemechanik

 

[Sher]

Good Morning Max -

Made the change formula cell to General and still no change. Not sure why
this isn't working.

 

[Max]

Aha, as mentioned in my response, you must:
" ... then click inside the formula bar
and press ENTER to re-confirm the formula."

Did you do the above?

Formatting alone will not suffice. You must re-confirm the formula to
re-trigger it
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,200 Files:354 Subscribers:53
xdemechanik

 

[David Biddulph]

Perhaps you had the cell format set to Text? Format the cell as General &
re-enter the formula.

 

[Sher]

Yes, I did enter to re-confirm the formula and nothing has changed.

 

[Max]

Copy your actual formula used (copy from inside the formula bar),
then paste it here in your reply
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,200 Files:354 Subscribers:53
xdemechanik

 

[David Biddulph]

In which case, try Tools/ Options/ View: uncheck Formulas.
Or Control ` (perhaps the key to the left of 1) to toggle this function.

 

[Sher]

Here is the formula:

Spend Plan A3: =IF(AD HOC ALL!A3="","", AD HOC ALL!A3)

 

[Sher]

David -

I tried what you said also and it still didn't work

 

[David Biddulph]

But what about my later message:
"In which case, try Tools/ Options/ View: uncheck Formulas.
Or Control ` (perhaps the key to the left of 1) to toggle this function."

If that didn't work, you need to respond to Max's last message:
"Copy your actual formula used (copy from inside the formula bar), then
paste it here in your reply"

Also, check that you haven't got spare spaces (or other non-printing
characters) in what you think is a formula but which Excel is presumably
treating as text.

 

[David Biddulph]

Yes, that's text, hence your problem. If it were a formula, the sheet name
would have had single quote marks around it. You can only omit the quote
marks if the sheet name has no spaces in it. The easiest way to insert such
a reference into a formula is to start typing the formula with the =IF( ,
and then go to the sheet concerned and click on the cell concerned to get
Excel to insert that cell's reference in your formula.

When you had hit enter to reconfirm your "formula", I would have expected it
to go to the "Update Values" dialogue, looking for the workbook from which
the link came.

 

[Max]

In your sheet: Spend Plan,

Put this slightly amended* into cell A3:
=IF('AD HOC ALL'!A3="","",'AD HOC ALL'!A3)

*added the apostrophes
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,200 Files:354 Subscribers:53
xdemechanik