graph the results of multiple spreadsheets
Attached are a number of spreadsheets. Each spreadsheet has the total loading
fees and card load amounts per month for both Mastercard and Visa cards.
If you open one of them up, say the latest: October2011.xls, you will see
various categories.
What I'm primarily interested in tracking and showing trends for, are the
dollar and Euro amounts.
For example, MasterCard has only two sets of figures: one for Load in USD (the
amount of dollars we have loaded onto Mastercards for October 2011) and Fees in
USD (the total amount of fees we charged for loading Mastercards in October
2011).
Looking at each spreadsheet you will see the amount changes. I would like to
graph these totals in a line graph, like what you see here:
http://i1-scripts.softpedia-static.com/screenshots/2D-3D-Line-Graph-1493.png
Where one colored line would represent Mastercard Loads in USD plotted per
month and another color line would represent the MasterCard loading fees we
charged, plotted per month.
I would like to do the same thing (on this same chart) with the Visa card
loads, with lines tracking the Visa monthly totals (the USD totals only) for Fees and Loads.
And finally, one line plotting the Visa/MasterCard total.
And just to add a tricky part, I would like to be able to add months to the
graph as I go along throughout the year creating new monthly total spreadsheet
- each monthly spreadsheet created updating the chart.
I can add this last part part as a related question, if you like.
And, a subsequent related question, will be to come the monthly totals automatically. Currently I do it manually. MontlyTotalsJan-Oct2011.zip
fees and card load amounts per month for both Mastercard and Visa cards.
If you open one of them up, say the latest: October2011.xls, you will see
various categories.
What I'm primarily interested in tracking and showing trends for, are the
dollar and Euro amounts.
For example, MasterCard has only two sets of figures: one for Load in USD (the
amount of dollars we have loaded onto Mastercards for October 2011) and Fees in
USD (the total amount of fees we charged for loading Mastercards in October
2011).
Looking at each spreadsheet you will see the amount changes. I would like to
graph these totals in a line graph, like what you see here:
http://i1-scripts.softpedia-static.com/screenshots/2D-3D-Line-Graph-1493.png
Where one colored line would represent Mastercard Loads in USD plotted per
month and another color line would represent the MasterCard loading fees we
charged, plotted per month.
I would like to do the same thing (on this same chart) with the Visa card
loads, with lines tracking the Visa monthly totals (the USD totals only) for Fees and Loads.
And finally, one line plotting the Visa/MasterCard total.
And just to add a tricky part, I would like to be able to add months to the
graph as I go along throughout the year creating new monthly total spreadsheet
- each monthly spreadsheet created updating the chart.
I can add this last part part as a related question, if you like.
And, a subsequent related question, will be to come the monthly totals automatically. Currently I do it manually. MontlyTotalsJan-Oct2011.zip
but the files posted don't have the same format some have the total some have more columns on theright which one is to use as template ?
gowlfow
gowlfow
ASKER
yes, but you will see that most of them follow the same layout as October's spreadsheet, and that is the format they will be in for future months as well. Besides, you are going to be creating a different spreadsheet (aren't you) from those attached. And, as mentioned, you're only interested in the sums which start with Mastercard's totals.
Sorry it is too much time demanding cannot attend at this time. Appreciate if other issue pending are closed.
gowflow
gowflow
ASKER
understood.
the other question will be addressed soon as pickups by Josef are resuming shortly and will be able to test. thank you.
the other question will be addressed soon as pickups by Josef are resuming shortly and will be able to test. thank you.
there was still an outstanding with HMF/Mastercard if I recall You can address this one if you wish
gowflow
gowflow
ASKER
yes, there is and, okay, I will get that to you at some point today - thanks!
How are you doing? Hope things are going well for you. Busy?
How are you doing? Hope things are going well for you. Busy?
fine tks, Sorry for the graphs but it look too complicated at least to understand I read your post at least 6 times and couldn't grasp it ... If you can put it in simpler way or cut it in pieces then ok I'll look at it but the way it is ... mmm sorry it need a brainny that I guess am not.
gowflow
gowflow
ASKER
okay, I'll try.
I'm just wanting a line graph following each of the following amounts:
- Mastercard Load in USD
- Mastercard Fees in USD
- Visa Wired Load in USD
- Visa Wired Fees in USD
- Western Union Load in USD
- Western Union Fees in USD
and finally
- Visa/MC Total Load in USD
- Visa/MC Total Fees in USD
The line graph would cover from the first spreadsheet: November 2010 and on
Each point of each line in the graph (there are 8 lines - from the above) represents a month.
Does that help?
I'm just wanting a line graph following each of the following amounts:
- Mastercard Load in USD
- Mastercard Fees in USD
- Visa Wired Load in USD
- Visa Wired Fees in USD
- Western Union Load in USD
- Western Union Fees in USD
and finally
- Visa/MC Total Load in USD
- Visa/MC Total Fees in USD
The line graph would cover from the first spreadsheet: November 2010 and on
Each point of each line in the graph (there are 8 lines - from the above) represents a month.
Does that help?
Yes much much better !!!
Now is the data scattered in the whole workbook or it is grouped in 1 worksheet ? like do we need to open 2 workbooks and look in multiple sheets to get these totals or you have gathered them in 1 sheet or several sheets ? to do a graph you need first to cumulate all the data you want like oyu did now in a sheet then we can build the graph around it
So how is it ?
gowflow
Now is the data scattered in the whole workbook or it is grouped in 1 worksheet ? like do we need to open 2 workbooks and look in multiple sheets to get these totals or you have gathered them in 1 sheet or several sheets ? to do a graph you need first to cumulate all the data you want like oyu did now in a sheet then we can build the graph around it
So how is it ?
gowflow
ASKER
The spreadsheets I sent you are all separate workbooks, but we could combine them into one if you want. The graph could be the front, main, sheet of the workbook.
The idea then, is that when a new sheet (tab) is created - say the next one for November 2011 - the graph would automatically add it's data, drawing the lines and bringing the graph up to date with the latest edition.
The idea then, is that when a new sheet (tab) is created - say the next one for November 2011 - the graph would automatically add it's data, drawing the lines and bringing the graph up to date with the latest edition.
yes possible but you need to have all the sheets there from Jan to Dec even if say Jun to Dec are empty but they would have the same format and we would account for them. Gather the workbook make it clean (all same format) post it here and I will work on a clean 1 workbook with 12 sheets + 1 that will call Graph
gowflow
gowflow
ASKER
it is attached. for now, don't use any data above row 13 on any of the sheets in the attached workbook. Monthly-Totals.xls
Noted your file but cannot relate to these pls put the column number in front of each items
Column Item
Mastercard Load in USD
Mastercard Fees in USD
Visa Wired Load in USD
Visa Wired Fees in USD
Western Union Load in USD
Western Union Fees in USD
Visa/MC Total Load in USD
Visa/MC Total Fees in USD
gowflow
Column Item
Mastercard Load in USD
Mastercard Fees in USD
Visa Wired Load in USD
Visa Wired Fees in USD
Western Union Load in USD
Western Union Fees in USD
Visa/MC Total Load in USD
Visa/MC Total Fees in USD
gowflow
ASKER
Mastercard Load in USD = B14
Mastercard Fees in USD = C14
Visa Wired Load in USD = B17
Visa Wired Fees in USD = C17
Western Union Load in USD = B18
Western Union Fees in USD = C18
Visa/MC Total Load in USD = B23
Visa/MC Total Fees in USD = C23
Mastercard Fees in USD = C14
Visa Wired Load in USD = B17
Visa Wired Fees in USD = C17
Western Union Load in USD = B18
Western Union Fees in USD = C18
Visa/MC Total Load in USD = B23
Visa/MC Total Fees in USD = C23
OK fine I was looking row 13 and up your looking row 13 down !!! big diffrence.
Fine Do you need VBA solution or I can make a nice template for you and then you can plug new data in the relevant sheets and you get the graphs ? what are you looking for exactly ?
gowflow
Fine Do you need VBA solution or I can make a nice template for you and then you can plug new data in the relevant sheets and you get the graphs ? what are you looking for exactly ?
gowflow
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Wow! Impressive.
Questions:
I don't want to include months that we haven't gotten to yet. How can I remove that data (Nov and Dec 2011) from the graph? It makes the trends look worse than they are by going down to zero.
Will I be able to add months ongoing?
How are we going to work it so that I can use this and get graph results for months and months to come?
Thank you!
Questions:
I don't want to include months that we haven't gotten to yet. How can I remove that data (Nov and Dec 2011) from the graph? It makes the trends look worse than they are by going down to zero.
Will I be able to add months ongoing?
How are we going to work it so that I can use this and get graph results for months and months to come?
Thank you!
ok let me come up with something then
gowflow
gowflow
ASKER
Also wanted to show Nov and Dec 2010. I need those two months.
I can ask this as a related question if you want:
I would like to show two years at a time as a comparison of year to year trends, so Nov 2011 could begin another line just below November 2010's line but, of course, we need to differentiate between the two years somehow. -
I can ask this as a related question if you want:
I would like to show two years at a time as a comparison of year to year trends, so Nov 2011 could begin another line just below November 2010's line but, of course, we need to differentiate between the two years somehow. -
So now we are doing a soup !!!
we broke 2 graphs one for the load the other for the fees and for each one you want a comparative by year right ? and you need it as line ? can't picture how it is going to look I'll hv to think about it. Meanttime if you want 2 years then I need all of 2010 !! not just Nov and Dec 2010
pls post Jan to Dec 2010 as it is changing the whole scope now I was already developing VBA to cut short the zeros !!!!
Do you mind if we come up with other or maybe a mix of line and something else like are you open or you want strictly line ??? and are you ok with the split fees and load as a principle ?
gowlfow
we broke 2 graphs one for the load the other for the fees and for each one you want a comparative by year right ? and you need it as line ? can't picture how it is going to look I'll hv to think about it. Meanttime if you want 2 years then I need all of 2010 !! not just Nov and Dec 2010
pls post Jan to Dec 2010 as it is changing the whole scope now I was already developing VBA to cut short the zeros !!!!
Do you mind if we come up with other or maybe a mix of line and something else like are you open or you want strictly line ??? and are you ok with the split fees and load as a principle ?
gowlfow
Also an other issue
instead of having all these worksheets can we just cut it to 3 ?
Graphs
This Year
Last Year
If yes then I would need all the fields that you need for 1 month I guess the ones we are plotting for graph and you may also have others as I see you table has many columns so if you give me what is necessary then you would fill in every month the details and it would update your graphs automatically ... you can then think of automation after that
gowlfow
instead of having all these worksheets can we just cut it to 3 ?
Graphs
This Year
Last Year
If yes then I would need all the fields that you need for 1 month I guess the ones we are plotting for graph and you may also have others as I see you table has many columns so if you give me what is necessary then you would fill in every month the details and it would update your graphs automatically ... you can then think of automation after that
gowlfow
ASKER
Yes, 3 pages will be fine, but when the third year rolls around what do we do? Start dropping off months as we come to them for the third year (at least as far as graphing is concerned)?
Let me see if I can find all 2010 months.
Not attached to lines. I think it shows the trends best. Splitting fees and loads is a good idea.
I'll let you know what fields I need in a minute.
Let me see if I can find all 2010 months.
Not attached to lines. I think it shows the trends best. Splitting fees and loads is a good idea.
I'll let you know what fields I need in a minute.
ASKER
As mentioned earlier, 3 pages will be fine, but that only accounts for two years. While we really need to graph only two years, I may be doing this job for many so need the ability to track and graph for months and years to come.
The fields I need to graph start on row 14 Cols B and C as they have the data I need to graph. So it is B14 and C14. B17 and C17, B18 and C18, B19 and C19, and B23 and C23.
The rows above that (row 12 and up) show where new card applicants are coming from (I removed the names) - and now that I think about it, the number of rows could increase slightly over time, but at this point am not wanting to graph it.
Still looking if I can get you the rest of 2010. I may not be able to and then we just have to start with Nov 2010.
The fields I need to graph start on row 14 Cols B and C as they have the data I need to graph. So it is B14 and C14. B17 and C17, B18 and C18, B19 and C19, and B23 and C23.
The rows above that (row 12 and up) show where new card applicants are coming from (I removed the names) - and now that I think about it, the number of rows could increase slightly over time, but at this point am not wanting to graph it.
Still looking if I can get you the rest of 2010. I may not be able to and then we just have to start with Nov 2010.
ASKER
I doubt I'm going to be able to come up with the rest of 2010 any time soon. The reason I'm starting on Nov 2010 is because that is when I first started compiling data for the months. Another person did it before me and I may not have access to her files. Please, if you can, just start with Nov 2010 for now and maybe we can include previous months later. If not, it will be okay. Thank you!
ok one quesiton though for the multiple years
How would yo uenvision a graph that is plotting several items by months for 2 years ??? isn't it a bit too croudy ???
I would suggest 2 years per month graph per item !! so you would get 8 graphs is that ok ? as I can't see how you would be able to graph 4 items in 2 years like 2 rows per items this would be 8 lines that cold come up and down its chaos !!!
Now as far as keeping years let me think of a way for sure if you choose the 8 years it wil be much simpler to think for several years but if you stick to putting all itmes in 1 graph then .... :( I guess u'll need to find a major wizard to pull this one
gowflow
How would yo uenvision a graph that is plotting several items by months for 2 years ??? isn't it a bit too croudy ???
I would suggest 2 years per month graph per item !! so you would get 8 graphs is that ok ? as I can't see how you would be able to graph 4 items in 2 years like 2 rows per items this would be 8 lines that cold come up and down its chaos !!!
Now as far as keeping years let me think of a way for sure if you choose the 8 years it wil be much simpler to think for several years but if you stick to putting all itmes in 1 graph then .... :( I guess u'll need to find a major wizard to pull this one
gowflow
ASKER
"How would yo uenvision a graph that is plotting several items by months for 2 years ??? isn't it a bit too croudy ???"
- Maybe use the same colors but different symbols for the two different years of the same category
But if you must, and maybe it is best, that each year - the first graph beginning in Nov 2010 and ending in Dec 2011 - has it's own graph with each year showing above the last year. Same with the fees. That way you can still see comparisons between the years and fees without having a chaotic mess of lines.
Will that work?
- Maybe use the same colors but different symbols for the two different years of the same category
But if you must, and maybe it is best, that each year - the first graph beginning in Nov 2010 and ending in Dec 2011 - has it's own graph with each year showing above the last year. Same with the fees. That way you can still see comparisons between the years and fees without having a chaotic mess of lines.
Will that work?
ASKER
Also, don't forget to not graph (or show lines on the graph) for months where there are no totals for yet. BTW, one of the next projects - should you choose to accept it - is to sum totals of succeeding months and input those totals in this spreadsheet automatically by a push of a button. Right now, I come to all these totals manually. :|
ASKER
ok, maybe we can just forget Nov and Dec 2010 if you want. Up to you.
ok for me the year start Jan and end Dec !!! we can start 2011 and have it so it holds several years.
Still with the lines you did not tell me you want 2 graphs each 8 lines (ty and Ly) one for load and one for fees or you go with my suggestion ?
8 graphs each showing 1 item comapring ty to Ly ?
gowflow
Still with the lines you did not tell me you want 2 graphs each 8 lines (ty and Ly) one for load and one for fees or you go with my suggestion ?
8 graphs each showing 1 item comapring ty to Ly ?
gowflow
ASKER
Lets go with one graph = 1 year just as you have it given it to me already.
For Jan 2012 we start a new set of graphs: one for loads and one for fees.
Reduce the size of the graphs to 1/3 or 1/4 size so that we can fit 4 loading graphs in 2 columns and 2 rows and 4 fee graphs underneath the loading graphs also in 2 columns and 2 rows.
The main thing I want to compare is how mastercard, western union and visa loads and fees compare with each other. The second thing I want to compare is how they compare year to year.
Okay?
For Jan 2012 we start a new set of graphs: one for loads and one for fees.
Reduce the size of the graphs to 1/3 or 1/4 size so that we can fit 4 loading graphs in 2 columns and 2 rows and 4 fee graphs underneath the loading graphs also in 2 columns and 2 rows.
The main thing I want to compare is how mastercard, western union and visa loads and fees compare with each other. The second thing I want to compare is how they compare year to year.
Okay?
ASKER
So I'm thinking you do not have to have the data along the top of the same sheet where the graphs are. You can put this on another sheet where it lists each month grouped by years.
ASKER
on previous question where you created the button: SUM-UP Completed Batches, I am getting an error: Method Range of Object_worksheet failed, with this highlighted in yellow:
MinRow = WS.Range(Mid(LastFormula, InStr(1, LastFormula, ":") + 1, Len(LastFormula) - InStr(1, LastFormula, ":") - 1)).Row + 1
I've close and restarted excel and still get the error.
When you have time. Thank you.
MinRow = WS.Range(Mid(LastFormula, InStr(1, LastFormula, ":") + 1, Len(LastFormula) - InStr(1, LastFormula, ":") - 1)).Row + 1
I've close and restarted excel and still get the error.
When you have time. Thank you.
Sorry JaseSt lets agre once and for all on a system !!!
You cannot post a qquestion and open a debate and start changing as we go along the quesiton to endup with a project not a question !!!!! You open a question on specific issue you close the question then open an other one !!! I am strictly fedup with your disorganised way of doing and spending too much time reqorking your thoughts !!!
When I answer 1 questio nfor you I can answer over 40 question for others for the amount of time !!!
AND PLEASE do not link other issues on an exisitng issue. You have a problem with an previous quesiton open a related question that will deal with !!!
UNDERSTOOD ?
gowflow
You cannot post a qquestion and open a debate and start changing as we go along the quesiton to endup with a project not a question !!!!! You open a question on specific issue you close the question then open an other one !!! I am strictly fedup with your disorganised way of doing and spending too much time reqorking your thoughts !!!
When I answer 1 questio nfor you I can answer over 40 question for others for the amount of time !!!
AND PLEASE do not link other issues on an exisitng issue. You have a problem with an previous quesiton open a related question that will deal with !!!
UNDERSTOOD ?
gowflow
ASKER
Ok, I agree with you. Just let me know if you feel the back and forth is really getting into another question.
Therefore, I close this one and will open another one that reformats the final product as mentioned above.
Therefore, I close this one and will open another one that reformats the final product as mentioned above.
ASKER
If you wish to continue, gowflow, here is the related question:
https://www.experts-exchange.com/questions/27444387/continuation-of-graph-the-results-of-multiple-spreadsheets.html
https://www.experts-exchange.com/questions/27444387/continuation-of-graph-the-results-of-multiple-spreadsheets.html
ASKER
Correction: I'm only interested in graphing the USD amounts for Visa, Western Union and Mastercard totals. Not the Euro amounts.