Display all values of the same date vertically

[Lifecoachlee]

Hi everyone,

I need some help so badly!

Here's a sample of the project I am working on with.

Sheet 1
----------------------------------------------------------------------

DATE	DEALER	PICKUP SITE	PRODUCT	QUANTITY
1-Apr-14	ACAPARROS	EX-PASIG	L.LAPU	300
2-Apr-14	CALOSUY	EX-PASIG	L.LAPU	200
3-Apr-14	AMSERCO	FOB MILL	ELEF.	500
12-Apr-14	ASIATIC	EX-PASIG	L.LAPU	700
16-Apr-14	ACAPARROS	EX-PASIG	ELEF.	1500
1-May-14	ASMART	EX-PASIG	ELEF.	1500
2-May-14	ASMKTG	EX-PASIG	G.HARV.	1500
3-May-14	CALOSUY	EX-PASIG	S.BLEND	300
4-May-14	AMSERCO	FOB MILL	L.LAPU	200
5-May-14	AMSERCO	FOB MILL	L.LAPU	500
5-May-14	AMSERCO	FOB MILL	L.LAPU	700
5-May-14	AMSERCO	FOB MILL	G.HARV.	1500
5-May-14	CALOSUY	FOB MILL	S.LORD	500
13-May-14	AMSERCO	FOB MILL	ELEF.	300
20-May-14	AMSERCO	FOB MILL	L.LAPU	300

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

Sheet 2
----------------------------------------------------------------------------

MONTH:

APRIL

DAY:

SITE:

EX-PASIG

DEALER

DATE

L.LAPU

ELEF.

ORO EX.

G.HARV.

S.LORD

S.BLEND

A.PURP.

S.NOODLE

S.GODD.

C.TAIL

E.DRAGON

<colgroup><col span="11"><col><col></colgroup><tbody>
</tbody>

Here's what I wanted to happen.

Month, Day and Site have their corresponding data validation list where you can pull up the records to display under the table above once criteria are met and automatically add the values of the same products of the same date.

Example:
I will select from the list the ff in the search criteria:

Month: May
Day: 5
Site: FOB MILL

It should display like this:

DEALER

DATE

L.LAPU

ELEF.

ORO EX.

G.HARV.

S.LORD

S.BLEND

A.PURP.

S.NOODLE

S.GODD.

C.TAIL

E.DRAGON

<tbody>
</tbody>

AMSERCO 5-May-14 1200 1500
CALOSUY 5-May-14 500



Please help. Thank you!

 

[Lifecoachlee]

Cleaned up for uniformity:
https://dl.dropboxusercontent.com/u/65698317/FLOUR%20MILL%20MARKETING%20SYSTEM%20v8.xlsm

Using dynamic named ranges would be better than copying down as possibly needed.

Hi Aladin,
Thank you for your feedback but I'm not sure if I am understanding you correctly.
I'm still a novice to excel so you can't talk to me with straight technical terms.
I apologized.
Can you please elaborate? Thank you.

 

[Aladin Akyurek]

Hi Aladin,
Thank you for your feedback but I'm not sure if I am understanding you correctly.
I'm still a novice to excel so you can't talk to me with straight technical terms.
I apologized.
Can you please elaborate? Thank you.

Did you see the upload with improvements?

Dynamic named ranges are self-adjusting. Whenever a new record added to or removed from the data, the ranges are all immediately known to the formulas which refer to them.

 

[Lifecoachlee]

Did you see the upload with improvements?

Dynamic named ranges are self-adjusting. Whenever a new record added to or removed from the data, the ranges are all immediately known to the formulas which refer to them.

Yes, I did. It's fast.
So, you are saying that whenever I would add a new record I'd have to insert a new row in order for the formula to adjust automatically?

 

[Lifecoachlee]

I think I got what you mean now. Sorry, mate.
I had to do a lot of reading about this excel thingy.
I'm coming up with something and will let you see it if I'm doing it right. Thank you for keeping track with me. :D

 

[Lifecoachlee]

Hi Aladin,

I tried to create a dynamic named ranged however I'm not getting any luck. I dunno where I am getting wrong.
This is what I did.

From this formula
=IF($A7="","",SUMIFS(BOOKINGS!$E$5:$E$10000,BOOKINGS!$A$5:$A$10000,">=1-"&$G$3,BOOKINGS!$A$5:$A$10000,"<="&$H$3&"-"&$G$3,BOOKINGS!$F$5:$F$10000,"?*|"&$A7&"|FOB MILL|"&B$6))

To this formula:
=IF($A7="","",SUMIFS(BookingQty,BookingDate,">=1-"&$G$3,BookingDate,"<="&$H$3&"-"&$G$3,BookingConcat,"?*|"&$A7&"|FOB MILL|"&B$6))

where:
BookingQty
=OFFSET(BOOKINGS!$e$5,0,0,COUNTA(BOOKINGS!$e:$e),1)
BookingConcat
=OFFSET(BOOKINGS!$f$5,0,0,COUNTA(BOOKINGS!$f:$f),1)
BookingDate
=OFFSET(BOOKINGS!$a$e,0,0,COUNTA(BOOKINGS!$a:$a),1)

 

[Lifecoachlee]

Hi Aladin,

I tried to create a dynamic named ranged however I'm not getting any luck. I dunno where I am getting wrong.
This is what I did.

From this formula
=IF($A7="","",SUMIFS(BOOKINGS!$E$5:$E$10000,BOOKINGS!$A$5:$A$10000,">=1-"&$G$3,BOOKINGS!$A$5:$A$10000,"<="&$H$3&"-"&$G$3,BOOKINGS!$F$5:$F$10000,"?*|"&$A7&"|FOB MILL|"&B$6))

To this formula:
=IF($A7="","",SUMIFS(BookingQty,BookingDate,">=1-"&$G$3,BookingDate,"<="&$H$3&"-"&$G$3,BookingConcat,"?*|"&$A7&"|FOB MILL|"&B$6))

where:
BookingQty
=OFFSET(BOOKINGS!$e$5,0,0,COUNTA(BOOKINGS!$e:$e),1)
BookingConcat
=OFFSET(BOOKINGS!$f$5,0,0,COUNTA(BOOKINGS!$f:$f),1)
BookingDate
=OFFSET(BOOKINGS!$a$e,0,0,COUNTA(BOOKINGS!$a:$a),1)

Correction:
where:
BookingQty
=OFFSET(BOOKINGS!$e$5,0,0,COUNTA(BOOKINGS!$e:$e),1)
BookingConcat
=OFFSET(BOOKINGS!$f$5,0,0,COUNTA(BOOKINGS!$f:$f),1)
BookingDate
=OFFSET(BOOKINGS!$a$5,0,0,COUNTA(BOOKINGS!$a:$a),1)

 

[Aladin Akyurek]

Correction:
where:
BookingQty
=OFFSET(BOOKINGS!$e$5,0,0,COUNTA(BOOKINGS!$e:$e),1)
BookingConcat
=OFFSET(BOOKINGS!$f$5,0,0,COUNTA(BOOKINGS!$f:$f),1)
BookingDate
=OFFSET(BOOKINGS!$a$5,0,0,COUNTA(BOOKINGS!$a:$a),1)

I'd suggest a different route...

BigNum
=9.99999999999999E+307

LrowBooking
=MATCH(BigNum,BOOKINGS!$A:$A)

BookingDate
=BOOKINGS!$A$5:INDEX(BOOKINGS!$A:$A,LrowBooking)

BookingQty
=BOOKINGS!$E$5:INDEX(BOOKINGS!$E:$E,LrowBooking)

BookingConcat
=BOOKINGS!$F$5:INDEX(BOOKINGS!$F:$F,LrowBooking)

IvecBooking
=ROW(BookingDate)-ROW(INDEX(BookingDate,1,1))+1

 

[Lifecoachlee]

Is IvecBooking the same as BookingIvec?
and where can I use IvecBooking?

IvecBooking
=ROW(BookingDate)-ROW(INDEX(BookingDate,1,1))+1

BookingIvec
=ROW(BOOKINGS!$B$5:$B$30)-ROW(BOOKINGS!$B$5)+1

 

[Lifecoachlee]

Also, how does LrowBooking work?

 

[Aladin Akyurek]

Is IvecBooking the same as BookingIvec?
and where can I use IvecBooking?

IvecBooking
=ROW(BookingDate)-ROW(INDEX(BookingDate,1,1))+1

BookingIvec
=ROW(BOOKINGS!$B$5:$B$30)-ROW(BOOKINGS!$B$5)+1

Yes they are same, but we will use IvecBooking for it must be defined in terms of the already booking ranges.

Ivec is by the way an abbrev from integer vector.

Also, how does LrowBooking work?

Lrow is an abbrev of last row. LrowBooking is the current last row of the data area in the BOOKINGS sheet.

The following workbook contains the required definitions, not only involving BOOKINGS, but also WITHDRAWALS and ARRIVALS:
https://dl.dropboxusercontent.com/u/65698317/FLOUR%20MILL%20MARKETING%20SYSTEM%20v8%20dynamic.xlsm

Moreover, all of the formulas are adjusted to refer to the dynamic named ranges...