Help needed with Excel spreadsheet

classic Classic list List threaded Threaded
7 messages Options
Tom Williams Tom Williams
Reply | Threaded
Open this post in threaded view
|

Help needed with Excel spreadsheet

I know it's odd to ask for Excel help in this mailing list but I need
help locating a LibreOffice Calc function/feature in Excel.  :)

I'm helping a friend create a billing invoice and since I'm a Linux
user, I'm using Calc to build the template.  So far, it's working well
and given I'm not an Excel user, I find myself struggling some in
getting Excel to "behave".  Anyway, I recently discovered the "/Expand
reference when new rows are inserted/" in Calc.  It does exactly what I
needed.   When I add/remove a row in/from the spreadsheet, my total
"SUM()" formula is updated automagically.

Does the equivalent feature exist in Excel?  What I've found is, when
adding rows *after* the last row in my SUM() formula, the new row is
excluded from the SUM() formula.   So, my formula is "=SUM(D7:D27)". 
Once I add row 28, I need the SUM() formula to be updated to
"=SUM(D7:D28)".  Conversely, when row 28 is removed, I want the SUM()
formula to be updated to "=SUM(D7:D27".   In Calc, the "/Expand
reference when new rows are inserted/" feature does this work for me.

I did some web searching and found this article:

https://www.extendoffice.com/documents/excel/3895-excel-update-formula-when-inserting-rows.html

but I hope to find an easier and more flexible way to accomplish what I
need.  Or is that approach what I really should use?

Thanks in advance!

Peace...

"The Other" Tom


--
/When I leave, I don't know what I'm hoping to find,
And when I leave, I don't know what I'm leaving behind.../

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Tom Williams Tom Williams
Reply | Threaded
Open this post in threaded view
|

Re: Help needed with Excel spreadsheet

On 3/6/19 8:26 AM, Tom Williams wrote:

> I know it's odd to ask for Excel help in this mailing list but I need
> help locating a LibreOffice Calc function/feature in Excel.  :)
>
> I'm helping a friend create a billing invoice and since I'm a Linux
> user, I'm using Calc to build the template.  So far, it's working well
> and given I'm not an Excel user, I find myself struggling some in
> getting Excel to "behave".  Anyway, I recently discovered the "/Expand
> reference when new rows are inserted/" in Calc.  It does exactly what I
> needed.   When I add/remove a row in/from the spreadsheet, my total
> "SUM()" formula is updated automagically.
>
> Does the equivalent feature exist in Excel?  What I've found is, when
> adding rows *after* the last row in my SUM() formula, the new row is
> excluded from the SUM() formula.   So, my formula is "=SUM(D7:D27)". 
> Once I add row 28, I need the SUM() formula to be updated to
> "=SUM(D7:D28)".  Conversely, when row 28 is removed, I want the SUM()
> formula to be updated to "=SUM(D7:D27".   In Calc, the "/Expand
> reference when new rows are inserted/" feature does this work for me.
>
> I did some web searching and found this article:
>
> https://www.extendoffice.com/documents/excel/3895-excel-update-formula-when-inserting-rows.html
>
> but I hope to find an easier and more flexible way to accomplish what I
> need.  Or is that approach what I really should use?
>
> Thanks in advance!
>
> Peace...
>
> "The Other" Tom
>
>
Nevermind!  I found this: 

https://bettersolutions.com/excel/formulas/automatic-expansion.htm

That should do what I need!  

Thanks anyway!  :)

Peace...

"The Other" Tom

--
/When I leave, I don't know what I'm hoping to find,
And when I leave, I don't know what I'm leaving behind.../

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Luuk Luuk
Reply | Threaded
Open this post in threaded view
|

Re: Help needed with Excel spreadsheet


On 6-3-2019 17:48, Tom Williams wrote:

> On 3/6/19 8:26 AM, Tom Williams wrote:
>> I know it's odd to ask for Excel help in this mailing list but I need
>> help locating a LibreOffice Calc function/feature in Excel.  :)
>>
>> I'm helping a friend create a billing invoice and since I'm a Linux
>> user, I'm using Calc to build the template.  So far, it's working well
>> and given I'm not an Excel user, I find myself struggling some in
>> getting Excel to "behave".  Anyway, I recently discovered the "/Expand
>> reference when new rows are inserted/" in Calc.  It does exactly what I
>> needed.   When I add/remove a row in/from the spreadsheet, my total
>> "SUM()" formula is updated automagically.
>>
>> Does the equivalent feature exist in Excel?  What I've found is, when
>> adding rows *after* the last row in my SUM() formula, the new row is
>> excluded from the SUM() formula.   So, my formula is "=SUM(D7:D27)".
>> Once I add row 28, I need the SUM() formula to be updated to
>> "=SUM(D7:D28)".  Conversely, when row 28 is removed, I want the SUM()
>> formula to be updated to "=SUM(D7:D27".   In Calc, the "/Expand
>> reference when new rows are inserted/" feature does this work for me.
>>
>> I did some web searching and found this article:
>>
>> https://www.extendoffice.com/documents/excel/3895-excel-update-formula-when-inserting-rows.html
>>
>> but I hope to find an easier and more flexible way to accomplish what I
>> need.  Or is that approach what I really should use?
>>
>> Thanks in advance!
>>
>> Peace...
>>
>> "The Other" Tom
>>
>>
> Nevermind!  I found this:
>
> https://bettersolutions.com/excel/formulas/automatic-expansion.htm
>
> That should do what I need!
>
> Thanks anyway!  :)
>
> Peace...
>
> "The Other" Tom
>
When i was reading above, i thought you were search for subtotals.

Excel has also a function named SUBTOTAL()

https://support.office.com/en-us/article/subtotal-function-7b027003-f060-4ade-9040-e478765b9939




--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Windows10 / LibreOffice  (latest?)
TomD TomD
Reply | Threaded
Open this post in threaded view
|

Re: Help needed with Excel spreadsheet

Hi :)
Sounds like a perfect time to suggest they install LO too.

If an Excel person hadn't immediately found what they were looking for in
Calc they would have immediately claimed that Calc couldn't do the task.

There are loads of articles about LO that have such posts in the comments
section.  It can be fun to refute them in as few words as possible.
Regards from a Tom :)


On Wed, 6 Mar 2019 18:21 Luuk <[hidden email] wrote:

>
> On 6-3-2019 17:48, Tom Williams wrote:
> > On 3/6/19 8:26 AM, Tom Williams wrote:
> >> I know it's odd to ask for Excel help in this mailing list but I need
> >> help locating a LibreOffice Calc function/feature in Excel.  :)
> >>
> >> I'm helping a friend create a billing invoice and since I'm a Linux
> >> user, I'm using Calc to build the template.  So far, it's working well
> >> and given I'm not an Excel user, I find myself struggling some in
> >> getting Excel to "behave".  Anyway, I recently discovered the "/Expand
> >> reference when new rows are inserted/" in Calc.  It does exactly what I
> >> needed.   When I add/remove a row in/from the spreadsheet, my total
> >> "SUM()" formula is updated automagically.
> >>
> >> Does the equivalent feature exist in Excel?  What I've found is, when
> >> adding rows *after* the last row in my SUM() formula, the new row is
> >> excluded from the SUM() formula.   So, my formula is "=SUM(D7:D27)".
> >> Once I add row 28, I need the SUM() formula to be updated to
> >> "=SUM(D7:D28)".  Conversely, when row 28 is removed, I want the SUM()
> >> formula to be updated to "=SUM(D7:D27".   In Calc, the "/Expand
> >> reference when new rows are inserted/" feature does this work for me.
> >>
> >> I did some web searching and found this article:
> >>
> >>
> https://www.extendoffice.com/documents/excel/3895-excel-update-formula-when-inserting-rows.html
> >>
> >> but I hope to find an easier and more flexible way to accomplish what I
> >> need.  Or is that approach what I really should use?
> >>
> >> Thanks in advance!
> >>
> >> Peace...
> >>
> >> "The Other" Tom
> >>
> >>
> > Nevermind!  I found this:
> >
> > https://bettersolutions.com/excel/formulas/automatic-expansion.htm
> >
> > That should do what I need!
> >
> > Thanks anyway!  :)
> >
> > Peace...
> >
> > "The Other" Tom
> >
> When i was reading above, i thought you were search for subtotals.
>
> Excel has also a function named SUBTOTAL()
>
>
> https://support.office.com/en-us/article/subtotal-function-7b027003-f060-4ade-9040-e478765b9939
>
>
>
>
> --
> To unsubscribe e-mail to: [hidden email]
> Problems?
> https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> List archive: https://listarchives.libreoffice.org/global/users/
> Privacy Policy: https://www.documentfoundation.org/privacy
>

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Tom Williams Tom Williams
Reply | Threaded
Open this post in threaded view
|

Re: Help needed with Excel spreadsheet

In reply to this post by Luuk
On 3/6/19 10:18 AM, Luuk wrote:

>
> On 6-3-2019 17:48, Tom Williams wrote:
>> On 3/6/19 8:26 AM, Tom Williams wrote:
>>> I know it's odd to ask for Excel help in this mailing list but I need
>>> help locating a LibreOffice Calc function/feature in Excel.  :)
>>>
>>> I'm helping a friend create a billing invoice and since I'm a Linux
>>> user, I'm using Calc to build the template.  So far, it's working well
>>> and given I'm not an Excel user, I find myself struggling some in
>>> getting Excel to "behave".  Anyway, I recently discovered the "/Expand
>>> reference when new rows are inserted/" in Calc.  It does exactly what I
>>> needed.   When I add/remove a row in/from the spreadsheet, my total
>>> "SUM()" formula is updated automagically.
>>>
>>> Does the equivalent feature exist in Excel?  What I've found is, when
>>> adding rows *after* the last row in my SUM() formula, the new row is
>>> excluded from the SUM() formula.   So, my formula is "=SUM(D7:D27)".
>>> Once I add row 28, I need the SUM() formula to be updated to
>>> "=SUM(D7:D28)".  Conversely, when row 28 is removed, I want the SUM()
>>> formula to be updated to "=SUM(D7:D27".   In Calc, the "/Expand
>>> reference when new rows are inserted/" feature does this work for me.
>>>
>>> I did some web searching and found this article:
>>>
>>> https://www.extendoffice.com/documents/excel/3895-excel-update-formula-when-inserting-rows.html
>>>
>>>
>>> but I hope to find an easier and more flexible way to accomplish what I
>>> need.  Or is that approach what I really should use?
>>>
>>> Thanks in advance!
>>>
>>> Peace...
>>>
>>> "The Other" Tom
>>>
>>>
>> Nevermind!  I found this:
>>
>> https://bettersolutions.com/excel/formulas/automatic-expansion.htm
>>
>> That should do what I need!
>>
>> Thanks anyway!  :)
>>
>> Peace...
>>
>> "The Other" Tom
>>
> When i was reading above, i thought you were search for subtotals.
>
> Excel has also a function named SUBTOTAL()
>
> https://support.office.com/en-us/article/subtotal-function-7b027003-f060-4ade-9040-e478765b9939
>
>
>
>
>
Thanks for the info!  Sorry I wasn't more clear.  :(   I just needed the
SUM formulas to update as I added/deleted rows.  :)

Peace...

"The Other" Tom

--
/When I leave, I don't know what I'm hoping to find,
And when I leave, I don't know what I'm leaving behind.../

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Tom Williams Tom Williams
Reply | Threaded
Open this post in threaded view
|

Re: Help needed with Excel spreadsheet

In reply to this post by TomD
On 3/6/19 5:42 PM, Tom Davies wrote:
> Hi :)
> Sounds like a perfect time to suggest they install LO too.
>
> If an Excel person hadn't immediately found what they were looking for in
> Calc they would have immediately claimed that Calc couldn't do the task.
>
> There are loads of articles about LO that have such posts in the comments
> section.  It can be fun to refute them in as few words as possible.
> Regards from a Tom :)


You know, I'm about ready to do that.   After doing some experimentation
with Excel today, I found it just doesn't want to "play nice" with what
I'm trying to do.  I want to have an image as the spreadsheet header and
have the basic formulas update when I add/remove rows.  I'm sure with
enough time, I could get Excel to behave but I've been able to get what
I needed working far more easily with Calc.

I haven't given up, just yet, but I'm really surprised at how stubborn
Excel is, when it comes to the things I'm trying to do with it.  :(

Peace...

"The Other" Tom

>
>
> On Wed, 6 Mar 2019 18:21 Luuk <[hidden email] wrote:
>
>> On 6-3-2019 17:48, Tom Williams wrote:
>>> On 3/6/19 8:26 AM, Tom Williams wrote:
>>>> I know it's odd to ask for Excel help in this mailing list but I need
>>>> help locating a LibreOffice Calc function/feature in Excel.  :)
>>>>
>>>> I'm helping a friend create a billing invoice and since I'm a Linux
>>>> user, I'm using Calc to build the template.  So far, it's working well
>>>> and given I'm not an Excel user, I find myself struggling some in
>>>> getting Excel to "behave".  Anyway, I recently discovered the "/Expand
>>>> reference when new rows are inserted/" in Calc.  It does exactly what I
>>>> needed.   When I add/remove a row in/from the spreadsheet, my total
>>>> "SUM()" formula is updated automagically.
>>>>
>>>> Does the equivalent feature exist in Excel?  What I've found is, when
>>>> adding rows *after* the last row in my SUM() formula, the new row is
>>>> excluded from the SUM() formula.   So, my formula is "=SUM(D7:D27)".
>>>> Once I add row 28, I need the SUM() formula to be updated to
>>>> "=SUM(D7:D28)".  Conversely, when row 28 is removed, I want the SUM()
>>>> formula to be updated to "=SUM(D7:D27".   In Calc, the "/Expand
>>>> reference when new rows are inserted/" feature does this work for me.
>>>>
>>>> I did some web searching and found this article:
>>>>
>>>>
>> https://www.extendoffice.com/documents/excel/3895-excel-update-formula-when-inserting-rows.html
>>>> but I hope to find an easier and more flexible way to accomplish what I
>>>> need.  Or is that approach what I really should use?
>>>>
>>>> Thanks in advance!
>>>>
>>>> Peace...
>>>>
>>>> "The Other" Tom
>>>>
>>>>
>>> Nevermind!  I found this:
>>>
>>> https://bettersolutions.com/excel/formulas/automatic-expansion.htm
>>>
>>> That should do what I need!
>>>
>>> Thanks anyway!  :)
>>>
>>> Peace...
>>>
>>> "The Other" Tom
>>>
>> When i was reading above, i thought you were search for subtotals.
>>
>> Excel has also a function named SUBTOTAL()
>>
>>
>> https://support.office.com/en-us/article/subtotal-function-7b027003-f060-4ade-9040-e478765b9939
>>
>>
>>
>>
>> --
>> To unsubscribe e-mail to: [hidden email]
>> Problems?
>> https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
>> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
>> List archive: https://listarchives.libreoffice.org/global/users/
>> Privacy Policy: https://www.documentfoundation.org/privacy
>>

--
/When I leave, I don't know what I'm hoping to find,
And when I leave, I don't know what I'm leaving behind.../

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Luuk Luuk
Reply | Threaded
Open this post in threaded view
|

Re: Help needed with Excel spreadsheet

In reply to this post by Tom Williams

On 6-3-2019 17:48, Tom Williams wrote:

> On 3/6/19 8:26 AM, Tom Williams wrote:
>> I know it's odd to ask for Excel help in this mailing list but I need
>> help locating a LibreOffice Calc function/feature in Excel.  :)
>>
>> I'm helping a friend create a billing invoice and since I'm a Linux
>> user, I'm using Calc to build the template.  So far, it's working well
>> and given I'm not an Excel user, I find myself struggling some in
>> getting Excel to "behave".  Anyway, I recently discovered the "/Expand
>> reference when new rows are inserted/" in Calc.  It does exactly what I
>> needed.   When I add/remove a row in/from the spreadsheet, my total
>> "SUM()" formula is updated automagically.
>>
>> Does the equivalent feature exist in Excel?  What I've found is, when
>> adding rows *after* the last row in my SUM() formula, the new row is
>> excluded from the SUM() formula.   So, my formula is "=SUM(D7:D27)".
>> Once I add row 28, I need the SUM() formula to be updated to
>> "=SUM(D7:D28)".  Conversely, when row 28 is removed, I want the SUM()
>> formula to be updated to "=SUM(D7:D27".   In Calc, the "/Expand
>> reference when new rows are inserted/" feature does this work for me.
>>
>> I did some web searching and found this article:
>>
>> https://www.extendoffice.com/documents/excel/3895-excel-update-formula-when-inserting-rows.html
>>
>> but I hope to find an easier and more flexible way to accomplish what I
>> need.  Or is that approach what I really should use?
>>
>> Thanks in advance!
>>
>> Peace...
>>
>> "The Other" Tom
>>
>>
> Nevermind!  I found this:
>
> https://bettersolutions.com/excel/formulas/automatic-expansion.htm
>
> That should do what I need!
>
> Thanks anyway!  :)
>
> Peace...
>
> "The Other" Tom
>
In EXCEL you can do this:

A B C
1 2 3
=A2+3 =B2+3 =C2+3

(I used CTRL+T to show formulas, you should see the values 4,5,6)

When you select A1:C3, and choose 'Insert'/'Table'

After this, typing the value 7 in cell 'A4', en pressing 'Enter' results
in the fields B4 and C4 being updated with the same formula as in row 3.

How can this be achieved in LibreOffice Calc ?






--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Windows10 / LibreOffice  (latest?)