mattbro456
New Member
- Joined
- Nov 21, 2014
- Messages
- 5
I'm having trouble with a sales database I'm currently working on, the point of this database is too keep track of all the varying sales department and noting how many units and money was made in each year.
On one spreadsheet I have about 49 tables created for each department each one looks like the example below but will have different department names (Software, Hardware, etc.) and they are all on one sheet, looking like the example:
Sheet Name: Departments (1)
<tbody>
</tbody>
<tbody>
</tbody>
What I would like to do is this: on a separate sheet I have a data validated cell that has all the departments listed so I can just click and select a specific department. After I select a department (like Software) I would like it to auto fill itself by essentially dragging the column data from the first sheet into the table so it looks like the following table.
Sheet name: Lookback (2)
Department: Software (this is B2 in the provided function I tried)
<tbody>
</tbody>
The current function I have typed out is {=IFERROR(INDEX(Departments!$A$1:$A$9,SMALL(IF(Department!$A$1:A$9=B$2,COLUMN(Departments!$B1:B9)-COLUMN(Departments!B1)+1),COLUMNS(DepartmentsB1:B2))),"")}
This formula will match the correct data for some selected departments, but it's not consistent for all.
Thoughts?
On one spreadsheet I have about 49 tables created for each department each one looks like the example below but will have different department names (Software, Hardware, etc.) and they are all on one sheet, looking like the example:
Sheet Name: Departments (1)
| Software Dpt | 2010 | 2011 | 2012 | 2013 | 2014 |
| units | 200 | 220 | 240 | 180 | 215 |
| Price | $15 | $10 | $17 | $19 | $20 |
| Revenue | $$$ | $$$ | $$$ | $$$ | $$$ |
<tbody>
</tbody>
| Hardware Dpt | 2010 | 2011 | 2012 | 2013 | 2014 |
| units | xx | xx | xx | xx | xx |
| Price | xx | xx | xxx | xx | xx |
| Revenue | xx | x | x | x | xx |
<tbody>
</tbody>
What I would like to do is this: on a separate sheet I have a data validated cell that has all the departments listed so I can just click and select a specific department. After I select a department (like Software) I would like it to auto fill itself by essentially dragging the column data from the first sheet into the table so it looks like the following table.
Sheet name: Lookback (2)
Department: Software (this is B2 in the provided function I tried)
| 2010 | 2011 | 2012 | 2013 | 2014 | |
| units | 200 | 220 | 240 | 180 | 215 |
| Price | $15 | $10 | $17 | $19 | $20 |
| Revenue | $$$ | $$$ | $$$ | $$$ | $$$ |
<tbody>
</tbody>
The current function I have typed out is {=IFERROR(INDEX(Departments!$A$1:$A$9,SMALL(IF(Department!$A$1:A$9=B$2,COLUMN(Departments!$B1:B9)-COLUMN(Departments!B1)+1),COLUMNS(DepartmentsB1:B2))),"")}
This formula will match the correct data for some selected departments, but it's not consistent for all.
Thoughts?
