A better way than cell formulas linked to other tabs.

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:
Channel Key Priority?
Nxt Key Decisionowner
Target Date
Status
Category
Notes

<tbody>
</tbody>
Broker Dealer
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>
etc up to 20 rows

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>
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.
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

buxtongt

Well-known Member
Joined
Oct 5, 2011
Messages
709
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

Board Regular
Joined
May 22, 2012
Messages
68
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

Well-known Member
Joined
Oct 5, 2011
Messages
709
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,556
Messages
5,487,535
Members
407,605
Latest member
PACULA

This Week's Hot Topics

Top