Posts by JimFuller1

  • Links To Missing Sheets

    Re: Links To Missing Sheets


    gerardf,


    I can't figure out what you need. Do you want to insert a new worksheet and have it named something besides Sheet1? Can you explain more clearly what you want and what problem you have in getting it?


    Jim

  • Chart Not Always Updating

    Re: Charts Fail To Update


    cr07,


    Since you are already using named ranges for the charts, I'm going to guess that there will not be a way to force a re-evaluation of what the range covers. It just covers it automatically.


    Even though you have a huge amout of data and formulas that are too big to post, we could still benefit by looking at the kind of formulas you are using. Sometimes there are more efficient ways to do things that will speed up your workbook. Just trim out most of the data and leave the formulas (substitute bogus stuff for the sensitive stuff) and post a small sample.


    Jim

  • Repeat Last Row Of Each Page At The Top Of Next Page

    Re: Repeat Last Row Of Each Page At The Top Of Next Page


    nikma,


    I think you will find you can only specify one contiguous range to repeat at the top of a page using page setup. If you can't, you may have to insert rows that duplicate the row above. This can be automated but would require knowing the page breaks in advance which is beyond my VBA skills. Try searching page break because I'm sure someone knows.


    Jim

  • Multi-column Data Validation

    Re: Multi-column Data Validation


    cymro,


    Since you only want to use Data Validation, I'm going to say it can't be done because either the list does or does not include the name. If the name is in the list, it can be chosen. If you have to change the name to the initials after the name is entered into the cell, you're no longer talking about using just Data Validation, you're talking VBA. Also, I don't think this is a very good strategy because if the list is more than a few names long, you're going to run into people with the same initials.


    A work around is to let the Data Validation control the full name and use a separate column for a lookup of the initials. The full name can be hidden for brevity.


    Jim

  • 2-dimensional Date Range With Dynamic Copy And Paste

    Re: 2-dimensional Date Range With Dynamic Copy And Paste


    Quote from epynephrin

    Maybe I've just never seen an actual Excel database layout,


    There is one attached. It has one record for every date a course was taken. The one record tells you everything you need to know. Your layout includes more than one piece of data for each record which is causing all your problems.


    Quote

    but based on the few I've seen I'm feeling like I've missed something. Is there a better way for me to track this whole thing, then, and I've just been too stupid the whole way along?

    Don't lets start calling each other or ourselves names. The purpose of the forumn is learning and that includes me too.


    Quote

    At the moment, I have a system that seems to work; I'm not sure it's perfect. Considering I knew nothing about VBA and a bit about Excel when I got into the project, I'm sure there's always a way to make it simpler--hopefully a little more idiot-proof than usual.

    You are probably correct that changes to what you have already will seem easier than starting over. The lesson learned will not go to waste however next time you get a project to work on.


    Quote

    Here's hoping I don't feel ridiculously dumb if/when I find out that there's a free Excel thing that does everything I've manually programmed for me.

    You won't as long as you view this as an investment in learning.


    The sample I attached is short. You may want to take the data you posted and convert it all to the layout I've suggested and just run a Pivot Table on it and see if you can now get what you want, or more of what you want, before you have to do any programming with VBA. That's when the layout pays off. When you've automated all the built in functionality and have to start creating a new wheel. Let me know what you think.


    Jim

  • Comparing Two Columns

    Re: Comparing Two Columns


    You could use a dynamic range name for each column of data. One caution, for sumproduct to work, both ranges should be the same size. This can still be done as long as you can know which column has the most entries. Someone really good with formulas could probably do it without knowing up front which column is the longest.


    Jim

  • Comparing Two Columns

    Re: Comparing Two Columns


    billyj,


    Assuming you want to compare columns C and G:


    =SUMPRODUCT((G2:G187=C2:C187)*1) for complete cell comparison
    =SUMPRODUCT((LEFT(G2:G187,3)=LEFT(C2:C187,3))*1) for first three digits


    See if that will get you started.


    Jim

  • Finding Duplicates

    Re: Finding Duplicates


    Super Simple Solution:


    1) Put one list in a sheet next to the other list.
    2) Make sure both lists have the same column widths.
    3) Make sure both sheets are set at the same zoom level.
    4) Toggle back and forth (CTL+PGUP ... CTL+PGDOWN) between the two really fast.
    (You may be amazed at how good this works for identifying diffs if you've never tried it)


    Jim


    PS - I don't know if there's a better solution since you haven't answered my questions...

  • Finding Duplicates

    Re: Finding Duplicates


    Nichola08,


    I can't tell if I got number 1, 2, or 3 correct.


    If the 060107 list is the most current, why not just use it instead of trying to find the differences to add to the third list?


    Jim

  • 2-dimensional Date Range With Dynamic Copy And Paste

    Re: 2-dimensional Date Range With Dynamic Copy And Paste


    epynephrin,


    Soapbox Warning!!!
    I sure you thought that layout for the data made sense but, what you have done is complicate your life beyond what is necessary. You're right, there is no way to have a Pivot Table do what you want BECAUSE of the way the data is layed out. I'm sure the whole reason you're having these long posts is because of trying to work around data that is layed out wrong.


    If you just used common database layout, built in functionality like Pivot Tables would do most of what you want without effort.
    Stepping down from Soapbox...


    Sorry I couldn't help.


    Jim

  • Dcount Function/Formula

    Re: Dcount


    Nichola08,


    Did you try the formula I gave you? Do you know how to do a Pivot Table in Excel? Don't be too hasty. Both will work. You'll have to edit the formula for your purposes, but it will work. Experiment and post back.


    Jim

  • Finding Duplicates

    Re: Finding Duplicates


    Nichola08,


    Let's break this down into steps.


    1) Compare 053007 to 060107 and find out if any person on the 053007 list is left off the 060107 list.
    2) Compare 053007 to 060107 and find out if any person on the 060107 list is left off the 053007 list.
    3) Combine the two results from above and copy and paste them into a third list from which we will do our reporting.


    Is the above correct?
    How are you going to know to delete from the third list?
    Is the 060107 list the most complete list?


    Jim

  • Dcount Function/Formula

    Re: Dcount


    Nichola08,


    I think Dave's advice would be to use a Pivot Table if the list is long and you want to cover all the bases quickly. My number two choice would be a formula like below.


    =SUMPRODUCT((A1:A9="jp")*(B1:B9="RET"))


    Third on my list would be DCOUNTA (for text) as you asked and you can hover over the linked word in your post to get a good example of that.


    Jim