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.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,479
Messages
6,113,894
Members
448,530
Latest member
yatong2008

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top