Posts by DeZaStR

    Re: Running Check For Duplicates In List


    Quote from DeZaStR

    I'd like to know if there is any way I can have Excel keep a running check for any duplicate entries previously made?


    Basically, if I begin keying in 5 digit numbers in column A, can I have column B tell me when I have already used a given number?


    Nevermind. I've just found a resolution using the countif function.


    Thanks just the same.

    I'd like to know if there is any way I can have Excel keep a running check for any duplicate entries previously made?


    Basically, if I begin keying in 5 digit numbers in column A, can I have column B tell me when I have already used a given number?

    Ok... this should be an easy one for you VBA wizards. I'm simply trying to write a piece of code that evaluates one cell's value before continuing the macro.


    Basically, if cell J3 is blank, then stop the macro and display a msgbox. If J3 is not blank, then continue with the macro. Instead, regardless of what's in cell J3, it is ALWAYS displaying the msgbox and will not continue the macro.


    I know that I don't know VBA, but I didn't figure this would be hard to do. Where am I going wrong?



    Thanks in advance...

    Re: Find/Display All Rows Which Match One Value


    Revisting this issue briefly, I have two more questions.


    1.) Is there a way to insert a row after each set of copied cells? Just for cosmetic purposes, making it easier to read...


    2.) Can I pull the original cell that I was checking for data into this report sheet? Meaning, the very first test performed (whether or not a specific cell was greater than zero)...if that cell WAS great than zero, can that value be entered into the copied data as well?


    Thanks again!

    Re: Find/Display All Rows Which Match One Value


    Ok...


    This works perfectly in my test workbook. However, I'm trying to now implement the code into my actual workbooks.


    In the section that defines the second workbook...

    Code
    With ThisWorkbook.Worksheets("Sheet2") 
            Set rngData = .Range("A1:A" & .Range("A65536").End(xlUp).Row)


    ...how do I change that to an outside workbook? For example, how can it reference "D:\documents\workbook.xls"? I "think" that's all I need to get this working for me.


    Thanks a lot for your help!

    I have two sheets with related, but different, information. What I would like to do is to first check if cell value B2 in Workbook 1 is greater than zero (easy enough). If it is, then I want to lookup and display all rows in Workbook 2 whose index value in A2 match that of Workbook 1 cell A2. Then, proceed to check the rest of the sheet...B3, B4, B5, etc...and display the resulting data for each of those as well.


    Does that make sense?


    I have attached a generic example workbook to better describe what I need. Please note that although "Workbook1" and "Workbook2" are on different sheets, in reality they are totally separate workbooks.


    I assume this will have to be done in VBA, as standard Excel functions (such as vlookup) will only return the first row of data it finds. However, if you know something I don't, and this CAN be done with standard Excel functions, please let me know.


    Thanks in advance for your assistance!

    Quote from XL-Dennis

    Hi,


    Instead of using the SUMIF-function consider to use SUMPRODUCT like the following:


    [vba]
    =SUMPRODUCT((A2:A10=10)*(B2:B10=20)*(C2:C20))
    [/VBA]


    I just tried that, and it doesn't seem to be working in my case. Here are some specifics about what I'm trying to accomplish:


    C3:C327 contains delivery dates
    M3:M327 contains carrier names
    V3:V327 contains the information I want to sum


    So, I want to sum all occurances in column V when the carrier name is "Bob" in column M and the delivery date is 9/3/04 in column C. Then next I will go to the date 9/4/04 and sum all occurances for "Bob". Etc...etc...then I will start over with all occurances for carrier "Jim"....and so on....


    I hope this makes sense...

    I don't know if it can be done, but I'm trying to figure out to write a SumIF function based on two separate sets of criteria. For example, columns A & B contain the criteria data and column C is the data to be summed. "If BOTH column A = 'value' AND column B = 'value', then sum column C".


    Any ideas? I've obviously tried incorporating the AND() function into this, but I'm just not quite getting it....and don't know if it can even be "gotten". :)


    Thanks in advance...

    Thank you for the response. However, my issue is with linking across many different workbooks. I believe I wasn't clear enough. I would like to sum the data in cell A1 through roughly 30 different workbooks (1.xls - 31.xls) into the monthly.xls workbook. (I used the word "worksheet" before, which was the wrong term to use) The only way I can see to do this right now is open each individual workbook and manually link the cell, but this will take FOREVER for what I need to do.


    Any other ideas?

    I have a worksheet that represents the monthly totals, and worksheets 1.xls - 31.xls that represent the days of the month. What I need to do is simple, but getting there is the problem. I need to simply add the data in the same cell on each of the spreadsheets to total on the monthly spreadsheet. For example, on monthly.xls, in cell A1 there would be a link adding [1.xls]Sheet1'!A1+[2.xls]Sheet1'!A1+[3.xls]Sheet1'!A1...and so on through 31.xls.


    Is there any quick way to setup these links? I will need to do this several times, not just for one cell. Is there perhaps some kind of macro/vba script that can accomplish this?


    Thanks...

    In a range from D2:D50 there will be page numbers. They can be random (i.e. - 2, 3, 5, 10, 11, 12, 15, etc...). What I am trying to do is run a loop script on this column of numbers, once per number, until it reaches the end. Can someone please fill in the blanks for me on this one?


    Do
    Sheets("All Data").Select
    Range("D2").Select
    Selection.Copy
    Sheets("All Trip Sheets").Select
    Range("H2:I3").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Loop


    I don't know how to do "Do..Loops". The basic function I'm trying to accomplish is to copy and paste the page number from "All Data" into the "All Trip Sheets" page, into merged cell H2:I3. It will then print that page and move to the next number in the list.


    Any ideas?

    There are two solutions to this that I can personally think of off hand...


    1.) You can do a find/replace on the budget workbook. For example, if the original names were "Budget" and "Costings", and you renamed each to "Budget1" and "Costings1", you could simply find/replace "Costings" with "Costings1" in the Budget1 workbook.


    2.) Open both workbooks. Since Budget links to Costings, go to the Costings book first. Go to "Save As" (not just "Save") and choose a different name. Your Budget book should now auto-update the functions to the new name of Costings. If you wish to keep the orginal Budget book as well, but keep the links to the new Costings book, "Save As" the Budgets book as well to a new name.


    Hope this helps....

    How can I format a cell whose value is simply a number that represents the total number of minutes into an hours:minutes expression?


    For example, if the original cell value is 145, I would want it to read 02:25 for 2 hours, 25 minutes.


    Thanks in advance!

    Can anyone help? I'm fairly certain that Excel has some database functions built into it, but I'm not exactly sure on how to use them. Here's what I've got going:


    Column A contains 150 varying account numbers. Columns B - H contain data about those account numbers (drive time, service time, etc...). How can I use the built in database functions to say...get the average of all of the entries in the column A for one particular account number? Sum all? Count all?


    I've tried using the helper with this, but it just isn't doing what I need it to....

    I'm not even sure if I can explain this problem properly...but I'll try.


    Excel is only recognizing numbers as text, and this has only begun as of today. Yesterday, I had a series of numbers that would be sorted by a macro. I hit the button, they would be placed in sorted order...all would be well. Now, the sorting isn't working properly. When I sort manually, it says something like there are some fields formatted as text. I can even highlight all fields, format as number, and try to resort, but get the same error.


    Furthermore, whenever I paste values into my worksheet, I have to go through each cell, clicking into the formula box and hitting enter to update the value before it will correct the formula result. Yes, I do have automatic calculation on...that's the first thing I thought of.


    Please help! None of this is making sense!!