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.
 

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.
 

Forum statistics

Threads
1,081,556
Messages
5,359,555
Members
400,533
Latest member
fpenning

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top