Offset formula

**cell-dweller** · 04-06-2010, 07:53 PM

I am looking for a formula to look through a cloumn and let me know what customer jobs are due for that particular day. I recall using a "offset" function some time back but cant get it to work.
Eg.
Date ordered Due date Customer
1-Apr-10 4-Apr-10 Smith
2-Apr-10 5-Apr-10 Jones
2-Apr-10 5-Apr-10 Billy
3-Apr-10 6-Apr-10 Sean

What I need is a formula that will let me know that today (5april for instance) customer Jones and Billy are due.

Any help appreciated.
Alo please tell me how or if it is possible to cointnue a formula down a column without dragging it down?

All the best,

**teylyn** · 04-06-2010, 08:27 PM

cell-dweller,

switch on the autofilter and filter by due date. That will give you the list of names.

Re the second question: are you looking for an alternative to dragging? You could use copy and paste instead. If that's not what you need, please explain some more and maybe attach a data sample.

**cell-dweller** · 04-06-2010, 08:47 PM

Originally Posted by teylyn

cell-dweller,

switch on the autofilter and filter by due date. That will give you the list of names.

Re the second question: are you looking for an alternative to dragging? You could use copy and paste instead. If that's not what you need, please explain some more and maybe attach a data sample.

Hi Teylyn,
Thanks for the reply. what you sy makes so much sense that i almost find it hard to let you know what i am looking for. What i used to have was a calendar with names next to it of my families birthdates, and when i would open the sheet, there woul be a reference cell telling me whose birthday was coming up. The formula was a "offset" formula.
I am trying to do the same with this current sheet. I want to look through the dates and see which ones are todays date and then display them.
Not sure if it is possible?

The second part is, how do i have a formula in a cell but can continue th formula on the next row? Is it possible to have the formula for the whole column, without having to drage it, or wiothouit it being visisble until the cell next to it is filled in? eg. i would put todays date in cell A1 and in cell B1 the formula reads =A1+2 giving me a date in two days time. How do i continue this formula on to the next row?

Please excuse the long winded replies.

**teylyn** · 04-06-2010, 09:31 PM

Your family birthday calendar probably had only one name against a date, or several names in one cell against a single date. With the Customer and Due data scenario, you're looking at potentially hundreds of names against a single date, depending on the size of your business. The first instance of a due data and the corresponding customer name is easy to set up. A Vlookup will do. But for the second, third and xth customer on the same date, the formula will have to span many rows and get exceedingly complicated.

In order to give you a solution that you can understand and maintain, I stand by my suggestion of the Autofilter. Click any cell in your data table, then click Data - Filter - Autofilter, select the dropdown for the due date, select the due date and see the list reduced to just those rows that have that due date.

If you want formulas to be automatically applied to new rows of data, use an Excel List (2003) or Table (2007).

Please update your profile to indicate your Excel version, so I don't have to guess which instructions to give you.

**cell-dweller** · 04-06-2010, 11:45 PM

Hi
I am on Excel 2003. I would love to get away from doing a filter. What about a autosearch or macro?

**DonkeyOte** · 04-07-2010, 02:56 AM

Use a Pivot Table - set Due Date as a Page Field, Customer as Row Field and Data Field (set to Count)

Use a Dynamic Named Range as the source for the Pivot Table such that a simple refresh of the Pivot will capture the latest data at all times.
see: http://www.contextures.com/xlpivot01.html

If really nec. you can use VBA to update the Page Field to reflect current date as and when the file is opened.

Any formula based approach will be inefficient in comparison to the above.