A better way than cell formulas linked to other tabs.

[jake.peterson]

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:
[TABLE="width: 850"]
<tbody>[TR]
[TD]Channel [/TD]
[TD]Key Priority?
[/TD]
[TD]Nxt Key Decision[/TD]
[TD]owner
[/TD]
[TD]Target Date
[/TD]
[TD]Status
[/TD]
[TD]Category
[/TD]
[TD]Notes
[/TD]
[/TR]
</tbody>[/TABLE]
Broker Dealer
[TABLE="width: 500"]
<tbody>[TR]
[TD]1 [/TD]
[TD]=IF(Broker_Dealer!C6="Yes", Broker_Dealer!B6, "")[/TD]
[TD]=IF(C7<>"", Broker_Dealer!D6,[/TD]
[TD]=IF(D7<>"", Broker_Dealer!E6,[/TD]
[TD]=IF(E7<>"", Broker_Dealer!F6,[/TD]
[TD] etc... [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2 [/TD]
[TD]=IF(Broker_Dealer!C7="Yes", Broker_Dealer!B7, "")[/TD]
[TD]=IF(C8<>"", Broker_Dealer!D7,[/TD]
[TD] =IF(D8<>"", Broker_Dealer!E7,[/TD]
[TD]=IF(E8<>"", Broker_Dealer!F7,[/TD]
[TD]etc...[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]=IF(Broker_Dealer!C8="Yes", Broker_Dealer!B8, "")[/TD]
[TD]=IF(C9<>"", Broker_Dealer!D8,[/TD]
[TD]=IF(D9<>"", Broker_Dealer!E8,[/TD]
[TD]=IF(E9<>"", Broker_Dealer!F8,[/TD]
[TD]etc...[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
etc up to 20 rows

Then there's a range for the Wealth_Advisor tab(currently no new header row)
Wealth Advisor
[TABLE="width: 500"]
<tbody>[TR]
[TD]1 [/TD]
[TD]=IF(Wealth_Advisor!C6="Yes", Broker_Dealer!B6, "")[/TD]
[TD]=IF(C7<>"", Wealth_Advisor!D6,[/TD]
[TD]=IF(D7<>"", Wealth_Advisor!E6,[/TD]
[TD]=IF(E7<>"", Wealth_Advisor!F6,[/TD]
[TD] etc... [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2 [/TD]
[TD]=IF(Wealth_AdvisorC7="Yes", Wealth_Advisor!B7, "")[/TD]
[TD]=IF(C8<>"", Wealth_Advisor!D7,[/TD]
[TD] =IF(D8<>"", Wealth_Advisor!E7,[/TD]
[TD]=IF(E8<>"", Wealth_Advisor!F7,[/TD]
[TD]etc...[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]=IF(Wealth_Advisor!C8="Yes", Wealth_Advisor!B8, "")[/TD]
[TD]=IF(C9<>"", Wealth_Advisor!D8,[/TD]
[TD]=IF(D9<>"", Wealth_Advisor!E8,[/TD]
[TD]=IF(E9<>"", Wealth_Advisor!F8,[/TD]
[TD]etc...[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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.

 

[buxtongt]

I'm not sure if this is what you want or not (if not feel free to let me know and I'll try to go down a different road), but one option would be to pull all the info in, then copy it all and paste values, so the numbers would be easier to work with as far as sorting, etc, etc. Hope this helps.

 

[jake.peterson]

Sorry I wasn't more clear. The columns that I would sort are not numerical values. They would be Owner, Date, Status, and Category.

I would like to do something like - If key priority= "yes" then copy row & paste to next empty row in the appropriate range on the summary page

I know a little about macros but I mostly record them. I'm not sure how I would past to the next empty space in a range.

Thanks for your quick response.

 

[buxtongt]

You should be able to record a macro for something like this, maybe with some minor tweaks afterwords. You would need to put it in the SheetChange event in the VB editor. For the next empty cell in a column I would use the ctrl+shift+down key combo. Hopefully this does the trick for you.