# Posts by def98

• ## formulas

Re: formulas

Happy New Year Gator,

I use a very similar form in XL and have a small 'table of assumptions' located outside (below) of the print area for the invoice document, which I print and mail. This way I can update different rates in the table as they change but the formulas are in the cells within the invoice above, so they continue to calculate correctly when the hours are entered.

If you submit your invoices via e-copy, you may want to use 'Paste Special' and select paste values, to not send your invoice with formulas, etc. If you print and send hard copies, like me, this is a non-issue.

• ## Time in formula

Re: Time in formula

EastTexas,

As pointed out above, you will need to work with decimals or hours and minutes (time formats) consistently for this to work. If you do not have a solution yet, it would be helpful if you could post an example of your work.

• ## Time difference including another day

Re: Time difference including another day

Mohammed,

My experience has been that to calculate time values across a 24 hour threshold properly in XL, an elapsed time format must be used (i.e. dd/mm/yy_[hh]:mm). Otherwise, XL does not know you are changing dates based on times values only. Unfortunately, this means the date and time must be entered (in the same cell with colon), which takes a bit longer.

• ## lost toolbar...

Re: lost toolbar...

Click within any worksheet, go to Tools, Options and click on the View tab. Click in the box next to Formula Bar to check this option.

• ## Conditional Formatting

Re: Conditional Formatting

Far,
Rather than a formula, try using "Cell Value Is" in Conditions 1 & 2 and select from the drop down menu options as follows. For the 2nd CF, simply click on "add" after completing the first CF.

Cond 1 Cell Value Is "less than", then enter 223 and format Patterns to green.
Cond 2 Cell Value Is "greater than or equal to", then enter 223 and format Patterns to red.

• ## Displaying times that span midnight

Re: Displaying times that span midnight

Cindy,

You have many options and good feedback to check out - I hope one of them solves your problem. We use a scheduling tool daily that might be of interest and I have attached a partial copy for your review. Dave's feedback is certainly valid and I agree with Tomach that using the full date and time makes it very straightforward and is perhaps the best choice.

We use the 24 hour clock and run schedules out around two weeks. We must constantly reset our schedules based on 'actual' production events/results. The attached file is set-up so you simply enter the full date and time of the 'reset' (cell A5) and it recalibrates the schedule in half-hour blocks, two weeks out. It also highlights the occurence of midnight (00:00) each day in green via Cond. Formatting. Try the file yourself - enter full date, space and the time (dd/mm/yy_hh:mm) in A5.

We use another on time performance measurement report in XL that spans multiple days and it will not calculate elapsed time correctly without the full date and time formatting as well. I selected 'Custom' in the formatting dialogue box and entered the desired format for our purpose as noted above (also see comment box displayed on worksheet).

• ## Count Based On Criteria In 2 Columns

Re: Count based on criteria in two columns

Ranger,

Thank you sir, SUMPRODUCT worked fine - gave up on COUNTIF for this one.

• ## Count Based On Criteria In 2 Columns

Need to count from a multiple column table based on criteria from two columns. Getting close with COUNTIF but struggling to properly insert the second criteria (nesting?).

First COUNTIF is based on column C (\$C\$7:\$C\$250) and references text value in "XXXX", which works fine. Need to also include second criteria, in same COUNTIF, from column I (\$I\$7:\$I:\$250) which would exclude counting if "CANCEL" was found in column I. Many thanks.

• ## COUNTIF based on date

Re: COUNTIF based on date

mvillarr,

I tried the SUMIF function and it appears to work fine (see attached). RoyUK's idea of using a Dynamic Named Range would be a good idea, else you will need to drag this through a large range to accommodate future entries.

• ## Permissions and sharing of an Excel file

Re: Permissions and sharing of an Excel file

Averykess et al:

We have many Excel files on our intranet that are shared. Some with restricted access (i.e. Read Only) and some unrestricted. We have never found a satisfactory way to share these files with Read & Write access. If there is a reliable way to do this I want to learn of it also - please post.

I understand Ms Access can handle multiple users with Read & Write permission and perhaps this could be an option for you. With a full spectrum of Excel experience/competency levels across our intranet and almost no level of ability in Ms Access, we elect to stay with Excel and the sharing restrictions.

Alexanderd,

Here is the 'Inventory-B' file. Not sure if 45 KB limit is total for multiple attachments or for each.

Another point, 'Inventory-A' is set-up to be the worksheet where items are added or deleted. The two items shaded in yellow were added as an example. Stock item and location will remain for zero balance items unless they are manually deleted or updated in worbook A. 'Inventory-B' can be expanded to your 300 rows with a highlight, click and drag.

Very handy to tile these two files so they share the screen and view them both as you test the data entry and observe the current balances.

## Files

Alexanderd,

Attached are a couple of file examples that function similar to what I think you are asking for. However, working these out raised more questions around multiple access (working in separate files helps this but may not be the answer overall) and transaction volumes (receipts & allocations). It also required a 'close' routine/event to be included. I recorded a small macro in the 'Inventory-B' workbook to set the opening balances and clear the day's activities (receipts & allocations) which is named 'daily_close'. I am not advanced with macros and cannot write VBA code, so perhaps one of the many experts on this forum could improve on this rough draft.

With the attached worksheet layouts, the operators receiving and allocating stock will have to enter a running tally quantity. And update it accordingly if multiple transactions occur daily, which could be prone to error. Both sheets
will dynamically update each other in the 'current balance' column. If either file is open and changes are made while the other is closed, they will auto-update if you answer yes to the update dialogue box question when it appears.

Close Routine
Assumes end of day and both files are current with transactions and saved.
The 'Inventory-A' workbook should be closed first.
With 'Inventory-B' workbook open, run "daily_close macro. This will open 'Inventory-A' workbook, set the opening balances and clear the receipt quantities in both workbooks (A&B), ready for the next business day. Then save and close 'Inventory-B' workbook. Good luck.

## Files

Hi ya Alexanderd,

I tend to think along the lines that mhabib is suggesting. Like others, it would help me to see an example but it sounds like this just might be more complicated than it needs to be.

You can either create all lists within one workbook on different worksheets/ tabs (1-stock shipments/allocations, 2-stock receipts and 3-stock balances) or in different workbooks/files in the same manner via external reference. Both methods, more or less, amount to entering an equal (=) sign in the worksheet cells that you want to display the other worksheet's data and then clicking on the cells to be read/mirrored on the other worksheet. Excepting custom code, you may have an issue with 'circular references' trying to get the two worksheets to dynamically read/update one another. Can provide more accurate detail/guidance after viewing your example data.

Are the stock items truly so transient and/or different that you must take them (say, item code & description) completely off the lists each time versus simply adjusting the available balance in a typical debit/credit/balance process?

• ## Macro to create multiple files based on auto-filter/sort

Re: Macro to create multiple files based on auto-filter/sort

Derk,

I am struggling with your guidance but it is only because I am not advanced enough to follow it well. I cannot write code currently, although I hope to take classes next year and am not familiar with Advanced Filters. I will keep working with the Advanced Filters, as I need to learn their functionality regardless. I have pared down some sample data and attached an explanatory file to this message. I was hoping what I need to be accomplished could be done without coding but perhaps not.

I have written a number of successful macros for this to filter, copy and paste the desired schedule data to a new file. Worked great on a static schedule file at a point in time - took a few seconds to run and created the new files needed for e-mailing and saving. However, not all data combinations (destination, origin, carrier) were identified and when others were added later in the week, the macros did not include the new column values and the related row data (shipment).

Richie,
Here is some sample data if you want to review and comment.

• ## Macro to create multiple files based on auto-filter/sort

Re: Macro to create multiple files based on auto-filter/sort

Derk,
Thanks, I will try again over this weekend. To record the macro originally, must I have each possible criteria (each origin, carrier, etc.) listed to ensure the auto-filter/selection process always looks for all? We frequently revise existing rows/orders and add additional rows/orders and we cannot miss anything to be able to rely on the macro consistently.

It would be my intention to copy and rename the existing file, clearing the data and starting again (WK48-->WK49-->WK50). This will require updating the macro code each time to the new file name or it creates a conflict right? Is there a better selection i.e. "all open workbooks" for the macro?

Richie,
So far I could not get the file small enough and still present a good amount of sample data. I'll have a go this weekend, referencing Derk's guidance and then take it from there. Thanks.

DEF98

• ## Macro to create multiple files based on auto-filter/sort

I have a worksheet for shipping schedules and need a macro to filter/sort by multiple cell values in multiple columns and copy all full rows selected to a new file in the same worksheet format. Not all column's cell values need to be filtered/sorted. We do this sort and file creation today manually using the auto-filter feature in xl2000. Schedules are kept in monthly workbooks/files with weekly worksheets/tabs. Worksheets have 18 to 20 columns and can grow to about 400 rows. Each schedule section is added/entered by destination (i.e. customer orders). We format everything as text currently and use the auto-filters constantly. Each row represents one shipping order and that data must be kept intact on both the master worksheet and the new worksheet created. The new worksheet will be named, e-mailed via Lotus Notes :{ and saved in a specific folder on our local network.

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 but this seems it should be workable as just a recorded macro. I have recorded a number of them but cannot seem to get it right. I either come up with orders 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 #) and set-up as an attachment in a new e-mail message in 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 and set-up as an attachment in a new e-mail message in Lotus Notes.
4. Then create a new file for the 'Destination' column value already selected/filtered. Name file and set-up as an attachment in a new e-mail message in Lotus Notes.
5. Remove all auto-filters and leave master file open with active worksheet displayed.

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.

• ## Formula

Re: Formula

THC:
Not sure from your info so far if you are wanting to calculate units or boxes (or both)? However, you can keep this simple by entering a negative number for the items taken out of inventory and of course a positive number for items going into inventory and using the 'SUM' feature from the toolbar across the data entry row for a current/running balance (assuming this is the total in your column F mentioned earlier). If you want to use box quantities and calculate individual units you can set-up the worksheet that way and still use negatives and positives with the 'SUM' feature. Simply build it from boxes, to units per box, to total units in or out.

• ## Cond. Formatting based on date change

Re: Cond. Formatting based on date change

paul d k:

Zip file was helpful but I had already tried jmhans suggestion, which worked and was a little easier for me. It looks like your file would work but previously when I separated the date and hours into different columns, the date would not auto change at 00:00 when the schedule was updated/reset. So I kept things (date & time) in the same column and entered special formatting of (mm/dd/yy_hh:mm). In keeping with this I used a slightly different formula than yours for the 1/2 hour times increments (+00:30).

Thank you for your feedback and I have attached a partial copy of the worksheet (CF Schedule Tracker) for your info.

Regards,
def98

• ## Cond. Formatting based on date change

Re: Cond. Formatting based on date change

jmhans:

Thank you, that last version did the trick for column A cells and I will work with that. When I did the paste special it also reformatted the other row cells to the right to the same as column A (date & time), which scrambles my existing data. I am fine with only column A being auto color filled and tested it many times with no problems. Thanks again.

• ## Cond. Formatting based on date change

Re: Cond. Formatting based on date change

Thanks. I tried this and it did color fill the entire row width selected.

However, it color filled all rows - not just the date change row and scrambled some data in other cells. Perhaps this is because of the combined date & time format in cloumn A (mm/dd/yy hh:mm). I believe Excel sees each row as being greater than the preceeding row due to the 1/2 hour incremental increase. I need it to perform the selected CF based on date change in a row (e.g. 12/11/04 to 12/12/04), which only occurs at 00:00 hours per 24 hour clock time.