If cell contains date, refer to other cell, feedback to alternative tab.

[chrislkc]

Hi all,

If anyone could help me with the following function it would be most appreciated.


Example of data layout:

D1 - Item type
H1 - Unsuccessful date
I1 - Successful date

We utilise this data set across four different sheets for various clients. The fifth sheet will collate information from the other four sheets and collate the required figures so I can calculate how much of each particular item type is in demand.

That said, I need a formula that basically works as - If (I1) has a date, take item type from (D1, so for example, "washing machine"), then add this as 1 value on to a separate sheet.

That way I can create multiple item type listings on the new sheets, and have this information of required item types as a value next to it. Such as:
Washing Machines(A1) 10(B1)
Electric Cooker (A2) 15(B2)


I hope this makes sense!

Many thanks.

Chris

[Glenn Kennedy]

I don't follow what is meant to be in which cell!!! A spreadsheet is worth 1000 words...

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[chrislkc]

Hi Glenn,

Thanks for your quick reply.

I've attached a sample, but allow me to re-explain as the cell numbers are slightly different now.


I need the formula to work on the third sheet called "Item amounts"
It will need to:
Look at J2 (if there is a date, the sum needs to ignore this row as the date signifies that an item has already been allocated to this person)
If there is no successful delivery date, it will refer to the information in E2
It will then take the item type and apply it as a value to its respective cell on sheet three

If you look at the sheet named "Item amounts" it should make sense as to what I'm trying to do. This is where I need the values to display, so that my colleagues can easily see the amount of items that are required at any given point.

I hope this helps!

Apologies if this still isn't clear enough.


Many thanks,
Chris

[63falcondude]

Hello and welcome to the forum.

In B2 of the 'Item amounts' worksheet, try this:

=COUNTIFS('Client list 1'!J:J,"",'Client list 1'!E:E,A2)+COUNTIFS('Client list 2'!J:J,"",'Client list 2'!E:E,A2)