Share via

=IF(INDIRECT and ISBLANK Functions

Anonymous
2021-10-10T22:31:35+00:00

Hello!

I could sure use some help tweaking the =IF(INDIRECT function in one of my Excel worksheets. My workbook has worksheets that track my monthly income, monthly budget, monthly actual expenses, and expense variances. The variance worksheet uses a formula that looks at the budget and actuals worksheets and returns the difference for each month (individual columns). My problem is that the formula returns a value in every month column for the whole year, but I'd rather it only return values for past and current months IF there are values in the corresponding month columns of in the budget and actuals worksheets.

Here's the formula: =IF(@INDIRECT("Actual_Housing["&C$6&"]")="","",@INDIRECT("Budget_Housing["&C$6&"]")-@INDIRECT("Actual_Housing["&C$6&"]"))

Can ISBLANK be used in some way? Or what do you suggest? Is what I want possible?

I hope my desciption of the issue makes sense!

Much appreciation for any and all help :)

dzcran

Microsoft 365 and Office | Excel | Other | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2021-10-27T06:53:57+00:00

    Hi,

    Please check whether the following is helpful.

    Formula in cell C7 is: =SUM(Var_Housing5987[JANUARY])

    Image

    - The above formula simply means:

    =SUM(C9:C15)

    If You sum the yellow-highlighted part in the above screenshot, i.e. range C9:C15, the answer = 74.66.

    Same as value in cell C7.

    - Please drag the formula to the right till December.

    Please re-look at the following:

    - In My earlier response, I suggested the following formula in cell C9:

    =IF(TODAY()<VALUE(1&"-"&C$6&"-"&YEAR(TODAY())),"",IF(@INDIRECT("Actual_Housing["&C$6&"]")=0,"",@INDIRECT("Budget_Housing["&C$6&"]")-@INDIRECT("Actual_Housing["&C$6&"]")))

    e.g. after applying the above formula in all cells in Housing category > please refer cell J12 (Home/maintenance/repair)

    - Cell J12 will be blank.

    Image

    - Budgeted Home maintenance/repair is Rs.80.

    - Actual Home maintenance/repair is Rs.0.

    Because, Actual is blank, in cell J12, formula returns blank.

    - If You want the formula to return Rs.80, in cell C9, please use following formula:

    =IF(TODAY()<VALUE(1&"-"&C$6&"-"&YEAR(TODAY())),"",@INDIRECT("Budget_Housing["&C$6&"]")-@INDIRECT("Actual_Housing["&C$6&"]"))

    Image

    - Please drag the above formula:

    to the right & down for Housing category.

    Please respond if You require further support. I will try My best to be of help.

    If I was able to help You, please mark My response as answer and helpful.

    Thank You!

    1 person found this answer helpful.
    0 comments

9 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2021-10-23T17:18:39+00:00

    Hi,

    I posted a link to my workbook, but haven't gotten anyfeed back yet.. Anyone out there have a little time and skill?

    Sure would appreciate it. :)

    0 comments
  2. Anonymous
    2021-10-16T16:26:25+00:00

    Hi,

    Sorry for my delay, I had to work all week. Here is a link to my file: https://1drv.ms/x/s!AnnU3VnMUVdYgq017r8lTQ4n1mnE9w?e=UGJt65

    My fingers are crossed that you see an easy solution. :)

    Best,

    dzcran

    0 comments
  3. Anonymous
    2021-10-11T02:28:31+00:00

    Hi dzcran, hope you're doing well. I’m Ian, and I’m happy to help you today.

    I'm sorry to hear about this issue.

    May you share a sample file in OneDrive so that we can analyze the data and apply an appropriate formula based on your need?

    This is a user-to-user support forum and I am a fellow user.

    I hope this helps, but please let me know if you need anything else

    0 comments
  4. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2021-10-10T23:25:58+00:00

    Hi,

    It is difficult to visualise your problem. Share the link from where i can download your MS Excel file and show the expected result very clearly.

    0 comments