Posts by def98

    Re: Format

    Hmmm, not sure what is going on here. Would be a big help if you post a small example of your worksheet.

    Does worksheet have headers?

    Re: Conditional IF formula

    Hi Sam,

    You can work this a couple of different ways stepping through an IF statement and doing the next step if it evaluates to true, or something else (i.e. nothing) if false, etc. etc.

    For the LOOKUP part, your data will need to be arranged vertically down the sheet and must always be sorted in ascending order, more or less as follows:
    A B
    0.50 $150
    0.75 $250
    1.00 $450

    You can use multiple sheets with LOOKUPs built into formulas. In other words, your evaluation data could be entered on sheet 1 and your answers (prices) would be returned on this sheet also. The LOOKUP table could be on multiple sheets in the same workbook or together on one other sheet in the same workbook.

    The Ms XL help on both the LOOKUP and IF functions are good to get you started. You may want to look at the AND function also and give it a go.

    Is there a list of values/factors for the clarity attribute?

    Re: Data Validation with Formula


    Your formula did the trick. I had not worked much with data validation and formulas before, so this was very beneficial for me. Thanks very much.

    Hats off to all the folks that contribute to this forum, especially the wizards that take on coding and other tough challenges, or whatever we throw at them.

    Well done!

    Re: Basic "If, Then" Formula

    Agree with Norie, a posted example would be a big help. In a quick review, it appears your third IF(AND... should reference F7<F16 (not <F15) but this may not fix it.

    Re: Data Validation with Formula

    Derk et al:

    Thank you for your responses. I tried this in DV and I must be missing something simple as it gives me the warning message no matter what is entered in the sequential cells. Same results I experienced when I initially tried DV myself before posting. Could there be a setting in XL that needs to be turned on or off?

    Any sequence change from Red except for Red and Black needs to show warning message. In laying out this example for the post, I realized we should also allow "P55" which is a total clean down. And, Black should always be followed by P55 to do this right. So, updated criteria follows:

    Red to Red = valid
    Red to Black = valid
    Red to P55 = valid
    Red to any other color = not valid (warning!)
    Black to P55 = valid
    Black to any other color = not valid (warning!)

    Thank you for your time to assist, as always.

    Good Day Folks:

    Is data validation a good solution for the following or should I try code?

    I have a production schedule and based on the color change sequence on items going down the worksheet, I want to provide an error message (warning, not a hard stop) to prompt that a "Purge" is required. Need soft message only, no auto-population of data is required. Have done this in the past with an =IF formula and text message in the worksheet but a recent change in the layout by Production now makes this problematic.

    Previous worksheet formula that worked fine:

    Stepping down the production schedule worksheet:
    If B6=Red and (skips one row for each item)
    If B8=Black or Red, then nothing/OK
    If B8<>Black or Red, then "Purge" is required (need warning message)
    Repeats and can expand to about 30 rows total with color sequence changing constantly.

    Not familiar with DV and have not gotten a formula to work with my first few attempts, so any assistance would be appreciated.


    Re: Selecting rows with specific values in a column


    Thank you, thank you, thank you! I just ran your new solution a number of times and then added a few more orders (rows) and ran it again and all works great so far. :rock: I will test it a bit further in the next day or two and let you know the final outcome. Only sorry I did not define it better to reduce your time. The work and guidance available on OzGrid from member's like yourself is amazing and a real treasure in today's fast pace/got no time world. Again, thanks very much for your good work.

    All the best,

    I will also study the two code listings (old & new) and my many failed attempts in between, as this has been a tremendous learning experience for me. Should only take another 5-10 years for me to generate a message box or similar with VBA!

    Re: Selecting rows with specific values in a column


    In summary, I need a workbook for the following:
    each Origin within a Destination (which could/will have multiple Carriers)
    each Carrier within a Destination (which could/will have multiple Origins), and
    a complete Destination list (per auto-filter before macro is run).

    You have all except the Origin lists/workbooks covered in your previous good work. I do not need a worksheet for each Carrier within each Origin or vice versa, just all orders (rows) for an Origin and all orders (rows) for a Carrier, within the selected Destination.

    The activity (sort/filter and set-up new worbooks to e-mail) is the same each time a set of orders are received, scheduled and completed per Destination (customer). Everything works from within the selected/auto-filtered Destination, then we create the Origin copies/workbooks, the Carrier copies/workbooks and finally a complete copy/workbook of all orders for the selected/auto-filtered Destination. The sequence of the sorting and new workbook creation is not important, so if it is easier to do them one way or the other, go for it.

    Thanks again for your time and efforts on this - it is really appreciated very much.

    Re: Selecting rows with specific values in a column


    Sorry to bother you again on this but I have tried to work through it off and on since our last thread exchange. This has really helped me to begin to understand (keyword is begin) a little about vba code and I am grateful for that.

    The code works from the auto-filtered Destination fine. It also filters, loops and copies the Carriers lists and the complete copy of the Destination data to new workbooks correctly :rock: . However, it is not generating the Origins lists. These should process separately the same as the Carriers lists do and be copied to new workbooks (Step Action #2 in my earlier post with the details - FYI only).

    I tried modifying the nested loops and even tried two separate loops but cannot get it to work correctly. Thank you again for your time and effort on this.

    Re: Selecting rows with specific values in a column

    Thank you very much for your effort - abso' the best yet on this little project of mine. Now I think the light at the end of the tunnel is not just another train! I have tried to "tweak" your code to get exactly what I need but was only partially successful and need some further help please.

    All of this works within a desired Destination (Dest Code), so when we run the macro the auto-filter is on and the desired 'Dest Code' has been selected/filtered in Column E. In other words, we need to process the sorts, filters, copies, etc. based on only one Destination at a time, which the operator will select first via auto-filter. If we set it up to run everything on the active sheet the repetition/duplicate copies is excessive. Can we work with this and leave the auto-filter on at the end or do you suggest changing our operator's routine?

    You nailed the heading cell width copying problem I was having. This and the Adv Filter loops, row data, etc. look great but I need slightly different output results. First, I need one complete copy (new workbook) of all rows (orders) for the selected Destination. Second, all filtered copies created should be in new workbooks - not worksheets, for easier e-mailing and separate filing.

    Don't worry about the e-mail (Lotus Notes) bit at all. You are certainly doing more than enough to help me with the code needed above. Please don't feel pressured on time either. As they say in Cornwall, you can get to it "drectly". Your assistance is greatly appreciated and I will wait.

    Re: Selecting rows with specific values in a column

    Brilliant work for ECD! And very similar to something I have been trying to crack (see below please) for a while. If I should do another post altogether just let me know.

    Apologies for my earlier comments about confidentiality - don't know where that came from?! I just re-read your thread above and guess I crossed it up with another thread in the wee hours o' the morning. Sorry about that.

    I have a worksheet for shipping schedules and need code to loop through multiple cell values in three columns and copy the entire rows selected to a new file in the same worksheet format (cell widths, headers, etc.). We do this sort and file creation today manually using the auto-filter, copy and paste in xl2000. Worksheets have 18 to 20 columns and can grow to about 400 rows. We format everything as text currently, use the auto-filters constantly and each row represents one shipping order. The new worksheet needs to be be named, e-mailed via Lotus Notes :yikes: and saved in a specific folder on our local network. The original/master schedule worksheet will be left open as it was (auto-filtered on chosen "Dest Code") and will be saved manually later in a different existing folder.

    The filter/sort criteria is simple (origin, carrier, destination) but I have not been successful in writing the macro to include all possible occurences (origin, carrier, etc.) if they are present and still run if some are not present, correctly. Unfortunately, I am not yet experienced in code and I cannot make it work with just a macro. I either come up with orders (rows) missing or excessive blank worksheets/files being created.

    Step Actions:
    1. Upon completion of initial entry of a batch of orders, auto-filter is on and desired 'Destination' is selected/filtered.
    2. Step through 'Origin' column (i.e. loading points in column 3), filtering and creating a new file for each different value or origin. Name file ("Origin" & Week # "WK48") and set-up as an attachment in a new e-mail message for Lotus Notes (V6.5).
    3. Then step through 'Carriers' column (column 16), filtering and creating a new file for each different value or origin. Name file ("Carrier" & Week # "WK48") and set-up as an attachment in a new e-mail message for Lotus Notes.
    4. Then create a new file for the 'Destination' column value already selected/filtered. Name file ("Dest Code" & Week # "WK48") and set-up as an attachment in a new e-mail message for Lotus Notes.
    5. Return to original/master file worksheet with auto-filter selection (chosen destination) displayed as when started.

    Operator will then manually address each e-mail and send. My next step will be to try and automate this to address the e-mail also, if possible.

    Would appreciate some guidance.

    Re: Copy and paste more than formats


    Thank you for your guidance. I interpret your code to set all column widths at 8. I was trying to do this without code as that is a threshold I have not crossed quite yet (although this fantastic forum is pushing me there). The column widths are not all the same across the worksheet, column A is 16 but the rest could be standardized at 12 for now. Thanks again.

    Re: Selecting rows with specific values in a column

    Hi ECD,

    You indicate the row data is dynamically updated but what about the data you need to sort/filter by (in column C). Can we assume this data is consistently placed in column C and the data imported is always within the criteria you specify to sort/filter by?

    Sounds like you may need a dynamically named range in the macro to make this work. I think we all respect confidentiality but it is difficult to help from an arm's length on detailed/complex challenges. Can you perhaps copy a small representative section of your data in a workbook and modify it to ensure confidentiality? This would be a tremendous help.

    Re: Copy and paste more than formats

    Thanks TJ,
    I have tried Paste Special and it does not auto-size the cells, unless I am missing something. Can get formats, borders and values - no problem, but the cells in the new workbook stay at the default width of 8.43 and I need them to auto-fit the data widths being copied.

    Running a copy routine to a new workbook and need to copy the data and the formatting and the column heigths and widths, etc. Seems to me I should not have to manually adjust cells to fit data after paste. I have messed around with copying Styles but cannot seem to get this to work correctly for me. I am sure this is simple and down to operator error (that would be me!) but your help is greatly appreciated.

    Re: Format row on change in A:A


    Try the attached revised sample file. Your CF formula "=IF($A2<>$A3, TRUE, FALSE)" was referencing the cell below, so when the sequence/value changed it was shading the one above. With the CF referencing the cell above "=IF($A3<>$A2, TRUE, FALSE)", the cell/row where the change occurs is highlighted. I know that you mentioned you tried switching the 2 & 3 previously without success, not sure what happened but this worked for me.

    Re: Format row on change in A:A


    Sorry this is not working for you, sure seems it shouldn't be too difficult. The first row worked for me but the default reference is to $A65536. Therefore, if A1 is populated, it will always be shaded if A65536 is empty. You can change this to <>0 but the effect is almost identical. The first empty cell (at bottom of entered data) is shaded also since it is not equal to the last populated cell.

    I assume you are not doing anything to lose/overwrite the CF during your data entry, right?

    I have attached a small example FYI. Mark, perhaps you could post an example of your problem worksheet for us to work on.

    Re: Word


    Your post subject is "Word" but form your description it appears you are using XL. Which is it - Word or Excel?

    Do you want to display the text "Low", "Medium", "High", "Exorbitant" in the profit analysis column cells based on your criteria?