Solved

Lookup a table and then do this

Posted on 2015-02-05
32
72 Views
Last Modified: 2015-02-06
In a sheet named Static I have a table with Country names in column A.


Please provide me with VBA code that will look up this table and if a Sheet with the name[s] in column A exists, Call my macro [named CreateAgeBands] for each one of the countries.

so:  look up Static column A [lets say A2 = Spain] if a sheet exists for Spain call my macro [CreateAgeBands]  then move on to A3 in static etc etc


Thanks
0
Comment
Question by:Jagwarman
  • 17
  • 15
32 Comments
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok can you post your workbook ?
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Also what do you mean by:
call my macro [CreateAgeBands]

What do you want the macro to do ??
gowlfow
0
 

Author Comment

by:Jagwarman
Comment Utility
Attached is a sample of the file.

because additional countries can be added into the Static table I need the macro to be flexible so that when  new country is added it will perform the same process to insert the Age bands.

Hope this is self explanatory
Countries.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Question

1)
Did you know that you have 2 module in this workbook Module1 and Module2 and you have following subs
CreateAgeBands
CreateCountries
FormulaInE
Insert

that exist in both modules ? like you have them as duplicates. This should never happen as the macro when it is called will run always the first one and if you make changes to the second they will never be executed.

2)
What is the Sequence in these Macroes so I understand the logic. I know I worked on CreateCountries but the rest new to me,

gowflow
0
 

Author Comment

by:Jagwarman
Comment Utility
Hi Gowflow.

I meant to delete Module 2. I moved everything from 2 to 1.

What is the Sequence in these Macros so I understand the logic. I know I worked on CreateCountries but the rest new to me,

1 CreateCountries
2 CreateAgeBands

Insert is a separate macro that the user will be able to call separately.

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok hv deleted Module2

Now the purpose of this question is I guess after we run CreateContries I see you have at the end added CreateAgeBands

so in practical if you run it now it will create sheets for all the countries that exist as per the rule and then call for CreateAgeBands

and if you have new countries created then you run again CreateCountries who will  create the sheets and will call CreateAgeBands.

etc...

I don't see why you should build a macro that will check to see if there are sheet names with countries and have it call CreateAgeBands as this is done anyway in createCountries.

gowflow
0
 

Author Comment

by:Jagwarman
Comment Utility
The way it works at the moment create age bands does not know whuich sheets to go to tto insert the formula
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
exactly and that is what we need to fix !!!
Actually I do not favor this type of code writing as it is very implicit. It should be explicit like variable declaration and the code knows then exactly what to do not Range("A1") but rather WS.Range("A1") or Sheets("ABC").Range("A1")

when you have code that point to Range("XX") then it depend where the focus is and if the code get the focus somewhere you don't want then here you go all the code will be written onto that sheet that have the focus instead of where it should write it and thus creating a big mess !!!

Can you please post a sample of what these sheet should be and I will re-write it for you ? Did you write the code or ?

gowflow
0
 

Author Comment

by:Jagwarman
Comment Utility
the file attached is the actual file they use with only the dates and country left in, all other data has been removed. For the aging, both of these dates are used and the reason for the Static table is,i) these are the current countries that are valid although new ones will will added in the future and ii) each country may have different a different aging criteria.
Countries.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok fine you posted the same workbook that was posted before If I am not mistaken. That was not my request. I wanted to see a workbook that have gone thru the sub CreateAgeBands to see how it should be at the end to understand the logic.

Please who wrote this code for you ??? and how come you can have a formula like
"=IF(RC[-1]<=30,Static!R7C2,IF(Spain!RC[-1]<45,Static!R7C3,IF(Spain!RC[-1]>45,Static!R7C4)))"

Pointing to a Sheet name (Spain) that does not exist already without having an error escape ?
Are these formulas the way they are written are correct for you ?

gowflow
0
 

Author Comment

by:Jagwarman
Comment Utility
That was not my request. I wanted to see a workbook that have gone thru the sub CreateAgeBands to see how it should be at the end to understand the logic.

[Answer] I can't run it through the Macro because it does not work which is why I have raised this request. However I have inserted the Aging manually to show it.

Please who wrote this code for you ??? and how come you can have a formula like
"=IF(RC[-1]<=30,Static!R7C2,IF(Spain!RC[-1]<45,Static!R7C3,IF(Spain!RC[-1]>45,Static!R7C4)))"

[Answer] the answer to your question is I don't know. I have been asked to try and get this to work for the user but it is proving a lot more difficult than I thought because it's like a moving target.

There will only be 2 sheets initially, the Raw Data and the Static. The macro will add the sheets for the countries.

as you will note each country can have different Age bands [which is why they are listed on the Static sheet] and when the user puts the data on the 'Raw Data' sheet not all of the countries listed in the Static Sheet may be on it.

So, that is why I asked for the Macro that you built 'CreateCountries'.

But then for each country the age bands have to be added to the sheet

The formula for aging is
Pay Dt Age [column S] is: Today - N
Date age [column U] is: Today - D

and then the Age bracket [columns T and V] depends on the country

So, for Spain if the number in Pay DT Age in S is 32 the age in T is 31-45
For United States if the number in Pay DT Age in S is 32 the age in T is 0-100

If a country is on the Raw Data and a sheet gets created but the country is not listed in the Static the user needs a message to tell them no aging available.

I hope this helps and I very much appreciate your assistance and understand your frustration.

Please feel free to disregard the 'CreateAgeBands' and write it your way.

But the formulas should be, in plane speak [depending on which sheet you are on]:
[S2 refers to the Sheet Spain]

If you are on the Spain sheet, look up on the Static sheet for Spain and IF S2<=30, 0-30 If S2 is >than 30 and <=45, 31-45 and if S2 >45 then its >45
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok image much clearer now so this is a big relief to know that we can forget about CreateAgedBand I just need in summary what are the columns to Add

My understanding:
Col S    Pay Dt Age
Col T    Age By Inc Pay Dt
Col U   Date Age
Col V   Age by Date

If yes then look at these definition and forget your business for 1 minute and try to read them again not knowing your buisnees and see from each one if you understand what it should commonly intuitively mean !!! If yes then keep them like this if no then pls find a meaningful intuitive easy more understandable header.

Thnak you.
gowlfow
0
 

Author Comment

by:Jagwarman
Comment Utility
I think maybe this will make the formulas easier to understand

=IF($P$2="Spain",IF(S2<=30,Static!$B$7,IF(S2<45,Static!$C$7,Static!$D$7)))
=IF($P$2="United States",IF(S2<=100,Static!$B$8,IF(S2<150,Static!$C$8,Static!$D$8)))

etc. etc. for each country
0
 

Author Comment

by:Jagwarman
Comment Utility
your understanding of the header is correct
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Don't worry about the formulas will get there 1 step at a time. Pls answer my previous question.
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Yes but they mean NOTHING !!! can you have more specific ? Did you read the last paragraph of my LAST comment ???

Like I guess
Col S     Due Date
Col T     ?? don't know what this field is can you say in plain English what this field should be ?
Col U    Date Age ??? what does it mean ?
Col V     Age by Date ??? also what it means ??


gowflow
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:Jagwarman
Comment Utility
sorry I am not sure I understand your question

But if you are asking can Those 4 headers be renamed then answer is No because they relate to other columns

S and T refer to N.
U and V refer to D
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
I am talking about only the header of these 4 columns like the data that is in row 1 I think you had all your formulas upside down now that I fixed them I can get sense of what is happening here.

Let me tell you in plain English my understanding and you can then correct me.

You have in D Invoice Date (you call it Date)
You have in N Pay Date (you call it Income Pay Date) that is fine.

In S your formulas were leading to a Date but in fact it should lead to a number and in T you should have a range related to S and in U a number and in V a range related to V.

S is the difference between today and Invoice Date
T is where the number in S falls for that specific country in the table of Aging (0-30 or 31-44 or >45) or depending what the age is for that country.
U is the difference between today and Pay Date
V is where the number in U falls for that country 0-30, 31-44 or > 45


If my understanding is correct then we need to change the way Static is setup as you don't have the same criteria all the time
0-30
0-3
0-100

this is a string and cannot be compared your formula
=IF(S2<=30,Static!$B$7,IF(S2<45,Static!$C$7,IF(S2>45,Static!$D$7)))

cannot work as your are assuming here all splits are equal to 30 or less 31 to 44 etc... where in fact you have also 3 and less and 100 and less.

So are you open also to changing Static layout ?? leaving country in Col A for sure !!!

gowflow
0
 

Author Comment

by:Jagwarman
Comment Utility
Col S    Pay Dt Age  - is a calculation. Count of number of days [today minus N so today 6/2/2015 minus N2 05/01/2015 is 32]
Col T    Age By Inc Pay Dt - is the age band this sits in based on the table in Static Sheet.
Col U   Date Age  - is a calculation. Count of number of days [today minus D so today 6/2/2015 minus D2 12/01/2015 is 25]
Col V   Age by Date - is the age band this sits in based on the table in Static Sheet.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
That is what I just explained !!! pls wait for my answer after you post !!! or else we are talking with no progress.

Pls chk my last replay and answer to it.
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Oops my mistake
S = today - pay date (N)
T = Age of pay date
U = Today - Invoice Date (D)
V = Age of Inv Date

Correct ?
If yes in 2 words caln we call them
S = Days to Pay
T = Age to Pay
U = Days to Bill (or Inv)
V = Age to Bill (or Inv)

??
gowflow
0
 

Author Comment

by:Jagwarman
Comment Utility
I can have

S = Pay Date Count
T = Pay Date Age
U = Date Count
V = Date Age
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
GREAT  !!!!!! Hourra an easy one out of the way.

Next, as I mentioned the issue about Static that you did not reply yet I ask again Can I propose a different layout of Static ?

gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok I made some changes and here is the new Static table attached.

Just to give you an idea the formula that you had that looked like this:
=IF(S2<=30,Static!$D$7,IF(S2<45,Static!$E$7,IF(S2>45,Static!$F$7)))

Should in fact be like this to be able to spot the countries correctly and the ranges correctly.
=IF(S2<VLOOKUP(P2,Static!A:F,2,FALSE),VLOOKUP(P2,Static!A:F,4,FALSE),IF(S2<VLOOKUP(P2,Static!A:F,3,FALSE),VLOOKUP(P2,Static!A:F,5,FALSE),VLOOKUP(P2,Static!A:F,6,FALSE)))

So you will notice that we added 2 columns in the beginning Min and Max which is what we need in term of figures to be able to evaluate the data in S and U to know where we stand and based on the figure we get we will choose from the col D, E or F.

If you have no problem with that then I will move to build you the macro and integrate it to CreateCountry.

Let me know.
gowflow
Static.xlsx
0
 

Author Comment

by:Jagwarman
Comment Utility
sorry for late response been tied up

that looks excellent please go ahead and thank you for your help with this
0
 

Author Comment

by:Jagwarman
Comment Utility
Hi Gowflow

I just tested the formula and found that if the number in Spain column S is 30 it returns 31-45 but it should return 0-30

if the number is 45 it returns >45 but it should return 31-45

Regards
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
you mean the new formula ?
gowflow
0
 

Author Comment

by:Jagwarman
Comment Utility
yes sorry the new formula
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
Comment Utility
ok fine no problem.

Here is a file that will do the following:
It has 2 sheets Static and Data Raw that have all your previous data.  with the new formulas in S to V.

In static you have 2 buttons
CreateCountries
When activated it will create countries that are in Raw Data and Static but not created yet and will be created with the same format as in Data Raw column wide and fonts etc... with the new formulas for Col S to V.

FillFormulas
This button when activated will fill formulas in all sheets of the workbook except sheet Static for Col S to V from row 1 to maximum rows in each sheet. This button is useful when data is entered in the country sheets obviously the new data will not have the formula so activating this button will create and extend the new formula to the new rows created.

NOTE
I noticed that in your formulas S and U you have today-N or today-D these are dynamic formulas that will change in time say you open the workbook in 2-3 months from now you will most likely have all your date ranges hit the maximum like >45 or >150 is this what you want ? does it make sense ?
In my mind you maybe want to see something real and static which would be the difference in days between Pay and Date Like N2-D2 would give a number and this would probably when checked would give either 0-30 or 31-44 or >45 and would indicate how it is being paid
Don't you think this would be more realistic ?

Just a though.

Anyway pls check the file and appreciate your comments.
gowflow
Countries-V01.xlsm
0
 

Author Comment

by:Jagwarman
Comment Utility
Hi Gowflow. Many thanks for this what you have done is really excellent. I take your point in your 'Note' but the user has asked for it to be as it is. I am going to move the sheets to a new workbook without any code or formulas so that will overcome that problem. Once again many thanks it's brilliant
0
 

Author Closing Comment

by:Jagwarman
Comment Utility
Excellent Expert
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Your welcome.
gowflow
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now