I can't pull up all data for today's date when there is more than one instance.

0

Hi all,

I am currently creating a workbook that will email all department of my ship everyday about upcoming vessel movements (we are at anchor for a prolonged period so everything comes and leaves via a boat of some sort.)

I have got half way there by creating the forms and tabs and copying some bits from templates.

The last part I am really struggling with though. I would like the SalesRpt sheet to auto populate all data from today's date and tomorrow''s (this sheet is where the pdf is created.)

I seem only to be able to get info for one instance of today's date but where there are two or more expected events I can't figure out how to show this. A kind of schedule may work but I can;t do it. Ideally I would like it to show "Upcoming Movements for Today" and "Upcoming Movements for Tomorrow". It should then only show movements for the day but show all of them.

Answer
Discuss

Discussion

I think the advice that can be given without looking at your workbook will not satisfy your needs. Please consider editing your question in that regard. Delete or obscure confidential data before posting.
With that said, iterations are difficult for Excel. Look at looping in VBA. Use a For ... Next structure to loop through all dates in a range (taken from a column) and skipping those that don't meet your specs. If your dates are in consecutive cells, Find the first one and then use Do While to loop until there is a change or the end. You will find plenty of examples of such code on the Internet.
Look for more ideas at "Vlookup to Return All Matching Results" linked to something on this site, advertised to the left of this reply on my screen as I write.
Variatus (rep: 4148) Oct 13, '20 at 9:27 pm
I agree with Variatus that it's hard to answer given the information provided but I think VBA is your best bet. 

I been away from Excel (and this fourm) for a while but was able to create a .xlsm workbook where one sheet contains all the movements for all dates,  past and future (with just three columns, date due, some form of number and a descrition). A few lines of VBA then read any rows on that sheet and in a SlsRpt sheet add today's movements in two column (the number and description) and the same for tomorrow in some adjacent columns.

I doubt that suits your purposes and I'm not sure if the forum rules allow files to be uploaded by users.

If you're familiar with VBA I could post the code and better describe my sheet. I think however it might be better if you follow advice from Variatus on giving a test version of your shreadsheet.
John_Ru (rep: 25) Oct 14, '20 at 1:52 pm
@John_Ru Excel files can be uploaded as part of a question or part of an answer.
Variatus (rep: 4148) Oct 14, '20 at 7:48 pm
Have you thought to use simple filtering to display all movements for today then create a pdf from the filtered view? (That assumes all shipments are on one sheet).

If you filter on a column of cells formatted as dates, the filtering menu will show Date Filters - they include options for Today and Tomorrow for example but you could choose a Between (or Custom) filter to display 2 or more dates.
John_Ru (rep: 25) Oct 15, '20 at 3:40 am
Add to Discussion

Answers

0

Here's a macro-enabled spreadsheet illiustrating the two suggestions I made under the Discussion section. (Thanks to Variatus for the clarification on file upload.).

I've illustrated them with a simple list of imaginary transactions (each of three fields) in the "All shipments" sheet- I'm sure you have more data but that could be handled too.

Firstly look at the SlsRpt sheet from yesterday- it shows what was to be shipped (yesterday) and due the following day (15 October).

Then go the the All Shipments sheet and see a list of imaginary movements. You should see a light-yellow button- if you press that (with macros emabled), it will switch to Sls Report, qiuckly clear old data (after row 4) and update the records to show today's and tomorrow's dates plus revision time..

You can play around with the dates listed in All Shipments (or rename that tab) and add extra rows (/ have gaps in those rows) and the macro button should still work. (Note however that empty gaps can cause problems if you add filters -like in the Filtered View sheet- unless you select data beyond thos gaps before you add the filter).

The sheet "Filtered view" is just "All shipments" (as I sent it, before your changes) but with the filter set to Today. Note the above about gaps in the rows.

The VBA behind this is a bit crude (but I've commented it so you can see roughly what's happening). You could extend that (for more data per shipment, to sort the shipments, print the pdf and email to a set of recipients, speed it up if there's lots of data etc.)- I'm sure Variatus and others could refine it with ease!

Hope this helps

John

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login