Automatic Answer

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Zygy - 05 Oct 2006 08:22 GMT

Is there a way of permanently arranging in col. H that the result of the
amount in col. D plus amount in col.E less the amount in col.F is
automatically shown once the entries in cols. D, E & F have been made. I am
tired of using copy and paste system!

Reply to this Message

Max - 05 Oct 2006 11:08 GMT

One way ..
Put in H2:
=IF(COUNTBLANK(D2:F2)<>0,"",SUM(D2:E2)-F2)
Copy H2 down to cover the max expected extent of data, say to H200 ?
Col H will appear "blank" until entries in cols D, E & F have been made

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Is there a way of permanently arranging in col. H that the result of the
> amount in col. D plus amount in col.E less the amount in col.F is
> automatically shown once the entries in cols. D, E & F have been made. I
> am tired of using copy and paste system!

Reply to this Message

Max - 05 Oct 2006 12:16 GMT

> Copy H2 down to cover the max expected extent of data, say to H200 ?

To copy down, just drag the fill handle (the solid black square at the
bottom right corner of H2) down to H200

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message

Zygy - 06 Oct 2006 08:54 GMT

Thank you for the reply. When I entered your formula in H2 and clicked OK, I
was presented with another Window titled "Function Arguments" and any of the
alternatives offered in "Help on this Function" appeared inappropriate for
my cell.

How do I proceed after entering your formula in H2, to get the answer in
pund sterling, having enter in pund sterling cells D2,E2 & F2? Thank you.

> One way ..
> Put in H2:
[quoted text clipped - 5 lines]
>> automatically shown once the entries in cols. D, E & F have been made. I
>> am tired of using copy and paste system!

Reply to this Message

Gord Dibben - 06 Oct 2006 23:38 GMT

When you say you "entered the formula in H2 and clicked OK" did you mean you hit
the ENTER key which is what you should do?

Otherwise, what did you click OK on?

After entering Max's formula in H2, copy it down H a bunch of cells.

Start by plugging numbers into D2,E2 and F2 followed by numbers into other cells
down D, E and F columns.

Gord Dibben MS Excel MVP

>Thank you for the reply. When I entered your formula in H2 and clicked OK, I
>was presented with another Window titled "Function Arguments" and any of the
[quoted text clipped - 12 lines]
>>> automatically shown once the entries in cols. D, E & F have been made. I
>>> am tired of using copy and paste system!

Reply to this Message

Zygy - 07 Oct 2006 09:01 GMT

To answer your question first- The Function Arguments Window, which I get to
enter the Formula has a OK button on it, which I always used with perfect
results. I tried again and I entered the formula copied and pasted below,
but now I get a notice stating that "your formula is missing Parenthesis..."
=SUM IF(COUNTBLANK(D:F2)<>0,"",SUM(D2:E2)-F2)
What is your comment on that?

> When you say you "entered the formula in H2 and clicked OK" did you mean
> you hit
[quoted text clipped - 30 lines]
>>>> I
>>>> am tired of using copy and paste system!

Reply to this Message

Gord Dibben - 07 Oct 2006 17:49 GMT

These are my comments.............................

To start with, I would just copy Max's formula into a cell without bothering
with the Function Wizard.

Secondly, I don't know how you got the extra "SUM" in Max's formula.

If you copy directly from the post, there is less chance of making a typo, which
you seem to have done during your journey to the Function Wizard.

=IF(COUNTBLANK(D2:F2)<>0,"",SUM(D2:E2)-F2) as posted by Max.

Gord

>To answer your question first- The Function Arguments Window, which I get to
>enter the Formula has a OK button on it, which I always used with perfect
[quoted text clipped - 36 lines]
>>>>> I
>>>>> am tired of using copy and paste system!

Reply to this Message

Max - 09 Oct 2006 02:41 GMT

Appreciate the help, Gord. Thanks!

Zygy: Hope you've got it to work in col H since

As for your side Q in your earlier reply:

> How do I proceed after entering your formula in H2,
> to get the answer in pound sterling,
> having enter in pound sterling cells D2,E2 & F2?

Just format H2 as currency, Symbol: £ English (United Kingdom)
before you copy H2 down. That should do it nicely.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message

Zygy - 10 Oct 2006 08:11 GMT

Thank you both for the help! I have got it now.

> Appreciate the help, Gord. Thanks!
>
[quoted text clipped - 7 lines]
> Just format H2 as currency, Symbol: £ English (United Kingdom)
> before you copy H2 down. That should do it nicely.

Reply to this Message

Max - 10 Oct 2006 08:36 GMT

Glad to hear that !
Thanks for feeding back ..

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Thank you both for the help! I have got it now.

Reply to this Message