Hi,
I have a list of "Customers" in column A (A2-A270) in my first tab.
In columns B-G (listing "Item name" in B1-G1) I have listed a year slash a number: e.g. 2018/1000 (years ranging from 2017-2020.
What I want to do is to be able to form a table per customer in a secondtab depending on which customer is visible after filtering in my first tab.
Tab 1:
<tbody>
</tbody>
This table in tab2 I want to have like this:
Items on rows, years in columns and in the cells in between I want to list the number given in column B-G in my first tab.
Customer 1 chosen with filter in tab1 -->
Tab2:
<tbody>
</tbody>
I have the following formula but it doesn't take the filtering in tab 1 into consideration. How can I do this?
=IF(VALUE(LEFT(Tab1!B2;4))=Tab2!B1;VALUE(RIGHT(Tab1!B2;(LEN(Tab1!B2)-5)));0)
if
Tab1!B2= statement for item 1 customer 1
Tab2!B1= year in table in Tab2
I know you can use the formula subtotal for filtered values - but how do I integrate if, left, right formulas etc.?
It is okay that the table in Tab2 only works when there is only one customer visible in filter i Tab1.
Thank you.
I have a list of "Customers" in column A (A2-A270) in my first tab.
In columns B-G (listing "Item name" in B1-G1) I have listed a year slash a number: e.g. 2018/1000 (years ranging from 2017-2020.
What I want to do is to be able to form a table per customer in a secondtab depending on which customer is visible after filtering in my first tab.
Tab 1:
Item1 | Item2 | Item3 | |
Customer 1 | 2017/2000 | 2018/890 | |
Customer 2 | 2020/8950 | 2017/780 | |
Customer 3 | 2018/5000 |
<tbody>
</tbody>
This table in tab2 I want to have like this:
Items on rows, years in columns and in the cells in between I want to list the number given in column B-G in my first tab.
Customer 1 chosen with filter in tab1 -->
Tab2:
2017 | 2018 | 2019 | 2020 | |
Item 1 | 2000 | |||
Item 2 | 890 | |||
Item 3 |
<tbody>
</tbody>
I have the following formula but it doesn't take the filtering in tab 1 into consideration. How can I do this?
=IF(VALUE(LEFT(Tab1!B2;4))=Tab2!B1;VALUE(RIGHT(Tab1!B2;(LEN(Tab1!B2)-5)));0)
if
Tab1!B2= statement for item 1 customer 1
Tab2!B1= year in table in Tab2
I know you can use the formula subtotal for filtered values - but how do I integrate if, left, right formulas etc.?
It is okay that the table in Tab2 only works when there is only one customer visible in filter i Tab1.
Thank you.