Posts by h1h

    Re: Multiple worksheets summary

    put some sample data into sheet Summary (not links!!!) and re-upload to give us an idea what it will look like. The now blank sheet is not sufficient.

    Re: help with public variables involving multiple modules

    from MS Visual Basic Help:

    Variables declared using the Public statement are available to all procedures in all modules in all applications unless Option Private Module is in effect; in which case, the variables are public only within the project in which they reside.

    Re: Alternative to Nested If - Then with more than 7 functions

    if not two of the conditions can be true at the same time, you can concatenate the results of several IFsm or nested IFs.
    e.g.: =IF(B15="A";"X";"") & IF(C15="B";"Y";"")
    the result will be either X or Y. However if both conditions are fullfilled the result would be XY.

    Should this not help I suggest you build a userdefined function.

    Re: drop down list of formulas

    there is no way of inserting a formula via data validation, as far as I know. It is called data validation. after all. A formula is not data.
    In your example the extra column has the advantage, that you not only see results but also what they are based on. The caveate is a wider sheet. You'll have to choose.

    Re: Summing Negative and Positive Numbers Separately Across Numerous Columns

    the SUMPRODUCT can be used to overcome the lack of possible arguments in SUMIF function. (In older XL Versions which I am still useing) You can see that the ranges are of the same size (a must) the functions goes through the ranges kind of step by step. In our case it checks wether the criteria "=AT2" is given. if yes the result of the next check is multiplied by 1, if no by 0. The next step checks if the value is below 0, if yes the next check is again multiplied by 1, if not 0. The last part is "just a range" without check, so the value is multiplied be the former results. If any of those was 0 the result will be zero, This is done for each cell in the range and the results are added up). In our example think of it as "if the range in the helper line has the same controll number AND id the value is larger (or smaller) than 0 THE add, else do not add.
    From what I have heard the latest XL version has an improved SUMIF function. I do not yet know how that one works.

    Have a nice weekend

    Re: Summing Negative and Positive Numbers Separately Across Numerous Columns

    I suggest you insert a helper row, e.g. above your current row 2. In that row you mark all columns belonging together incl. the ones holding the results. Then you can use these formulas
    you may allways hide the helper row to keep the looks as they were before.

    Re: drop down list of formulas

    enter the function as needed, but instead of 10, 100 etc. e ref to the column next to it. In that column use data validation with a list of the desired arguments, i.e. 10, 100, etc.

    Re: Saving a worksheet as a new workbook

    try this

    Re: Lookup &amp; Sum Offset in Row

    As far as I am concerned you should reconsider the layout of your data. It apperas that one line consists of several individual sales-records. Instead of having e.g. 10 such recorda ccross you should have one line for each sale, i.e. 10 lines. Then you could easily use SUMIF function etc.