Filter & Find

[Muh]

There is a data From Jan to May In Separate Sheets . We Need Answer required in "Table Sheet "

1. Answer Required Based On 2 Drop down in Column "B4" Month and Column"D4" Employee

2. there is Already Have Formula in "D6" .But this Formula Depend on Only Employee Drop down "D4" . so This Formula Returns Entire month Data .

3.we need only Concerned Month and Concerned Employee Data As-per the Drop down selection Rectify Attached file formula to meet above requirements

4. Now As per the Given formula Result Return Entire month Data . We need Need Single Month Data Based On Employee As per the Selection

Sample Answer
Division	SALES	GP	GP %	2SALES C	GP C
ACCESSORIES
COMPUTER ACCESSORIES
ELECTRONICS
LAP
MULTIMEDIA
NETWORK AND SOFTWARES
TOOLS

[td]

Jan​

[/td]​

[td]

Jan​

[/td]​

[td]

Jan​

[/td]​

[td width="20.5766%"]

Jan​

[/td]​

[td width="8.5143%"]

Jan​

[/td]​

[td]

662​

[/td]​

[td]

321​

[/td]​

[td]

48%​

[/td]​

[td width="20.5766%"]

15%​

[/td]​

[td width="8.5143%"]

47%​

[/td]​

[td]

9​

[/td]​

[td]

6​

[/td]​

[td]

67%​

[/td]​

[td width="20.5766%"]

0%​

[/td]​

[td width="8.5143%"]

1%​

[/td]​

[td]

10​

[/td]​

[td]

3​

[/td]​

[td]

30%​

[/td]​

[td width="20.5766%"]

0%​

[/td]​

[td width="8.5143%"]

0%​

[/td]​

[td]

201​

[/td]​

[td]

56​

[/td]​

[td]

28%​

[/td]​

[td width="20.5766%"]

5%​

[/td]​

[td width="8.5143%"]

8%​

[/td]​

[td]

18​

[/td]​

[td]

2​

[/td]​

[td]

11%​

[/td]​

[td width="20.5766%"]

0%​

[/td]​

[td width="8.5143%"]

0%​

[/td]​

 

[Muh]

Hi Can anyone Write a formula

 

[shamimarita]

There is a data From Jan to May In Separate Sheets . We Need Answer required in "Table Sheet "

1. Answer Required Based On 2 Drop down in Column "B4" Month and Column"D4" Employee

2. there is Already Have Formula in "D6" .But this Formula Depend on Only Employee Drop down "D4" . so This Formula Returns Entire month Data .

3.we need only Concerned Month and Concerned Employee Data As-per the Drop down selection Rectify Attached file formula to meet above requirements

4. Now As per the Given formula Result Return Entire month Data . We need Need Single Month Data Based On Employee As per the Selection

Sample Answer
Division	SALES	GP	GP %	2SALES C	GP C
ACCESSORIES
COMPUTER ACCESSORIES
ELECTRONICS
LAP
MULTIMEDIA
NETWORK AND SOFTWARES
TOOLS

Hello Muh,

You can use the following formula to show values based on the Month and Employee.

Formula:
=LET(
combined, VSTACK(Jan:May!$D$5:$P$26),
header, Jan!$D$4:$P$4,
selected_employee, $D$4,
selected_month, UPPER(TEXT(DATEVALUE("1-" & $B$4), "mmmm")),
month_col, CHOOSECOLS(combined, 1),
filtered, FILTER(combined, month_col = selected_month),
division, CHOOSECOLS(filtered, 4),
data, CHOOSECOLS(filtered, 5, 6, 7, 12, 13),
header_out, CHOOSECOLS(header, 4, 5, 6, 7, 12, 13),
employee_col, CHOOSECOLS(filtered, 3),
filtered_employee, FILTER(HSTACK(division, data), employee_col = selected_employee),
VSTACK(header_out, filtered_employee)
)

 

[Muh]

Very Nice Sir ,

But While Using This Formula There is Division It is showing as 2 times. This is because the Value is in the Same Division in both branches. We need its total. Same Like Given 2.Answer Below Table

Answer Came As per the Given formula


2. Answer required Same Like Below

 

[Muh]

Very Nice Sir ,

But While Using This Formula There is Division It is showing as 2 times. This is because the Value is in the Same Division in both branches. We need its total. Same Like Given 2.Answer Below Table

Answer Came As per the Given formula
View attachment 1785

2. Answer required Same Like Below

View attachment 1787

Dear Sir
Greetings of the Day


Kindly Check Above Query .