Stopping a lookup once a certain date is reached

[yetibeard]

Hi,

I have a sheet with months of data which is filled in each day, with the data for each date in separate columns, with the date at the top (row 3) of each column.

I have a separate "report" sheet which looks back through this data and populates the "report" information for the given date at the top of the report.

Part of the report is 'JOB TO DATE' data, where I use this formula to look back and add up all the info for that job number

=SUMPRODUCT(SUMIF('DATA INPUT'!$5:$5,'Shift Report 24Hrs'!$P$22,'DATA INPUT'!$36:$36))

'data input' row 5 contains the job number in the data input
'shift report 24hrs' p22 has the job number
'data input' row 36 has the data I am summing

This formula works fine for the current date and I am happy with it. But if I want to re-do a report for a previous date then this formula continues to sum all the data for that job number.

Is there a way I can get this formula to work and stop summing for the given date?

For example. We have been running this job number for 10 weeks. I want to run a report for week 2 and see what the job to date data was for that moment in time, the current formula adds up all 10 weeks.

I hope I have made this clear enough!

Thanks in advance

[BadlySpelledBuoy]

Difficult to give an exact solution without a sample workbook so we can see the data layout etc.

You can use SUMIFS to add more criteria to the sum, one of which would be the date is <= specified date.

Put the date you wish to sum up to in a cell (for the formula below I've used A1, but you can change that to suit).

Please Login or Register  to view this content.

Any help?

BSB

[yetibeard]

Hi,

Thanks for your reply. That is working exactly how I need it to.

I couldn't be happier with that result!

One more thing, which is similar with a slight difference.
The report also has a 'week to date' section. For this I have a table which works out the start of the week based on todays date. I then use this table to pull data from the data input tab. I use the following formula
=SUMPRODUCT(SUMIF('DATA INPUT'!$3:$3,$B$50:$B$59,'DATA INPUT'!$35:$35))
data input 3:3 is the row of dates
b50:b59 is the table with this weeks dates in
data input 35:35 is the row i am summing

Again, how do I stop this based on a specific date, same as the initial problem. (I've tried doing this myself but don't get the right result!)

Thanks






Cheers

[BadlySpelledBuoy]

I think for this one I'd need to see a sample workbook where you can manually create your expected results.

It will probably be possible to do this without the dates table or the need to use SUMPRODUCT.

BSB

[yetibeard]

I have created a simple file. I have hidden some rows so the data is in the same cell as what I am using.

[BadlySpelledBuoy]

So which dates do you want it to calculate between? The week start date and the date in H2?

BSB

[yetibeard]

yes please!

[BadlySpelledBuoy]

Try this. By using a >= and <= construct it negates the need for the table to calculate the dates. Instead is uses the formula you already have to find the week start and uses that within the SUMIFS.
Target:

Please Login or Register  to view this content.

Actual:

Please Login or Register  to view this content.

BSB

[yetibeard]

I know this is in the wrong section of the forum, but while you active... Is there a way to have a protected sheet but still allow spell check to be done? When i Protect the sheet there is no option to leave spell check enabled.

[yetibeard]

Thanks very much! Ill do that! That's fantastic!

[BadlySpelledBuoy]

No problem. Happy to help

As for spell checking a protected sheet, only way I know is with VBA.

You can either uprotect, check the spellings then protect again all done with a small macro, or you could protect the sheet on the workbook open event setting UserInterfaceOnly to True then call a spell check again via VBA.

Not sure of any other way to do it than that. Not something I use often...

BSB

[BadlySpelledBuoy]

VBA for spell checking protected sheet.
This in the ThisWorkbook module:

Please Login or Register  to view this content.

And this in a standard module and linked to a button to set the spell check running:

Please Login or Register  to view this content.

See attached.

BSB

[yetibeard]

I'm learning excel is capable of all sorts!

I like the spell check button...

Can it.. (I'm going to assume it can!) ... you know my data input page with the coulmns of data under each date, can the spell check run for just the last 3 days? Rather that checking the full sheet which will eventually be years of data!

[BadlySpelledBuoy]

Yes indeed, Excel is far more versatile than many people give it credit for.

It can indeed check only a selected area. The below code will find today's date in row 3 then spell check rows 35 and 36 for the column it finds today's date in and the previous two columns only. You can easily amend ranges to suit.

Please Login or Register  to view this content.

However, your sample workbook only has numbers, so little point in spell checking that!

BSB