+ Reply to Thread
Results 1 to 18 of 18

Help Convert this formula to dynamic one (Spills horizontally and vertically)

  1. 09-23-2022, 09:13 AM #1
    leprince2007
    leprince2007 is offline
    Forum Contributor
    Join Date
    02-24-2015
    Location
    Egypt
    MS-Off Ver
    Office 365 ProPlus
    Posts
    769

    Help Convert this formula to dynamic one (Spills horizontally and vertically)

    Hello Everybody,
    Could you help me in my question,please?
    I created The following formula in "Report "Sheet that gets data from "Data" Sheet:
    Please Login or Register  to view this content.
    I need to convert this formula to dynamic one(spills horizontally and vertically ) because:
    -it will be one dynamic formula only instead of copying it to the whole sheet,and it will be easier to edit.
    -One dynamic formula is faster than thousands of formulas.(My real data starts from 1-1-2021 and increases everyday)
    -it will spill automatically.
    Note:I tried to do it but I failed ,you can find the formula in column F only. Other columns contain the old formula.
    Please help me
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 09-23-2022, 09:33 AM #2
    leprince2007
    leprince2007 is offline
    Forum Contributor
    Join Date
    02-24-2015
    Location
    Egypt
    MS-Off Ver
    Office 365 ProPlus
    Posts
    769

    Re: Help Convert this formula to dynamic one (Spills horizontally and vertically)

    Please move this Thread to this forum "Excel Formulas & Functions"
    Sorry , I posted it here by mistake.
    Reply With QuoteRegister To Reply
  3. 09-25-2022, 09:38 AM #3
    leprince2007
    leprince2007 is offline
    Forum Contributor
    Join Date
    02-24-2015
    Location
    Egypt
    MS-Off Ver
    Office 365 ProPlus
    Posts
    769

    Re: Help Convert this formula to dynamic one (Spills horizontally and vertically)

    Bump....Bump
    Feel free to ask about anything not clear.
    Please help me
    Reply With QuoteRegister To Reply
  4. 09-27-2022, 12:02 AM #4
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,955

    Re: Help Convert this formula to dynamic one (Spills horizontally and vertically)

    I have moved this, as requested.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  5. 09-29-2022, 02:20 AM #5
    leprince2007
    leprince2007 is offline
    Forum Contributor
    Join Date
    02-24-2015
    Location
    Egypt
    MS-Off Ver
    Office 365 ProPlus
    Posts
    769

    Re: Help Convert this formula to dynamic one (Spills horizontally and vertically)

    Bump....Bump
    Please help me
    Reply With QuoteRegister To Reply
  6. 10-01-2022, 12:37 PM #6
    leprince2007
    leprince2007 is offline
    Forum Contributor
    Join Date
    02-24-2015
    Location
    Egypt
    MS-Off Ver
    Office 365 ProPlus
    Posts
    769

    Re: Help Convert this formula to dynamic one (Spills horizontally and vertically)

    Bump....Bump
    Please help me
    Reply With QuoteRegister To Reply
  7. 10-02-2022, 08:48 AM #7
    oeldere
    oeldere is offline
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Help Convert this formula to dynamic one (Spills horizontally and vertically)

    204 viewers and no answers, that indicates the question is not clear to the forummembers.

    It is also not clear to me.

    A pivot table to analyse the data.

    See the attached file.
    Attached Files Attached Files
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.
    Reply With QuoteRegister To Reply
  8. 10-02-2022, 09:49 AM #8
    leprince2007
    leprince2007 is offline
    Forum Contributor
    Join Date
    02-24-2015
    Location
    Egypt
    MS-Off Ver
    Office 365 ProPlus
    Posts
    769

    Re: Help Convert this formula to dynamic one (Spills horizontally and vertically)

    Thanks oeldere for your reply
    My question is simple:
    I have a formula in Report sheet I need to make it dynamic (Spills horizontally and vertically):
    Please Login or Register  to view this content.
    Explain formula:
    It gets weighted average for the following items with criteria of company and date :
    Moisture-Ash-Net Kcal-Fine
    Reply With QuoteRegister To Reply
  9. 10-04-2022, 12:48 PM #9
    leprince2007
    leprince2007 is offline
    Forum Contributor
    Join Date
    02-24-2015
    Location
    Egypt
    MS-Off Ver
    Office 365 ProPlus
    Posts
    769

    Re: Help Convert this formula to dynamic one (Spills horizontally and vertically)

    Is there anything not clear?
    Please help me
    Reply With QuoteRegister To Reply
  10. 10-04-2022, 02:48 PM #10
    oeldere
    oeldere is offline
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Help Convert this formula to dynamic one (Spills horizontally and vertically)

    "My question is simple:
    I have a formula in Report sheet I need to make it dynamic (Spills horizontally and vertically):"
    Maybe it is clear (and simple) to you, but not for me.

    Just take the members by the hand and show what the expected result should be.

    Also reply on the question on the pivot table solution.
    Reply With QuoteRegister To Reply
  11. 10-04-2022, 03:18 PM #11
    leprince2007
    leprince2007 is offline
    Forum Contributor
    Join Date
    02-24-2015
    Location
    Egypt
    MS-Off Ver
    Office 365 ProPlus
    Posts
    769

    Re: Help Convert this formula to dynamic one (Spills horizontally and vertically)

    Thanks oeldere for your reply.Your pivot table is not the solution for me.
    The expected results are in sheet report.It contains one formula that fills down and right to the whole sheet.
    The formula is right and working but I need it to be dynamic (one formula that spills horizontally and vertically because I have office 365).
    please help me
    Reply With QuoteRegister To Reply
  12. 10-04-2022, 03:30 PM #12
    oeldere
    oeldere is offline
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Help Convert this formula to dynamic one (Spills horizontally and vertically)

    Thanks oeldere for your reply.Your pivot table is not the solution for me.
    Why not, the results are not correct?

    Or another reason?

    And you don't add the expected results in your file.

    How do you expect we can test the offered solution.
    Reply With QuoteRegister To Reply
  13. 10-04-2022, 03:38 PM #13
    leprince2007
    leprince2007 is offline
    Forum Contributor
    Join Date
    02-24-2015
    Location
    Egypt
    MS-Off Ver
    Office 365 ProPlus
    Posts
    769

    Re: Help Convert this formula to dynamic one (Spills horizontally and vertically)

    You don`t read my my post #11 well:
    The expected results are in sheet "report".
    Reply With QuoteRegister To Reply
  14. 10-05-2022, 02:03 AM #14
    oeldere
    oeldere is offline
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Help Convert this formula to dynamic one (Spills horizontally and vertically)

    1) You did not answered my question about Pivot Table.

    2) If that was clear to me, I would not have asked.

    You presist not giving additional information.

    I quit on this topic, maybe another one can or will help you.
    Reply With QuoteRegister To Reply
  15. 10-05-2022, 02:31 AM #15
    leprince2007
    leprince2007 is offline
    Forum Contributor
    Join Date
    02-24-2015
    Location
    Egypt
    MS-Off Ver
    Office 365 ProPlus
    Posts
    769

    Re: Help Convert this formula to dynamic one (Spills horizontally and vertically)

    Sorry I didn`t mention my reasons of not accepting your pivot table solution.
    I didn`t accept Pivot table as a solution because:
    1-My formula gets "weighted average" not sum of data:
    Please Login or Register  to view this content.
    2-I want the data to be in the same format of "sheet Report" because it is better to be understood.
    Last edited by leprince2007; 10-05-2022 at 02:34 AM.
    Reply With QuoteRegister To Reply
  16. 10-05-2022, 04:19 AM #16
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,479

    Re: Help Convert this formula to dynamic one (Spills horizontally and vertically)

    Does anything in this thread help you ?

    https://www.excelforum.com/excel-for...l-formula.html
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
    Reply With QuoteRegister To Reply
  17. 10-05-2022, 09:14 AM #17
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,409

    Re: Help Convert this formula to dynamic one (Spills horizontally and vertically)

    The main reason that I haven't looked at it is that - as a sample - it is massive... way too large to visualise what is happening several screen widths away.
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
    Reply With QuoteRegister To Reply
  18. 10-05-2022, 05:54 PM #18
    leprince2007
    leprince2007 is offline
    Forum Contributor
    Join Date
    02-24-2015
    Location
    Egypt
    MS-Off Ver
    Office 365 ProPlus
    Posts
    769

    Re: Help Convert this formula to dynamic one (Spills horizontally and vertically)

    Quote Originally Posted by JohnTopley View Post
    Does anything in this thread help you ?

    https://www.excelforum.com/excel-for...l-formula.html
    I tried with no luck.
    Can anyone help me,please?
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Dynamic formula for vlookup & substitute that spills
    By paradise2sr in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 08-08-2022, 11:05 AM
  2. convert code to search data horizontally and show data vertically based on first row
    By Mussa-A in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-02-2021, 07:05 AM
  3. [SOLVED] what formula would return 2 results? horizontally and vertically
    By Tony0731 in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 03-12-2021, 01:07 AM
  4. [SOLVED] How to get an array formula to work both vertically and horizontally?
    By MrMac80 in forum Excel General
    Replies: 10
    Last Post: 07-15-2019, 01:28 AM
  5. [SOLVED] Applying single formula horizontally and vertically
    By rajre in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-21-2014, 07:10 AM
  6. [SOLVED] Vlookup on Dynamic Range that expands vertically and horizontally
    By huy_le in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-27-2013, 03:47 PM
  7. Drag horizontally but have formula increase vertically?
    By Greg_2 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-15-2011, 09:24 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1