• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macros referencing data in Tables - Auto-Populating other sheets

S

Sara.Gan

New Member
I am working on an automated worksheet that I have to complete by Monday. I don’t have enough time to figure this out on my own, so I am looking for some help. I am working with a multi-sheet workbook in Excel 2010.


The file is posted here:

http://speedy.sh/9tguW/Service-Matrix-Template-CHANDOO.xlsm


Download at SpeedyShare


The basic flow of how it works is below:


Home office enters services and selects matching service from the prepopulated list. Only two columns:


Service Name | Matching Service Name


Using a simple macro to move the entries to a second worksheet where the branch adds a service level to the service.


Service Name | Matching Service Name | Service Level


This list feeds into two other parts


Cost Benefit Deliverable - is populated based on Matching Service name with a variety of talking points. Would normally use VLOOKUP to populate but I’m not sure if that is too simple for this.


Service Model Matrix - is populated based on services AND level. I need a macro ( I think with a loop) to look at my list with

Service Name | Matching Service Name| Service Level

and fill in the service model, with the right services under the right level.


The Service Model Matrix also feeds from a list of services and levels. Right now I am just using absolute cell references to link to the service levels on a separate tab.


Any help would be greatly appreciated. I really want to use this project to learn more about macros and loops, but I am up against a time crunch now.
 
Hi, Sara.Gan!

I read your post, I downloaded your file, and maybe I'm not fully focused, but... I still don't get what do you need.

What do you want to get in sheet "Service Model DELIVERABLE" which is full of plenty #¡REF! values?

Regards!
 
I'm sorry, it's a bit hard to explain. The Service Model Deliverable is populated at the top with Service Levels from that tab. The other portion of the sheet should be populated with the services selected by the branch on the branch review tab. The branch reviews the services we entered, then assigns a level. So service names should fill in those fields.


For the cost benefit deliverable, I want use the same branch review data, but I want to match it based on the Matching Service names, but also display the service name the branch enters on the branch review tab.


Hope that makes more sense. Ideally, I want the branch to be able to review what we entered, make changes on the branch review tab, click a button to run a macro, and the cost benefit and service model deliverable are filled in.
 
Hi, Sara.Gan!

Yes, it seems to be hard, ... for me to understand, at least.

Would you be kind enough as to type here the expected values of SMD sheet for two colored blocks, accordingly to other sheets cell values? Let's say B39:B45 and D31:D37, for example.

As Jack said, let's go by parts. Afterwards we face CBD sheet.

Regards!
 
Hi Sara ,


I share SirJB7's confusion ! Let me try and summarise a few points :


1. You would like your tab "Service Model DELIVERABLE" to look like the tab "Fleishel Example".


2. To do this , you have given formulae in all the relevant cells , but for some reason , they have all become =#REF!


3. You already have several macros in Module 2 and Module 3.


4. You have instructions which require macros in steps 2 , 5 and 6 ; do you want these macros to be developed ? If so , you need to specify precisely what you want these macros to do.


5. In your second post , you say that the cells in the "Cost-Benefi DELIVERABLE " tab should be populated according to certain rules ; what are these rules ? What value should be looked up in which table on which tab , and what value should be returned ?


Narayan
 
@NARAYANK991

Hi!

Should it be true that two confused are less confused than only one? In this case I'm beginning to doubt...

:)

Regards!
 
Hi Sara ,


As SirJB7 has mentioned , let us go step by step.


I put in the following formula in cell B39 of your "Service Model DELIVERABLE" tab :


=IF(SMALL(IF($B$3='Service-Matrix-Template-CHANDOO(1).xlsm'!Level_Branch_Review,ROW('Service-Matrix-Template-CHANDOO(1).xlsm'!Level_Branch_Review)-1,65536),ROW(A1))>ROWS('Service-Matrix-Template-CHANDOO(1).xlsm'!Level_Branch_Review),"",INDEX('Service-Matrix-Template-CHANDOO(1).xlsm'!Service_Branch_Review,SMALL(IF($B$3='Service-Matrix-Template-CHANDOO(1).xlsm'!Level_Branch_Review,ROW('Service-Matrix-Template-CHANDOO(1).xlsm'!Level_Branch_Review)-1,65536),ROW(A1))))


It looks very intimidating , but most of it is because the workbook name is so long ! If we remove this from the formula , it becomes :


=IF(SMALL(IF($B$3=Level_Branch_Review,ROW(Level_Branch_Review)-1,65536),ROW(A1))>ROWS(Level_Branch_Review),"",INDEX(Service_Branch_Review,SMALL(IF($B$3=Level_Branch_Review,ROW(Level_Branch_Review)-1,65536),ROW(A1))))


This depends on two named ranges :


1. Level_Branch_Review referring to =OFFSET('Branch Review'!$C$2,0,0,COUNTA('Branch Review'!$C:$C)-1)


2. Service_Branch_Review referring to =OFFSET('Branch Review'!$B$2,0,0,COUNTA('Branch Review'!$C:$C)-1)


Copying the first 'monster' formula downwards , till cell B45 , will populate those cells with data from the "Branch Review" tab.


Narayan
 
@NARAYANK991

Hi!

Maybe I'm wrong, but:

a) named range Service_Branch_Review shouldn't be =OFFSET('Branch Review'!$B$2,0,0,COUNTA('Branch Review'!$B:$B)-1) instead of ...$C:$C...?

b) with or without this correction, I retrieve #¡VALUE! errors in B39:B45

Regards!
 
Hi SirJB7 ,


Thanks for your comments.


a) I intentionally kept the COUNTA range the same in both , so that the range length remains the same in both columns.


b) I forgot to mention that the formulae are array formulae , to be entered using CTRL SHIFT ENTER.


Narayan
 
@NARAYANK991

Hi!

It's me again... another questions:

a) in the formula where it says $B$3 shouldn't it be B$3 so as to copy to all 5 groups horizontally?

b) am I wrong again or in Fleishel Example the data blocks repeats horizontally and in Service Model DELIVERABLE they do vertically?

No need to say that I didn't analyze in detail the model and your solutions, just do the easy job to copy & paste and then compare. It's you who's doing the dirty job here.

Regards!
 
Hi SirJB7 ,


Thanks for your comment ; actually , I have not really done the relative and absolute addressing thoroughly ; I am waiting for Sara's comment to know whether this is what she is looking for.


If this has to be duplicated to all the remaining blocks in that tab , you are right ; the addressing will need to be addressed !


Narayan
 
Just got started working on this again. Am going to try the formula now and will post an update shortly. THANKS!
 
Hello Narayan and SirJB,


Thanks so much for your help. This is very close to what I need for the Service Model Deliverable. I used named ranges and the shorter array formula:


=IF(SMALL(IF($B$3=Level_Branch_Review,ROW(Level_Branch_Review)-1,65536),ROW(A1))>ROWS(Level_Branch_Review),"",INDEX(Service_Branch_Review,SMALL(IF($B$3=Level_Branch_Review,ROW(Level_Branch_Review)-1,65536),ROW(A1))))


Once I copy the formula down, I get the same issue SirJB is having. I need the formula to return the NEXT value from the Branch Review tab for that service level. In this case, cell B40 on the Service Model Deliverable tab should say Calls. I did try changing my cell references from $B$3 to B$3, but that did not correct the issue.


The same formula will need to be repeated across the orange sections, showing the same values at the bottom of the tier for higher levels in columns D,F,H and J on the Service Model Deliverable tab.


In regards to the Cost-Benefit Deliverable, it is populated based on the Branch Review tab. I would assume I need to create a third named range for this as well. I want to reference column B, Matching Service Name, on the Branch Review tab and have that value used to populate the cost benefit deliverable from columns B-F on the Cost Benefit Backstage tab. For example, if Account Aggregation was one of the Matching Service Names on Branch Review, it would be listed as an item on the Cost Benefit Deliverable with Questions, Description, Benefit and Cost of not participating from the Cost Benefit Backstage. While I want the Cost Benefit Deliverable to reference column B, Matching Service Name, I would like it to display Column A, Service Name.


The end result is to provide the Financial Advisor with a customized Service Matrix to share with his/her clients, and a customized Cost Benefit Deliverable which provides talking points for discussing those services with clients.
 
I looked at this more, and I think that the named range for Service_Branch_Review needs to be modified. I want to refer to column A, not column B for the Service Name.


Service_Branch_Review referring to =OFFSET('Branch Review'!$B$2,0,0,COUNTA('Branch Review'!$C:$C)-1)


I modified the cell reference in the named range from $B$2 to $A$2. Do I just modify the range name and leave the formula alone?
 
Hi Sara ,


As I have mentioned in an earlier post , the formula should give the correct result if it is entered as an array formula ( using CTRL SHIFT ENTER ).


In case you want that the range name should refer to column A instead of B , just change $B$2 to $A$2.


Narayan
 
@SirJB7

Hi, myself!

Well, let's put Sara.Gan to work on it... NARAYANK991 is still working on it... so the inevitable question is what am I doing?... and the answer is...:

a) either reading

b) or nothing

Regards!
 
I don't understand how the formula structure should change when I fill it to the upper service levels. I got it work, and pasted formulas from the bottom left B39:B45, and it worked properly. I then pasted the formulas in rows D,F,H, and J, and they were all working properly. Then I save the file, and they all go wacky.


I have this formula in B39:


=IF(SMALL(IF($B3=Level_Branch_Review,ROW(Level_Branch_Review)-1,65536),ROW(A1))>ROWS(Level_Branch_Review),"",INDEX(Service_Branch_Review,SMALL(IF(B$3=Level_Branch_Review,ROW(Level_Branch_Review)-1,65536),ROW(A1))))


That formula is filled down, then pasted over into rows D, F, H, and J.


I have this formula in D31:


=IF(SMALL(IF(D$3=Level_Branch_Review,ROW(Level_Branch_Review)-1,65536),ROW(C1))>ROWS(Level_Branch_Review),"",INDEX(Service_Branch_Review,SMALL(IF(D$3=Level_Branch_Review,ROW(Level_Branch_Review)-1,65536),ROW(C1))))


Once I have these formulas in row B,D,F,H and J I hit save. Then it reverts back to listing the five levels services in order across the rows, instead of repeating the same service level. The first column for the row has the right services, then the higher levels list to the right.


For now I just put absolute references to the left most column for that service level, since the first columns are working and I get the result I want.


Now time to finish the cost benefit deliverable.
 
I got everything to work, but there is one other item I would like to add. When the template is blank, before anyone enters anything, you see #REF errors in all fields on the Service Model Deliverable. Is there a way to modify my formula to show blank cells instead?


Here is my updated file with formula added and Cost Benefit Deliverable completed. Please let me know your thoughts.


http://speedy.sh/GKFgg/Service-Matrix-Template-CHANDOO.xlsm
 
Hi Sara ,


Good for you. You can remove the display of #REF by wrapping an IFERROR function around your existing formulae.


For example , if you have any formula A which can return an error value , writing :


=IFERROR(A,"")


will ensure that in the event of A returning an error value , the cell will display a blank. This is available in Excel 2007 and later. For Excel 2003 , you can have :


=IF(ISERROR(A),"",A)


Your formulae will become longer !


Narayan
 
Hi Narayan,


Just added that, thanks! I sent this file to my coworkers to test out, and the data validation lists on the H.O. Entry and Branch Review tabs disappear. Did you have this issue when you opened the file? I'm not sure what would be causing it to occur. I can write a macro to re-add the lists, but I am wondering if I can do that and have the macros automatically run when the file opens, or if there is a problem that I need to fix to make the lists work anytime it opens.
 
Hi Sara ,


Yes. I find there is no data on these two tabs.


Is there any macro to delete data ? Do you have any confirmation before deleting ? If not , please incorporate this. In fact , even the copy macro , which is copying from the "H.O. Entry" tab to the "Branch Review" tab , should verify that there is data before copying.


Narayan
 
You must log in or register to reply here.
Back
Top