jake.peterson
Board Regular
- Joined
- May 22, 2012
- Messages
- 68
I have a report with a summary tab, and multiple detail tabs. Right now I am using cell formulas to pull information to the summary tab based on a Yes/No field to indicate whether its a key priority or not. All the detail tabs are the same with the exception of the name.
The summary tab has a range for each detail tab and looks something like this(cell forumlas included for illustration)
The first range is for the Broker_Dealer tab:
<tbody>
</tbody>Broker Dealer
<tbody>
</tbody>etc up to 20 rows
Then there's a range for the Wealth_Advisor tab(currently no new header row)
Wealth Advisor
<tbody>
</tbody>etc up to 20 rows
Then another range for the next tab, you get the idea.
The problem I have comes when someone wants to sort the data in the fields. I recorded a macro that sorts the individual ranges, but then the cell formulas are out of order and I don't think this is the best way to move forward.
I would like to build a macro that pulls the data line by line based on the Y/N indicator of each tab, but I'm not sure I have the chops for it and I'm not sure its even possible.
Any suggestions, or help would be greatly appreciated. Thanks in advance.
The summary tab has a range for each detail tab and looks something like this(cell forumlas included for illustration)
The first range is for the Broker_Dealer tab:
Channel | Key Priority? | Nxt Key Decision | owner | Target Date | Status | Category | Notes |
<tbody>
</tbody>
1 | =IF(Broker_Dealer!C6="Yes", Broker_Dealer!B6, "") | =IF(C7<>"", Broker_Dealer!D6, | =IF(D7<>"", Broker_Dealer!E6, | =IF(E7<>"", Broker_Dealer!F6, | etc... | ||
2 | =IF(Broker_Dealer!C7="Yes", Broker_Dealer!B7, "") | =IF(C8<>"", Broker_Dealer!D7, | =IF(D8<>"", Broker_Dealer!E7, | =IF(E8<>"", Broker_Dealer!F7, | etc... | ||
3 | =IF(Broker_Dealer!C8="Yes", Broker_Dealer!B8, "") | =IF(C9<>"", Broker_Dealer!D8, | =IF(D9<>"", Broker_Dealer!E8, | =IF(E9<>"", Broker_Dealer!F8, | etc... |
<tbody>
</tbody>
Then there's a range for the Wealth_Advisor tab(currently no new header row)
Wealth Advisor
1 | =IF(Wealth_Advisor!C6="Yes", Broker_Dealer!B6, "") | =IF(C7<>"", Wealth_Advisor!D6, | =IF(D7<>"", Wealth_Advisor!E6, | =IF(E7<>"", Wealth_Advisor!F6, | etc... | ||
2 | =IF(Wealth_AdvisorC7="Yes", Wealth_Advisor!B7, "") | =IF(C8<>"", Wealth_Advisor!D7, | =IF(D8<>"", Wealth_Advisor!E7, | =IF(E8<>"", Wealth_Advisor!F7, | etc... | ||
3 | =IF(Wealth_Advisor!C8="Yes", Wealth_Advisor!B8, "") | =IF(C9<>"", Wealth_Advisor!D8, | =IF(D9<>"", Wealth_Advisor!E8, | =IF(E9<>"", Wealth_Advisor!F8, | etc... |
<tbody>
</tbody>
Then another range for the next tab, you get the idea.
The problem I have comes when someone wants to sort the data in the fields. I recorded a macro that sorts the individual ranges, but then the cell formulas are out of order and I don't think this is the best way to move forward.
I would like to build a macro that pulls the data line by line based on the Y/N indicator of each tab, but I'm not sure I have the chops for it and I'm not sure its even possible.
Any suggestions, or help would be greatly appreciated. Thanks in advance.