Posts by bellevue

    Re: Find solution to #VALUE! error message in SUMPRODUCT

    Thanks ever so much NBVC, I feel such a wally as I thought I'd checked "simple" things like that but I obviously missed it.

    It's been puzzling me for hours and I'm just so sorry there's no "technical learning" so to speak, to come from this for anyone else but I guess it reminds us all to check for bugs.

    I've added to your reputation and want to say a huge thank you.


    I have a macro enabled workbook with two worksheets and a #VALUE! error message appears in one column of formulae but not in another column that has the same formulae. To explain how the workbook works, basically one enters a start date (Sheet Availability D1) and a end date (Sheet Availability D2) for a 7 day period and then see the price for those 7 nights in Sheet A cells L13 and Y13 for two separate letting units.

    I've attached a copy of the workbook: the issue appears in Worksheet A cells U7:U16 (encircled in a red box) using a SUMPRODUCT function. As far as I can see, the same formulae are used in Worksheet A cells H7:H16 (encircled in a blue box) and return a result without the error message.

    In addition, when I look at a copy of the workbook, saved elsewhere, with the same formulae, both U7:U16 and H7:H16 work fine without an error message.

    Can anyone explain what's going wrong please ?


    Re: Running a Macro from within another Macro


    Thanks so much to "S O" for their help on this one and, when I got the solution from them, it all seemed to work.

    However, when I get to the blank sheet in Workbook1, it opens Workbook2 and runs the macro for Workbook2 but it doesn't close Workbook1 ?

    Where have I gone wrong please ? Any help would be greatly appreciated.


    PS - I'm aware of the change in file format when it saves

    I hope someone can help me please.

    I have created a macro ("CreateUploadSheetA" - code below) which runs within a workbook called "Workbook1.xlsm".

    It performs the macro in a loop through all the worksheets in this workbook until it encounters a blank worksheet.

    At this point, it opens another workbook ("Workbook2.xlsm") and activates it and I've coded it to run a macro called "CreateUploadSheetH" before returning to Workbook1.xlsm and then closing that workbook.

    However, as soon as the macro reaches the line to run the macro called "CreateUploadSheetH", it stops and returns the error message "Cannot run the macro 'CreateUploadSheetH'. The macro may not be available in this workbook or all macros may be disabled".

    I've been into Windows Button > Excel Options > Trust Center > Trust Center Settings > Macro Settings and "Enable all macros" is selected.

    In addition, the macro "CreateUploadSheetH" does exist (and thus is available...I think ?) within Workbook2.xlsm (the "active" workbook...I think) but it is not available within Workbook1.xlsm...not sure if that makes a difference.

    Can anyone help please ?


    I hope someone can help please.

    I have created a macro that runs through a series of worksheets in a workbook using a loop and performs calculations, the last of the worksheets is blank; I have set it such that when it comes across an empty cell (e.g. an empty cell within the last worksheet) it should stop and close the workbook...however, it's not working.

    It gets to the last worksheet and stops but doesn't close the workbook.

    Can anyone let me know please where I'm going wrong ?



    I have created a macro in excel 2007 that (in this order) opens a word document and then a web page: at the end of the macro (as it currently stands) I have the web page visible on my screen with the excel spreadsheet and word icons visible in the taskbar.

    What I would like to do is add some code to the end of the macro that will then "snap" the three windows side by side (i.e. effectively right click the taskbar and select the "Show windows side by side" option).

    The code, so far is:

    Sub Na02d()
        ActiveWorkbook.FollowHyperlink _
          Address:="H:\*****\Handling\*****\Gold\2015PriceGrid.docx", _
        ActiveWorkbook.FollowHyperlink _
          Address:="http://www.*****.com/to-let-userid***.html#availabilityAnchor", _
    End Sub

    I've used asterisks where personal information is visible.

    I hope someone can help please.

    Best wishes

    Re: Looking up data from a table

    Hello Derk

    Thanks ever so much for coming back to me.

    There is some flexibility in the layout of the table, as long as it looks up the days before arrival as suggested. I'm not 100% sure what a look up table but I guess that's fine as well.

    I hope you can help.

    Best wishes (and thanks)

    I hope someone can help please; I've attached a sample worksheet so you can see what I'm trying to do.

    I have a calendar of arrival dates for a property (cells B10:B24 in the attached example) and, for each date, I want cells C10:C24 to hold data from a table within the worksheet (cells B4:I7 in the attached example) based upon:

    the number of days before arrival (cells B3:I3 in the attached example) AND
    the "code" for that arrival day (cells A4:A7 in the attached example)

    Thus, if the arrival day is 11/11/14, today would be 4 days before arrival (11/11/14-7/11/14) and the code for the arrival day would be "2" (cell A13). When you look at the table (A2:I7) the number at the intersection of these two criteria and what would need to go into cell C13 would be "15" (from cell H6).

    I'm struggling to find the formula to make this happen and hope someone can help please.


    Re: Combining two IF statements

    First of all, can I say thank you very much to you guys for coming back with a reply, I do appreciate it.

    Well…NBVC…you’ve done it again !! Thank you so much for the solution, you have really saved me an awful lot of time…bless you !

    Thanks also to Batman and MikeRickson, your solutions are good as well.

    For Luke M, I think you may have misread the formula as the two statements aren’t exclusive…the first is calculating the product of Availability!$D$1:Availability!$D$1 whereas the second is calculating the product of Availability!$D$1:Availability!$D$2.

    [FONT=&quot]Anyway, thanks to you for your contribution as well.[/FONT]

    I do so hope someone can help please ?

    I have two IF statements (that each include SUMPRODUCT functions) and I need to combine them, potentially by using "IF(AND(..."

    The two statements are:



    Ideally, the result if both statements are true should be "1" or "0" if either/both are false.

    I'm not 100% sure if it's possible to combine two IF statements that include SUMPRODUCT but I'm sure someone will quickly tell me. I've tried to do it like this but to no avail:


    I hope someone can help.



    I hope someone can help please. I have four IF statements that work fine on their own; however, I need to combine them into one statement. I have tried a lot of ways but can't seem to get it to work.

    The four statements are below are I would really appreciate some help please.