Date Reference

**alimar7** · 12-01-2008, 01:26 PM

Hi im new to this board, but im finding im suing excel more and more for my job, and need some help with a few things

First things first, i work for a supplier, and we have a number of wholesale customers, one of which we phone all there restaurants (98 a week) and i have a spreadsheet set out to record there orders. This simply involves me putting a 1 in the cell corresponding to the day they placed there order.

Want i want to do is create a formula which will return a 1 in a cell next to the next to the restaurant in column B if it has put in an order during the current week. So along the top of the sheet is a merged cell containing the start of each week, then five cells underneath each containing date of each day. Below all that before the fields containing the ordering date (1 or a 0) is a row of merged cells which contain either a 0 or a 1 for the current week. I also have cells at the start of the document that return the current date, and the commencing date of the current week.

A lot of this i have simply added to try and solve the problem but ive been trying with out success for a few days now.

So basically what i want to do is return a 1 in column B next to each restaurant if during the current week the restaurant has put in an order.

Failing that, a way to return the value of the five cells for the restaurants five days on that week would be ace.

CHEERS in advance

Ali the cheesemonger

**NBVC** · 12-01-2008, 02:32 PM

So you have a 1 or 0 below each day of the week for each restaurant? And you want to know it there is at least one number 1 entered in any of those 5 or 7 days of the week?

if so,

=--Countif(C2:H2,1)>0

If you put this formula in B2, it will return a 1 if there is at least a 1 in any of the cells in the range C2:H2. Otherwise it will return a 0.

Is that what you are looking for?

**alimar7** · 12-02-2008, 04:28 AM

Sort of the problem is, i want the five cells the formula checks to change to the current week. Each week has five cells, for each restaurant.

Cheers
Ali the cheesemonger

**NBVC** · 12-02-2008, 08:51 AM

Not 100% sure I follow your setup... an attached sample worksheet would help.

Anyways here are some formulas based on a couple of assumptions

Assuming you have a week start date once in every 5 cells horizontally (which are merged) and then 2 rows down, you have another set of merged cells which has a 1 or 0 in it to represent the entire week.

If that is indeed the case then perhaps a formula like this will do:

=INDEX(C3:R3,MATCH(A1,C1:R1))

Where C1:R1 contain your week start dates and C3:R3 contain your 1's and 0's.

A1 would contain today's date which you can just replace in the formula with TODAY() if desired.

If however, you have a 1 or 0 under each date within the week and you want to see if there is at least 1 number 1 entered in one of those dates, then perhaps this formula:

=--COUNTIF(OFFSET(INDEX(C1:R1,MATCH(A1,C1:R1)),2,0,1,5),1)>0

with same reference assumptions as above.

**alimar7** · 12-02-2008, 12:03 PM

=IF(COUNT((INDEX(F10:IV10,0,MATCH(1,F$2:IV$2,0)))

INDEX(F10:IV10,0,MATCH(2,F$2:IV$2,0))))>=0.9,1,0) This was the eventual formula i ended up with! and it seems to work

**NBVC** · 12-02-2008, 12:06 PM

Why are we matching 1 and 2 in these: MATCH(1,F$2:IV$2,0) and MATCH(2,F$2:IV$2,0) ?

**alimar7** · 12-03-2008, 04:44 AM

my sheet sets the Monday of the current week as one, then set the fifth cell in the week as 2, so it finds the first one and the first 2 and looks at the cells between them.

i guess i could have used 5, ie friday in excel weekday format, but just as easy to use 2

**NBVC** · 12-03-2008, 09:36 AM

That was never mentioned before in this thread.

It is always best to mention these things if you want them incorporated into the formulas supplied.

That is also why it is always best to supply an attached worksheet example. So we can see what you see, not having to guess.

**alimar7** · 12-03-2008, 10:16 AM

Sorry, i just inserted it when i worked out how to do it, currently trying to make a formula to work out the average for each day of the week, much more fun than selling cheese

Cheers
Ali the Cheesemonger

Date Reference

LinkBack

Thread Tools

Rate This Thread

Display

Date Reference

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions