Rocksolid Light

Welcome to novaBBS (click a section below)

mail  files  register  newsreader  groups  login

Message-ID:  

The study of non-linear physics is like the study of non-elephant biology.

aus+uk / uk.comp.sys.mac / OTish: Autofill rows in Mac Excel

SubjectAuthor
* OTish: Autofill rows in Mac ExcelRJH
`* Re: OTish: Autofill rows in Mac ExcelBruce Horrocks
 `* Re: OTish: Autofill rows in Mac ExcelRJH
  +* Re: OTish: Autofill rows in Mac ExcelTimS
  |`* Re: OTish: Autofill rows in Mac ExcelBruce Horrocks
  | `- Re: OTish: Autofill rows in Mac ExcelRJH
  `* Re: OTish: Autofill rows in Mac ExcelLiz Tuddenham
   `- Re: OTish: Autofill rows in Mac ExcelLiz Tuddenham

1
OTish: Autofill rows in Mac Excel

<uhaqtr$mo4o$1@dont-email.me>

  copy mid

https://www.novabbs.com/aus+uk/article-flat.php?id=18354&group=uk.comp.sys.mac#18354

  copy link   Newsgroups: uk.comp.sys.mac
Path: i2pn2.org!i2pn.org!eternal-september.org!news.eternal-september.org!.POSTED!not-for-mail
From: patchmo...@gmx.com (RJH)
Newsgroups: uk.comp.sys.mac
Subject: OTish: Autofill rows in Mac Excel
Date: Wed, 25 Oct 2023 10:33:31 -0000 (UTC)
Organization: A noiseless patient Spider
Lines: 13
Message-ID: <uhaqtr$mo4o$1@dont-email.me>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=fixed
Content-Transfer-Encoding: 8bit
Injection-Date: Wed, 25 Oct 2023 10:33:31 -0000 (UTC)
Injection-Info: dont-email.me; posting-host="f0a2786001b8693bda83bda0041d5b0b";
logging-data="745624"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX18Exz6cWqe0MjbxG3lDH7ZY"
User-Agent: Usenapp for MacOS
Cancel-Lock: sha1:lfw5qWh9C0CYpx8ZSar8l1keD9g=
X-Usenapp: v1.27.2/l - Full License
X-Face: vQnbv9.hE?8i]$Ze]OcTpb,obA[3MtUngaZO(ISFlziUk#Sqro&Lzf|1w5j@Wx_[Ju319$< Ka>(F68r$z?yD[m[!^RH/FdWU9.@APw!U9~XbK^jVZPS5Q|A`\oD`O7W/1N{DHKvYsvcMNH009wq\k Aha&{ID11Ks^}4KwoJ#
 by: RJH - Wed, 25 Oct 2023 10:33 UTC

I've set up a spreadsheet in Excel for Mac (latest version, 365, v.16.78) and
I'd like it to autofill a row, informed by a set of formulae in the rows
above, without me having to do anything other than fill in the basic data -
date, and values for gas and electricity. Screenshot here - it's the area
shaded grey I'd like autofilled.

https://www.icloud.com/sharedalbum/#B0zJr1PPdJ0ayTG

Of course, I could just highlight the row above and drag down by the handle.
But I'm intrigued to figure out how/if this could be automated.

--
Cheers, Rob, Sheffield UK

Re: OTish: Autofill rows in Mac Excel

<ad4705c0-6f0c-4f09-868d-63c280baf75d@scorecrow.com>

  copy mid

https://www.novabbs.com/aus+uk/article-flat.php?id=18356&group=uk.comp.sys.mac#18356

  copy link   Newsgroups: uk.comp.sys.mac
Path: i2pn2.org!i2pn.org!weretis.net!feeder8.news.weretis.net!fu-berlin.de!uni-berlin.de!individual.net!not-for-mail
From: 07....@scorecrow.com (Bruce Horrocks)
Newsgroups: uk.comp.sys.mac
Subject: Re: OTish: Autofill rows in Mac Excel
Date: Wed, 25 Oct 2023 13:29:08 +0100
Lines: 24
Message-ID: <ad4705c0-6f0c-4f09-868d-63c280baf75d@scorecrow.com>
References: <uhaqtr$mo4o$1@dont-email.me>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
X-Trace: individual.net egeIIJ+WZ03sOPI/EO4xNAHnqwQv8FTbtY11UWhOvC3Ve5+tyt
Cancel-Lock: sha1:c8YmxzvdpJr33kSKD8GnPqjVkNo= sha256:d7GW9RrzqKgUum+GO39vshZBZberjNwQ1Lls6W6K4Ac=
User-Agent: Mozilla Thunderbird
Content-Language: en-GB
In-Reply-To: <uhaqtr$mo4o$1@dont-email.me>
 by: Bruce Horrocks - Wed, 25 Oct 2023 12:29 UTC

On 25/10/2023 11:33, RJH wrote:
> I've set up a spreadsheet in Excel for Mac (latest version, 365, v.16.78) and
> I'd like it to autofill a row, informed by a set of formulae in the rows
> above, without me having to do anything other than fill in the basic data -
> date, and values for gas and electricity. Screenshot here - it's the area
> shaded grey I'd like autofilled.
>
> https://www.icloud.com/sharedalbum/#B0zJr1PPdJ0ayTG
>
> Of course, I could just highlight the row above and drag down by the handle.
> But I'm intrigued to figure out how/if this could be automated.

What do you mean by autofilled? 1) Formula are added; 2) values are
added (if so where from?); 3) Values copied from above but then ready
for you to alter?

2 & 3 require the use of a macro. 1 could be done by pre-filling with
formulae that leave the cell blank until the date column (say) is added
and then they reveal their answers.

--
Bruce Horrocks
Surrey, England

Re: OTish: Autofill rows in Mac Excel

<uhb4j0$p7n1$1@dont-email.me>

  copy mid

https://www.novabbs.com/aus+uk/article-flat.php?id=18357&group=uk.comp.sys.mac#18357

  copy link   Newsgroups: uk.comp.sys.mac
Path: i2pn2.org!i2pn.org!eternal-september.org!news.eternal-september.org!.POSTED!not-for-mail
From: patchmo...@gmx.com (RJH)
Newsgroups: uk.comp.sys.mac
Subject: Re: OTish: Autofill rows in Mac Excel
Date: Wed, 25 Oct 2023 13:18:24 -0000 (UTC)
Organization: A noiseless patient Spider
Lines: 51
Message-ID: <uhb4j0$p7n1$1@dont-email.me>
References: <uhaqtr$mo4o$1@dont-email.me> <ad4705c0-6f0c-4f09-868d-63c280baf75d@scorecrow.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=fixed
Content-Transfer-Encoding: 8bit
Injection-Date: Wed, 25 Oct 2023 13:18:24 -0000 (UTC)
Injection-Info: dont-email.me; posting-host="f0a2786001b8693bda83bda0041d5b0b";
logging-data="827105"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX18h9Lv/iiOiheFj90IEbnLo"
User-Agent: Usenapp for MacOS
Cancel-Lock: sha1:f9t2ybNIBJmEMI2iJ/4mArzWNKk=
X-Face: vQnbv9.hE?8i]$Ze]OcTpb,obA[3MtUngaZO(ISFlziUk#Sqro&Lzf|1w5j@Wx_[Ju319$< Ka>(F68r$z?yD[m[!^RH/FdWU9.@APw!U9~XbK^jVZPS5Q|A`\oD`O7W/1N{DHKvYsvcMNH009wq\k Aha&{ID11Ks^}4KwoJ#
X-Usenapp: v1.27.2/l - Full License
 by: RJH - Wed, 25 Oct 2023 13:18 UTC

On 25 Oct 2023 at 13:29:08 BST, Bruce Horrocks wrote:

> On 25/10/2023 11:33, RJH wrote:
>> I've set up a spreadsheet in Excel for Mac (latest version, 365, v.16.78) and
>> I'd like it to autofill a row, informed by a set of formulae in the rows
>> above, without me having to do anything other than fill in the basic data -
>> date, and values for gas and electricity. Screenshot here - it's the area
>> shaded grey I'd like autofilled.
>>
>> https://www.icloud.com/sharedalbum/#B0zJr1PPdJ0ayTG
>>
>> Of course, I could just highlight the row above and drag down by the handle.
>> But I'm intrigued to figure out how/if this could be automated.
>
> What do you mean by autofilled? 1) Formula are added;

The formula is in the cell directly above the one waiting to be filled. For
example, cell D15 is =A15-A14. D16 would be A16-A15 - which happens if I
handle-drag D15 to D16. But I'd like that to happen automatically . . .

> 2) values are
> added (if so where from?);

I have to manually enter the basic data - date and meter readings to columns
A, B and C.

> 3) Values copied from above but then ready
> for you to alter?
>

In the example, I don't want to alter anything - I'd just like cells D16:N16
to automatically fill once I've entered data in columns A, B and C.

> 2 & 3 require the use of a macro.

Ah, OK. I did have a quick try but couldn't work out how Excel 'knows' when I
start to fill in more rows from future meter readings with basic data.

> 1 could be done by pre-filling with
> formulae that leave the cell blank until the date column (say) is added
> and then they reveal their answers.

Yes, that works, I could populate the columns with the formula. But that does
fill the yet to be completed in the future cells with error message #DIV/0!.
Looks a bit messy!

I'll have a fiddle with macros - thanks for taking the time to look/comment.

--
Cheers, Rob, Sheffield UK

Re: OTish: Autofill rows in Mac Excel

<kpsjkoFesleU1@mid.individual.net>

  copy mid

https://www.novabbs.com/aus+uk/article-flat.php?id=18358&group=uk.comp.sys.mac#18358

  copy link   Newsgroups: uk.comp.sys.mac
Path: i2pn2.org!i2pn.org!news.neodome.net!fu-berlin.de!uni-berlin.de!individual.net!not-for-mail
From: tim...@streater.me.uk (TimS)
Newsgroups: uk.comp.sys.mac
Subject: Re: OTish: Autofill rows in Mac Excel
Date: 25 Oct 2023 13:31:04 GMT
Lines: 11
Message-ID: <kpsjkoFesleU1@mid.individual.net>
References: <uhaqtr$mo4o$1@dont-email.me> <ad4705c0-6f0c-4f09-868d-63c280baf75d@scorecrow.com> <uhb4j0$p7n1$1@dont-email.me>
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=fixed
Content-Transfer-Encoding: 8bit
X-Trace: individual.net MVk5oCyGMMsFVDQ6UUqDHwFoCTH8E8/mK4mOIJzaBgwkxFmsXk
Cancel-Lock: sha1:0plqeE9t/pFIXf8kPn0j6OBd18Y= sha256:sgep7Id/Vl59iEsULXRlsKbwqsLdLu7XL5PJc9/0BR0=
X-Face: "M;\x&0=#cxDW4-*uL~{5d@+P7KmbB:]::l[1_h]^tu#H9yQ#.X4kSbDi;atp9otO=_G|Z2 t<Cay~&S1ru:A{I$SdbF$KS43iuPUwIh,8X"%3L;9=T~]BLW=IKvZ|/}D@Rml{4D#!Uc)|mu`34(e~ x[(n[\3.'{ChZ!"cF'!%M)iM0u~wQ"%,JC}JOhtI;:<EF5?fcf<^2T,{u.&U|?`X%B_eD##Y=ZdC2< Mq_b}MHzYQg8x-N,4)~`n*K*k?No"WVa3]]dntN(76o.nd1`;l}[-O<wJB{MQNv=H^6M`>CL2oMg$~ oW5:YPiBg[-rf40JdIu#K4A+M2gSW<LK5!!SEt:%K0z&:[N:7
User-Agent: Usenapp for MacOS
X-Usenapp: v1.27.1/l - Full License
 by: TimS - Wed, 25 Oct 2023 13:31 UTC

On 25 Oct 2023 at 14:18:24 BST, "RJH" <patchmoney@gmx.com> wrote:

> Yes, that works, I could populate the columns with the formula. But that does
> fill the yet to be completed in the future cells with error message #DIV/0!.
> Looks a bit messy!

So have the formula check the value, if it's null then have the formula do
nothing.

--
Tim

Re: OTish: Autofill rows in Mac Excel

<dfcfd268-6187-42d7-992a-a07237943c54@scorecrow.com>

  copy mid

https://www.novabbs.com/aus+uk/article-flat.php?id=18360&group=uk.comp.sys.mac#18360

  copy link   Newsgroups: uk.comp.sys.mac
Path: i2pn2.org!rocksolid2!news.neodome.net!fu-berlin.de!uni-berlin.de!individual.net!not-for-mail
From: 07....@scorecrow.com (Bruce Horrocks)
Newsgroups: uk.comp.sys.mac
Subject: Re: OTish: Autofill rows in Mac Excel
Date: Wed, 25 Oct 2023 15:01:10 +0100
Lines: 18
Message-ID: <dfcfd268-6187-42d7-992a-a07237943c54@scorecrow.com>
References: <uhaqtr$mo4o$1@dont-email.me>
<ad4705c0-6f0c-4f09-868d-63c280baf75d@scorecrow.com>
<uhb4j0$p7n1$1@dont-email.me> <kpsjkoFesleU1@mid.individual.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
X-Trace: individual.net T68U+tpSogpCLm41wnljngkeIFp28aO2I7PDQqWigmgVP9pnH6
Cancel-Lock: sha1:L7wrAdb7LqvT+r83Y3jAbEkCZ7A= sha256:aHo+ILK1TL3GXwkqSmK2wCclsMd5N5JcQD+QAcrGyv0=
User-Agent: Mozilla Thunderbird
Content-Language: en-GB
In-Reply-To: <kpsjkoFesleU1@mid.individual.net>
 by: Bruce Horrocks - Wed, 25 Oct 2023 14:01 UTC

On 25/10/2023 14:31, TimS wrote:
> On 25 Oct 2023 at 14:18:24 BST, "RJH" <patchmoney@gmx.com> wrote:
>
>> Yes, that works, I could populate the columns with the formula. But that does
>> fill the yet to be completed in the future cells with error message #DIV/0!.
>> Looks a bit messy!
>
> So have the formula check the value, if it's null then have the formula do
> nothing.

Yep.

There's an IFERROR() function e.g. =IFERROR(1/0,"Ooops")

--
Bruce Horrocks
Surrey, England

Re: OTish: Autofill rows in Mac Excel

<1qj5zbf.1fza599hm140iN%liz@poppyrecords.invalid.invalid>

  copy mid

https://www.novabbs.com/aus+uk/article-flat.php?id=18365&group=uk.comp.sys.mac#18365

  copy link   Newsgroups: uk.comp.sys.mac
Path: i2pn2.org!i2pn.org!usenet.goja.nl.eu.org!3.eu.feeder.erje.net!feeder.erje.net!news.szaf.org!fu-berlin.de!uni-berlin.de!individual.net!not-for-mail
From: liz...@poppyrecords.invalid.invalid (Liz Tuddenham)
Newsgroups: uk.comp.sys.mac
Subject: Re: OTish: Autofill rows in Mac Excel
Date: Wed, 25 Oct 2023 16:21:36 +0100
Organization: Poppy Records
Lines: 40
Message-ID: <1qj5zbf.1fza599hm140iN%liz@poppyrecords.invalid.invalid>
References: <uhaqtr$mo4o$1@dont-email.me> <ad4705c0-6f0c-4f09-868d-63c280baf75d@scorecrow.com> <uhb4j0$p7n1$1@dont-email.me>
X-Trace: individual.net 4I4rNiLcC4ho6C+CwytYRQ8EG8ORrxLoGvjBj6QIn1ccq1OuGw
X-Orig-Path: liz
Cancel-Lock: sha1:G7iQAxAVsBk6T/zcD2Pa+koH8B0= sha256:0PbWAAQx1D1YElmsR6SinnIlyI4aLVVWSfD/jpGgZ0k=
User-Agent: MacSOUP/2.4.6
 by: Liz Tuddenham - Wed, 25 Oct 2023 15:21 UTC

RJH <patchmoney@gmx.com> wrote:

> On 25 Oct 2023 at 13:29:08 BST, Bruce Horrocks wrote:

[...]
> Yes, that works, I could populate the columns with the formula. But that does
> fill the yet to be completed in the future cells with error message #DIV/0!.
> Looks a bit messy!

In ClarisWorks Spreadsheet the cell contents below leaves a blank cell
if the input cell is empty:

=IF('cell'=0;"";'formula')

Where 'cell' is the position of the input cell and 'formula'; is the
formula that give the *DIV/0! message. Thus if the input cell is empty,
the output will be an empty string and the formula won't be calculated.

In plain English it reads: If the contents of the cell is 0 the output
becomes a blank, otherwise it becomes the result of the formula.

If there is more than one input that could give rise to an error, the
test needs an OR statement thus:

=IF(OR(A11="";B11="";C11="");"";'formula')

Where A11, B11, C11 are input cells.

This works in CW4 Spreadsheet and I expect something very similar (if
not identical) will work in Excel, because they both came from the same
ancestor.

--
~ Liz Tuddenham ~
(Remove the ".invalid"s and add ".co.uk" to reply)
www.poppyrecords.co.uk

Re: OTish: Autofill rows in Mac Excel

<uhbnpt$uogm$1@dont-email.me>

  copy mid

https://www.novabbs.com/aus+uk/article-flat.php?id=18366&group=uk.comp.sys.mac#18366

  copy link   Newsgroups: uk.comp.sys.mac
Path: i2pn2.org!i2pn.org!eternal-september.org!news.eternal-september.org!.POSTED!not-for-mail
From: patchmo...@gmx.com (RJH)
Newsgroups: uk.comp.sys.mac
Subject: Re: OTish: Autofill rows in Mac Excel
Date: Wed, 25 Oct 2023 18:46:21 -0000 (UTC)
Organization: A noiseless patient Spider
Lines: 24
Message-ID: <uhbnpt$uogm$1@dont-email.me>
References: <uhaqtr$mo4o$1@dont-email.me> <ad4705c0-6f0c-4f09-868d-63c280baf75d@scorecrow.com> <uhb4j0$p7n1$1@dont-email.me> <kpsjkoFesleU1@mid.individual.net> <dfcfd268-6187-42d7-992a-a07237943c54@scorecrow.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=fixed
Content-Transfer-Encoding: 8bit
Injection-Date: Wed, 25 Oct 2023 18:46:21 -0000 (UTC)
Injection-Info: dont-email.me; posting-host="f0a2786001b8693bda83bda0041d5b0b";
logging-data="1008150"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX19JPy7uInakIykpJFFO7YJ2"
User-Agent: Usenapp for MacOS
Cancel-Lock: sha1:+11j8ARV2eHUublZEJfrXuOfPv0=
X-Face: vQnbv9.hE?8i]$Ze]OcTpb,obA[3MtUngaZO(ISFlziUk#Sqro&Lzf|1w5j@Wx_[Ju319$< Ka>(F68r$z?yD[m[!^RH/FdWU9.@APw!U9~XbK^jVZPS5Q|A`\oD`O7W/1N{DHKvYsvcMNH009wq\k Aha&{ID11Ks^}4KwoJ#
X-Usenapp: v1.27.2/l - Full License
 by: RJH - Wed, 25 Oct 2023 18:46 UTC

On 25 Oct 2023 at 15:01:10 BST, Bruce Horrocks wrote:

> On 25/10/2023 14:31, TimS wrote:
>> On 25 Oct 2023 at 14:18:24 BST, "RJH" <patchmoney@gmx.com> wrote:
>>
>>> Yes, that works, I could populate the columns with the formula. But that does
>>> fill the yet to be completed in the future cells with error message #DIV/0!.
>>> Looks a bit messy!
>>
>> So have the formula check the value, if it's null then have the formula do
>> nothing.
>
> Yep.
>
> There's an IFERROR() function e.g. =IFERROR(1/0,"Ooops")

Yep, managed that, thanks - and it works for all the cells except a couple
that are returning values in the row directly below only, not errors - even
though they're dividing into a blank cell. So I need to add some IF statements
to blank out those values. Something for tomorrow :-)

Thanks all.
--
Cheers, Rob, Sheffield UK

Re: OTish: Autofill rows in Mac Excel

<1qj7flb.1m7sfex1ilee6yN%liz@poppyrecords.invalid.invalid>

  copy mid

https://www.novabbs.com/aus+uk/article-flat.php?id=18368&group=uk.comp.sys.mac#18368

  copy link   Newsgroups: uk.comp.sys.mac
Path: i2pn2.org!i2pn.org!weretis.net!feeder8.news.weretis.net!fu-berlin.de!uni-berlin.de!individual.net!not-for-mail
From: liz...@poppyrecords.invalid.invalid (Liz Tuddenham)
Newsgroups: uk.comp.sys.mac
Subject: Re: OTish: Autofill rows in Mac Excel
Date: Thu, 26 Oct 2023 10:58:54 +0100
Organization: Poppy Records
Lines: 18
Message-ID: <1qj7flb.1m7sfex1ilee6yN%liz@poppyrecords.invalid.invalid>
References: <1qj5zbf.1fza599hm140iN%liz@poppyrecords.invalid.invalid>
X-Trace: individual.net vLz4oyAHkuYjwl+dSOcZxwLaUz8DSkG3sXya6qKNR52D0vF0FA
X-Orig-Path: liz
Cancel-Lock: sha1:D8NE0MpQqRSAzcA8pZL43mzmcRg= sha256:d7KLxPN6cYLaW0okFagCXknwyDadAj9U/dCSLYdS3j4=
User-Agent: MacSOUP/2.4.6
 by: Liz Tuddenham - Thu, 26 Oct 2023 09:58 UTC

Liz Tuddenham <liz@poppyrecords.invalid.invalid> wrote:

Apologies for following-up my own post but there are two possible tests
that could be performed:

=IF(OR(A11="";B11="";C11="");"";'formula')
will still give an error if the inputs are all '0' [zero]

I should have given the alternative test:
=IF(OR(A11=0;B11=0;C11=0);"";'formula')
which will not display anything until all the inputs are above zero
(perhaps it will throw an error if they are alphabetical, but that is
probably a good thing).

--
~ Liz Tuddenham ~
(Remove the ".invalid"s and add ".co.uk" to reply)
www.poppyrecords.co.uk

1
server_pubkey.txt

rocksolid light 0.9.81
clearnet tor