+ Reply to Thread
Results 1 to 4 of 4

Unable to use cell for reference which has formula -- Please advie

  1. #1
    Registered User
    Join Date
    07-15-2013
    Location
    franklin, usa
    MS-Off Ver
    Excel 2003
    Posts
    5

    Unable to use cell for reference which has formula -- Please advie

    I have one coloumnA with formula =IF(F4="","",(H4-F4)) ------ the result I want to use for reference in which I am unable to use the result value using below formula.
    I have one more cell B with month & date

    Currently using formula =SUMPRODUCT((TEXT(A4:A1501,"mmmyyyy")=$B4&$C4)*('Credit Cards&Loans Transactions'!J4:J1501))
    Cell B4 has month & C4 has year.

    J4:j1501 cell has the formula "=IF(F4="","",(H4-F4)) " , I want to use the result of cell in above formula.

    Please advice, Any help would be appreciated.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,240

    Re: Unable to use cell for reference which has formula -- Please advie

    Without seeing a (clean) sample of what you are working with, and what you are trying to do, its hard to say what is going wrong here
    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

  3. #3
    Registered User
    Join Date
    07-15-2013
    Location
    franklin, usa
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Unable to use cell for reference which has formula -- Please advie

    Actually I have a spreadsheet with two tabs sheets - once has data and another produce stats.

    ->data tab has following coloums
    A coloumn has month and year information in formate - "mmmyyyy"
    H coloumn has EMI data -- eg: 1000$
    F coloumn has Intrest data -- eg: 100$
    J coloumn has Principal clearing data with formula "=IF(F4="","",(H4-F4))" which gives me result $900 cleared from above example data in H& F

    column.

    This tab sheet works fine for calculation. But having problem in Stats tab sheet.

    -> Stats tab sheet has following coloumn/row which is giving "#VALUE!" error.

    > Decription Cell : The row which has description is "Total Principal Cleared for the period"
    > Problem cell : The row result cell G12 in stats tab sheet is giving error "#VALUE!" has formula "=SUMPRODUCT((TEXT('Credit Cards&Loans

    Transactions'!A4:A1501,"mmmyyyy")=$B4&$C4)*('Credit Cards&Loans Transactions'!J4:J1501))"

    > Cell B4 has Month drop down list and C4 cell has year drop down list. Using both cell I want to select month and year to get the stats from

    data tab sheet "A" coloumn A4:A1501 range.

    > Purpose of stats tab: when I select month and year from in the B4 & C4 cell I want to sum the amount from J4:J15011 cell range for selected

    month and year. Each cell J4:J15011 has formula "=IF(F4="","",(H4-F4))".


    I believe the error I am getting in the result cell (G12- in Stats tab) is because the cell is refering to formula in "J4:J1501" cells not the

    result of the cell. which suppose to refer the result amount so the sum amount (principl amount) of the selected month and year is displayed in

    G12 cell in stats tab sheet.

    Please advice for solution, Any help would be appreciated.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,357

    Re: Unable to use cell for reference which has formula -- Please advie

    Can you not attach the Excel workbook here? It would make helping you a lot easier.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Replace absolute cell reference with Indirect cell reference in formula
    By Roothy in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-11-2013, 04:46 AM
  2. Dragging formula, change sheet reference, but not cell reference
    By Kalilaya0419 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-13-2013, 04:50 PM
  3. [SOLVED] Unable to copy formula into another cell in same sheet
    By naveen4pundir in forum Excel General
    Replies: 8
    Last Post: 04-25-2012, 02:16 PM
  4. Replies: 4
    Last Post: 07-15-2008, 01:42 PM
  5. [SOLVED] Unable to enter Formula into cell
    By Kathy L in forum Excel General
    Replies: 2
    Last Post: 12-27-2005, 06:00 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1