Posts by Emmy

  • Copy rows to specific sheets based on a condition

    Re: How to copy rows based on a condition


    In my experience looping through a column of figures and copying rows of data one row at a time takes a long time. My initial thought would be to sort the data by column I and then you would only need to copy twice, one for each criteria to the separate worksheets. Would you mind the data being sorted or must it remain untouched?

  • Autosize Rows when spreadsheet varies in size

    Re: Autosize Rows when spreadsheet varies in size


    Is the data always all together and always with a value in a column? If so you could use:

    Code
    Range("A1", Range("A65536").End(xlUp)).Rows.EntireRow.AutoFit



    Basically finds the last row (in Column A) with data in it and autofits from Row 1 to that row.

  • Generating a report in Excel

    Re: Generating a report in Excel


    Please look at the attached. I've updated it so that as you add more data to the main tab that will be shown on the print out tabs. Let me know if this isn't what you want and you'd rather have sheets with static data which are updated as and when you choose to. I've also changed the printing as you requested.

    Emmy

  • Generating a report in Excel

    Re: Need Help Generating a report in Excel


    I only have Excel 2003 and am not familiar with the differences between the two types. However I've attached a sample xls which might work for you.

    If you double click on a customer in Column A on Sheet1, the data is populated onto Sheet2 and printed to the last used printer. I've used a mixture of VBA code and formulas.

    Let me know if this is what you're after or not.

    Emmy

  • Join Strings, Into Single Cell, Which Appear In 2 Lists

    Hi,


    I am looking to populate a cell with a string of all the values which match two criteria.


    To explain more fully I have attached a xls which has two worksheets. The Company Meetings worksheet which lists all the meetings, the important data (highlighted in yellow) is the location of the meeting and who hosted it. The Summary worksheet has a table which I would like to populate, for each person, a list of locations which can be found in the list of countries (D2:D70).


    For example, looking at the company meetings tab, George has visited various locations but only Austria, France, Germany and Italy show up on the list of countries. Therefore this is the result I would like in cell B2. And so on for the others...


    I have had an idea on how to do this which I have been working on but I doubt whether this is the easiest way so I'm open to other ideas but to explain my current thought process:


    - Use SUMPRODUCT formula to see if each person has visited each country in the list
    - Replace the "-" with "" and sort
    - This gives me a list of countries each person has visited
    - I have named dynamic ranges for these


    I have written code for this which seems to work fine if a little slow on populating the formulas but I'm now at a loss on how to get this list into a cell. Looking on various forums I've found that you can put this into a list or a combo box but not as a string in a cell. One thing I found which seemed to be what I needed used the Join function but I couldn't get this to work with my Named Ranges...


    Data in the xls has been changed to protect the guilty ;)


    Any help would be greatly appreciated.


    Thanks,


    Emmy

  • Unhide Picture If Cell Contains Value

    Re: Unhide Picture If Cell Contains Value


    Hi Dave and Junho,


    Thank you very much for taking the time to help me.


    Dave - I understand that this xls may not conform to Excel Best Practices but this is what I'm stuck with.


    Junho - sorry I'm not sure I understand what you mean. Would you mind posting your example so that I can see?


    Many thanks,


    Emmy

  • Unhide Picture If Cell Contains Value

    Re: Unhide Picture If Cell Contains Value


    Hi Dave,


    Thank you very much for replying to my problem.


    I'm not entirely sure I know what you mean.


    If I used the IF and ISNUMBER statements and conditional formats (for the borders) then the data would be a little all over the place (see the BUYS (2) tab I have inserted). Though I agree that this does solve my problem the layout isn't great. My previous attempt was doing exactly this but I put more columns in to get the layout I wanted and then merged the cells above so that they would have the existing column widths. The problem with this is that it messed with other things on the xls because of the merged cells (I find merged cells isn't very good practice when dealing with data). My next idea was to have pictures of the cells on the warehousing tab if there's another (better) way of doing this I'd be very grateful to hear it.


    If I have totally misunderstood what you mean would you mind clarifying?


    Thanks,


    Emmy

  • Unhide Picture If Cell Contains Value

    Hi,


    Thank you very much in advance for your help.


    I have attached a simplified version of the xls I am using. On the attached xls there are two tabs. The first tab (BUYS) has linked pictures of cells on the second tab (Warehousing).


    I would like some code which would display the pictures only when the corresponding cells in Column F have a number in it. (This number could be anything)


    e.g. If cell F4 has a number in it show Picture 1
    If cells F4 and F5 have a number in them show Picture 1 and 2


    My full xls (not attached) has another tab called SELLS. These two tabs (BUYS and SELLS) have 15 pictures each (1 for each row 4:18).


    Ideally an exception would be written into the code so that if the xls was named "yymmdd Asian Executions" or "yymmdd Europe Executions" none of the pictures would show up.


    I hope I have been clear but it's very difficult to be objective so please let me know if you need any more information.


    Thanks again,


    Emmy