Posts by def98

    Re: Create A User Friendly Sheet Index For Worksheet Navigation

    Thx Dave, I certainly do follow your guidelines most of the time and recommend them to others also but I felt the business scenario dictated the multiple sheets (partially due to change impacts / resistance). However, I foolishly was not aware of that right-click on the scroll arrow functionality - that is a great tip! Many thx. Thread is solved.

    Want to go to a particular sheet in the same workbook by selecting the sheet name from a list in sheet 2 and clicking a command button. Sheet 2 has a list of all sheets in the workbook and sheet names are in two cells in column A & B (e.g. tdm-216). User to select two cells which are the desired sheet name (all sheet names are unique) and then click macro to jump to that sheet in the workbook. There will be over 100 sheets in the workbook.

    Re: VBA Macro To Convert Sheet To Values Only & Rename

    Dave, thx for your response, but I think my post was not clear or perhaps incomplete - my apologies. I did some research on "UsedRange" and it is certainly more efficient but still not working for me.
    Need some help please with:
    Step 2.a - to make the paste special work for text and formatting only
    Update: This relates to copying sheet 1 (master form, Range A1:N58) to the back of the workbook but without formulas or data validation (values only?), if possible.
    Step 2.b - the sheet/tab renaming (e.g. "0213-100") as it is currently retaining the name of the master form.
    Update: This relates to renaming the above saved sheet with the text in L1 and M1 combined (e.g. "0213-100"). For each sheet logged and saved to the back, the number (sheet name) will increase by +1, so it will be unique with each sheet saved.

    In a workbook I have a master form (sheet 1) and a summary log (sheet 2) and behind these I am saving copies of the unique completed forms per event / incident.
    1. User enters data on sheet 1 (master form).
    2. Click macro button to copy select data to sheet 2 (summary log). Code not shown.
    2.a Copy completed sheet 1 with unique # in L1:M1 (e.g."0213-100") to back of workbook via paste special to only copy text and formatting (1 form snapshot per event / incident).
    2.b Rename copied sheet/tab (now sheet 3+) with the unique # in L1:M1 (e.g. "0213-100").
    2.c Clear master form and assign next sequential # (per # list in log), ready for next use.
    2.d Save changes.

    Need some help please with:
    Step 2.a - to make the paste special work for text and formatting only
    Step 2.b - the sheet/tab renaming (e.g. "0213-100") as it is currently retaining the name of the master form.

    I have a workbook with two worksheets. Worksheet #1 is a form that will be populated with data and saved as a new worksheet, then cleared and used repeatedly as a master form. Worksheet #2 is a log / register of the unique forms completed and saved from the master each time. I need to assign a unique sequential # to each form when it is saved and record this number in a column on Worksheet #2 (the Log). I am using some macros for the copy work but struggling with the auto-numbering of the forms when completed and saved.

    Re: Insert Line Break Via Formula

    Thx for your reply Andy but I am missing something. Here is a formula example =Inventory!E4&Training!E4, pulling two tasks from two diff worsheets into my lookup table. I need the new line either before or after the "&" but keep getting errors. Thx again

    I am referencing tasks (text only) from multiple worksheets by date into a lookup table. The dates are listed in column A with tasks in cloumn B. I am then using vlookup to pull the tasks to another worksheet formatted as a calendar. All are in the same workbook. Some dates have multiple tasks from different worksheets. I am using "&" to concatenate and place all tasks (text) into the respective cell per the date. I need to insert a line break (similar to Alt + Enter) between each task to start a new line each time within the same cell in the calendar worksheet. Is there a way to do this with a formula? Thank you

    Re: Counting days within a given date range

    I do similar work using the NETWORKDAYS function in a number of scheduling/tracking worksheets in XL. This works best for me because it accepts variable holidays (non-working days) in addition to the start/end date and automatically excludes weekends. Below is partial text from Ms Help on this topic - FYI.

    from Microsoft XL Help...
    Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays. Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days worked during a specific term.
    If this function is not available, run the Setup program to install the Analysis ToolPak. After you install the Analysis ToolPak, you must enable it by using the Add-Ins command on the Tools menu.


    Re: cells displaying formulas rather than results


    I doubt this is your issue but you may want to check the formatting for those problem cells. I have had this happen when a cell was changed to "Text". If this is the case, I would select "General" form the formatting menu and you may need to re-enter the formula.

    Re: Sum when filtered


    Very handy SUBTOTAL formula - thanks for that.

    Curious if something similar can be done with one of the COUNT functions? We use filters excessively in a simple XL scheduling worksheet and frequently need to count the rows that are filtered by product, line, date etc. We currently go into Tools/Options/Calculation tab and select "Manual". This counts the # of rows filtered and displays the quantity in the bottom left corner. However, we have to switch it back to auto calculation for other formulae to work the way we want it to on the fly.

    The # of rows varies from 20 to over 200 in a typical weekly schedule and all have a standard header. It would be handy to have the count (per filtered rows) displayed in one of the header rows. Thx.

    Re: Inventory Addition on a Form

    Welcome to Ozgrid! You should try a search on the word "inventory" (on the dark blue drop-down menu bar) and see if the related posts answer your questions. Failing that, posting a small example of your worksheet(s) would be much more efficient for the great team of wizards on this forum to help.

    Re: Count/sum with two criteria from multiple sheets


    Thanks very much for your research efforts and input. Unfortunately, I could not get the examples to work for me. Even tried the one from Thomach and couldn't make that work either. :? I have reverted to sub-tables on each sheet to summarize the local figures first and then I'll pull the totals to the monthly summary worksheet. Extremely basic but it works.

    Do you have experience with the MoreFunc 3D add-in? I am not familiar with it but the examples look interesting and I may have a number of applications for the functionality.

    Thanks again for your time,

    I am trying to count data from multiple sheets based on two criteria. The sheets are all formatted the same and represent shipping schedules. The first is a text match to a specific customer name and the second is to count based on a numeric value which can be -1,0,1,2,3+ representing order lead time in days. The lead time is calculated for each row on each worksheet per NETWORKDAYS function based on order date versus ship date.

    Sheets 1-4 are the schedules and sheet 5 is a summary sheet to measure the order lead time per week and per customer. On summary sheet, column A has customer names, column B is total orders for that customer from sheets 1-4 (Weeks 1 – 4). Column C needs to display # of 0 days lead time orders, column D needs to display # of 1 day lead time orders, column C needs to display # of 0 days lead time orders, column E needs to display # of 2 days lead time orders, column F needs to display # of 3 days or more lead time orders. On the summary sheet, the upper table displays only the order lead time data in the respective columns in weekly buckets. I need the lower table to display the data per customer in the respective columns (0, 1, 2, 3+ days lead time). I also need it to tally MTD (Month To Date) and to ignore if column F is blank even though -1 shows in column I due to formula. See Week 4 on summary sheet – 12 displayed in column C but should be nothing/blank as there are no orders in Week 4 yet.

    I have tried SUMPRODUCT and COUNTIF and even some nesting but I cannot seem to get the syntax or function combinations right. I got it to work on one sheet but not multiple sheets. Thanks in advance for your kind assistance.


    Re: Copy cell data to next row on other sheet


    Thx, this looks workable with a few tweaks. I understand that typically one would want to post all the other details (units, cost, etc) but we have a system that handles the regular, higher volume stuff. This is for one-off or unusual consulting items and a separate file will track special claim invoices, also one-off or unusual incidents that we do not want to set-up in our main system at this time (both are low volume activity). This log is intended to be a summary record, so we only need basic data for tracking. Hard copy records will be kept elsewhere for the detail, if needed.

    Thanks again for your time and assistance.


    I need to be able to run a macro (Log_Invoice) each time a new invoice is completed, to record certain cell data on an Invoice Log within the same workbook. Then create a new worksheet (named "new invoice") with all data fields cleared, ready for the next invoice.

    I have recorded the macro in the attached file to get started but do not know how to check the Invoice Log and only copy selected data (cells shaded in yellow) to the next blank line. The Invoice Log will not be cleared and current thinking is to retain soft copies of completed invoices also.

    Thanks in advance for your kind guidance.

    PS - If you clean-up the macro code and have the time, feel free to comment on your improvements for my learning benefit. Thx

    Re: Format


    I added auto-filters to the file Shades re-arranged for you. As you can see manipulating data in XL is much easier when layed-out in this manner. With headers in bold text or some distinguishing format, XL recognizes them and will sort accordingly without the maddening scramble issue.

    To add/turn-on filters, select your header row and click Data on the menu bar, then click AutoFilter. You can also remove them in a similar manner. Then simply click on the AF button in the column that has the data (i.e. date, company) that you want to sort by :) . This can then be printed or copied into an e-mail as you wish.

    A few points:
    1) When doing regular sorting you can misalign your data if you neglect to select the entire range. If you do not realize this and hit the save button, it cannot be undone except by you manually :? .
    2) When using AF, if you filter the worksheet data to a selection, say by Company B, and then attach the file to an e-mail, be aware that you will be sending the entire file with all of your data. if the recipient takes off the filter selection, they will see all data on the worksheet. Copy and pasting the filtered data only into an e-mail sends only the selected data.