Excel - Fill in the fields between 2 values 1

[allancsr]

Hi,
Please help me with a formula.
I have a wook sheet that comapres In and out dates then returns a Quanity of items needed to the field.

The data only gives an in and out date. The item may be in use for a range of dates. So I would Like the spreadsheet to fill in the quanity between the dates
Example:
AAGEN is in use from 2002/1/1 till 2002/1/4
I want it to show in use also on the 2nd and 3rd.

Item Order 2002 1 1 2002 1 2 2002 1 3 2002 1 4
AAGEN BobSmith 2 2

Here is the current formula

IF(OR(AND($C$1=look!A2, B2=look!C2),AND($C$1=look!B2, B2=look!C2),), look!E2, "&quot

This sheet is 93 colums by 5000 rows so any help will be appreciated.

Thanks
Allan
Allan@colrotone.com

 

[DaleWatson123321]

Allan,

The first thing I notice when I assess your example, is that you appear to have done what some "relatively new spreadsheet users" do - and that is to "spread out" the dates by assigning one day to a column.

In such situations, new spreadsheet users are often pointed in the "proper" direction - which is to use only TWO columns for the date. In this case, it would be "DATE OUT" and "DATE IN".

Spreadsheets like Excel have tremendous capability to analyze or extract or summary, etc, data from a database such as the one you describe. In particular, it is possible to use Excel's "date functions" and "database functions" to perform such analysis.

You could, for example, use database formulas to determine: whether a product is available, 2) how many days it has been "out", 3) how many days it is "overdue", etc.

Therefore, RATHER than come up with a formula which will potentially keep you on the "wrong track", might I suggest you consider offering to email to Tek-Tips contributors (myself included), a "scaled down version" of your database. Having the file "on screen" will enable competent Excel users to quickly assess your situation and "direct you in the proper direction".

If you wish, email me at the following address.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[allancsr]

Dale
I have sent you the email that you requested.

If you have any other questions please let me know.

For other people who may help
I need to fill in the spaces For visual plotting for allocation of assets.
This is what my Operations guys want.

I have posted an example here for those who may wish to help.

http://home.centurytel.net/allan/help/example.xls

Thanks
Allan

 

[DaleWatson123321]

Allan,

I've just completed the file, and will be emailing it back to you immediately.

I've accomplished your objective of "filling in the gaps".

As you'll notice, I've taken the liberty to demonstrate the option I spoke of - to extract the data on a "selective" basis - by month. With this option in place, it will now be more realistic, in terms of fitting each month on the width of a page. The code also automatically formats the extracted data and sets the print range.

You'll also notice the VBA code is attached to a button that says "Extract Monthly Data". To the left of this button, you only need to type in the number for the month prior to clicking the button.

I've also eliminated all the "resident formulas". I've stored one row of the formulas on row 1, and then copy the formulas via VBA to the extracted records, and the formulas are then converted to "values". This will reduce the size of the file.

Please also read my notes on the "database" sheet, which refer to the "overlap" of items from one month to the next. As you'll notice, I've created the extraction criteria such that these "overlap" records WILL be included in both of the months they overlap.

I've had to make some assumptions, so please provide feedback as to how reasonable these assumptions are, as well as the overall approach to addressing your application.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
or nd.watson@shaw.ca

 

[allancsr]

WOW WOW WOW

Dale ,
Great Job
Your Sheet is very helpful and decriptions explain wonderfully the workings of the VBS.

Thanks agian you did above and beyond the help I expected from anyone and Tek Tips!

Allan