Free Microsoft Excel 2013 Quick Reference

Macro to create Scatter graph using data from different sheets

Hiall

I have to create 24 Scatter graph every week, so I want towrite a macro that creates all the charts. Can any one please show mehow to write the core code. I am attaching a sample excel workbook.

Thankyou in advance


Hi

Is it possible to create a graph with data from different documents. I have data in two separate documents that I would like to put onto the 1 graph.

Any help would be appreciated.

Hi guys,

Nice easy one for you as I am a clutz ....

Can you tell me how to plot a graph using data from different sheets ?

i.e:

I have 10 sheets in a workbook, each sheet is a certain date and cell K9 in every sheet has a temperature reading that I would like to plot on a graph.

Can you tell me how to do this ?

Adrian

I have data and charts on my worksheet, everything is in one sheet. There's so much data at the moment and I want to seperate this - every month's data should be in different sheets. Now I want to make a chart using the data from different worksheets. Every time, when I'm trying to do that, I got an error which says that there's error in worksheets.
Is there any possibilities to make a chart using data from differents sheets?

And the 2nd question! I have a chart where are monthly averages. I want to add a line which shows the total average (average of monthly averages) to the same chart. How can I do this?

I add an example, so you can understand my problem better.

And sorry for my English

Thanks!

Hi Guys, i am from brazil, i am creating excel plan, but i would like to put some complex formulas on it to create report using data from another sheets.

Sorry for my bad english. My native language is portuguese.

I translated my plan from portuguese for all forum users understand.

Please download my plan here: http://www.witson.com.br/uploads/englishversion.rar

I will use this plan to control payment Receiving (check and slip).

My objective is Sheet (Resumo) Get automatically data from anothers sheets (Loja 1, Loja 2 and Loja 3).
For example Open Shop 1 (Loja 1 in Portuguese), see dates, month 04 go from Number 1 to 18, now go to Resumo Sheet, you will see month 04 checks copied.

I want that Resumo Sheet get data in this sequence:

Get all lines for month 04 checks from Loja 1 Sheet > Get Names Fields and all lines for month 04 slip from Loja 2 and Loja 3 Sheets > Get Names Fields and all lines for month 05 checks from Loja 1 Sheet > Get Names Fields and all lines for month 05 slip from Loja 2 and Loja 3 Sheets > Repeat until Finish

I use 2 macros on my plan, go to Loja 1 Sheet and type in line 67 any number in credit (it automatically will create new line waiting new data entry..) and i also am trying auto update Resumo Sheets using Macro, go to Resumo Sheet and Press Data Update Button (you will see that automatically it will get all 04 month lines from Loja 1 Sheet, but i am not able to develop something better for now, i just started use VBA 4 days ago and i dont know Programming language.. I dont know if better way to create this list is using macro or another way, please help me, i only will be able to use this plan after help from this forum users..

Only to understand, i already tried post this doubt from more than 6 excel forum from brazil and anybody was able to help me, becuase of that i researched better excel forum and translated all my plan to try get help..

If not understand anything, please reply that i will try clarify..

Thanks for All

I would like to create a chart using data from several worksheets.
Basically, each worksheet graphs data for a different time period. For
example
SHEET1:
Store : Customers : Sales : Margine
Data : Data : Data : Data

SHEET2:
Store : Customers : Sales : Margine
Data : Data : Data : Data

etc. I would like to be able to create a chart using say, the sales data.
(SHEET1:Salesata, SHEET2:Salesata).
Is there a way to select the datarange from seperate Worksheets?

I get weekly data of different diseases from different places that I enter
indifferent worksheets in an excel file. I want to see the continuous trend
(disease and place wise). For that, without entering data again, I need to
create a chart using data on different worksheets. How do I do that? Can you
please help? Thanks.

I would like to create a chart using data from several worksheets.
Basically, each worksheet graphs data for a different time period. For
example
SHEET1:
Store : Customers : Sales : Margine
Data : Data : Data : Data

SHEET2:
Store : Customers : Sales : Margine
Data : Data : Data : Data

etc. I would like to be able to create a chart using say, the sales data.
(SHEET1:Sales:Data, SHEET2:Sales:Data).
Is there a way to select the datarange from seperate Worksheets?

I am trying to create a chart using data from two separate worksheets but it
will only allow me to use the information from one.

Hi

I am a beginner, so sorry if this is a really simple question.
I need to create a chart that uses data from 3 sheets, for both the x and y axis, how do I do this? I am using Excel 2007
Thanks

Hi,
I would like to create chart fetching the data from excel sheet.As I am totally new to Macros....I am not sure to how to start with butI am sure that i will make this work easy.Could anyone pls let me know on the same.

I want to selct the data range :
AQ2 to AR2
and AQ35 to AR45
similarly i want it do for 10 charts.
Whether the below one is correct?
******************

Sub Macro2()
Range("AQ32:AQ45").Select
Range("AS32:AS35").Select
End Sub

Is it possible to do calculations using data from one sheet on another completely different sheet?

I would like to make a sheet that sums up data from another sheet(s).

I'm not finding out from the material that I have on the subject.

Thank you.

Richard

This is probably a very stupid question and very easy to do, but how do I write a formula on sheet 2 that will use data input onto sheet 1. I'm new to using Excel & can't figure it out. Please help?!!!

Dear all,

I am trying to do a summation using information from different sheets.

I essentially have information on the names and compensation of the employees of company A----data for different years come in different sheets-----e.g. here is an example of how my data are structured for year 2007

******** ******************** ************************************************************************>Microsoft Excel - sample.xls___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=
ABCD1Company A: Year 20072NameRolesalarybonus3georgeFD1074John CEO1585 ED Total2396 ED Average4757jimIndependent NED788StevenIndependent NED342007
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

and here for year 2006:

******** ******************** ************************************************************************>Microsoft Excel - sample.xls___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=
ABCD1Company A: Year 20062NameRolesalarybonus3georgeFD8304John CEO5205 ED Total2126 ED Average477jimIndependent NED348BillIndependent NED6112006
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

What I need to do is to find a way to calculate the sum of salary and bonus for company A for each year separately and then have it in a different sheet or file e.g. according to the information provided above the sum of salary for year 2006 is 22 (8+5+3+6)----please note that the numbers in cells A5 and A6 have to be ignored since they do not correspond to any individuals.

Similarly, for year 2007, the sum of salary is 35 (10+15+7+3)

Here is an example of how i would like to have my data organized

******** ******************** ************************************************************************>Microsoft Excel - Book1___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=
ABCDE1CompanyYearsum of salarysum of bonusnumber of indiviuals paid2companyA2007352743companyA200622654Sheet1
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
(Column E just reports the number of individuals paid in each year)

many thanks in advance

This has been causing problems to me for a long time so any help will be greatly appreciated

C.

Hi, anyone can help me with a formula that can lookup data from different sheets?

i got this summary excel file which will lookup the date and auto update the data from the daily data excel file.

However, my data are located in many different sheets, and 1 sheet i got 2 ref no. in it. In order for me to put in the lookup formula, i got to change the sheet name and the column no. for every cell.

Is there any formula that will allow me to auto fill up the cells when i drag it down from the first cell?

Please refer to the example excel files for more info, from there you can see that i changed the sheet name and column no. in every cell, eg: TP1-TP2, TP3-TP4 and column no. 7 to 12

Thanks for the help.

Dear expert,

Please help me on how to create macro to plot graph using data from sample attached data. The highlighted column with red color to be used y-axis and data as x- axis by selecting one parameter from cell column. it is also important to plot two graphs on same axis for comparison.

I am trying to use VB to create 50 charts each with 3 lines using data from 3 different excel spreadsheets in the same workbook. I am able to create one chart with 3 lines using data from the three spreadsheets, this is good. However I exprience problems when i try to insert a loop to create 50 charts that correspond the the rows in each spreadsheet. I am new to VB and am very inexperienced with VB. In each worksheet my data is arranged in rows. row 1 of each worksheet has the header information and rows 2 through row 50 have my corresponding data. for example A2 lists the name and F2 through T2 lists the data. Can anyone please help me, I have tried a bunch of different things but nothing seems to alllow me to be able to create multiple charts which correspond to each row of data?
Thank you


	VB:
	
 
Sub Chart2PPT() 
    Dim arow As Integer 
    Dim acol As Integer 
    Dim StartPoint As Integer 
    Dim EndPoint As Integer 
    Dim rStartPoint As String 
    Dim rEndPoint As String 
     
    StartPoint = 2 
    EndPoint = 4 
     
    For arow = 2 To 5 
        rStartPoint = "f" & StartPoint 
        rEndPoint = "t" & EndPoint 
         
         
         
        Charts.Add 
         
        ActiveChart.ChartArea.Select 
        ActiveChart.ChartType = xlLine 'Type of graph
        ActiveChart.SetSourceData Source:=Sheets("Appts").Range(StartPoint & ":" & EndPoint), PlotBy _ 
        :=xlRows 'data source
        ActiveChart.SeriesCollection(1).XValues = Sheets("Spwr").Range("f1:t1") 'naming the x-axis
        ActiveChart.SeriesCollection(1).Name = "Appts" ' Name of 1st data series 1
         
        With ActiveChart.SeriesCollection(1) 'put labels on 1st data series
            .HasDataLabels = True 
            .DataLabels.NumberFormat = "##" 
        End With 
         
        With ActiveChart.Axes(xlValue) 
            .HasMajorGridlines = False 
            .HasMinorGridlines = False 
        End With 
        ActiveChart.PlotArea.Select ' Background of graph
        With Selection.Border 
            .ColorIndex = 16 
            .Weight = xlThin 
            .LineStyle = xlContinuous 
        End With 
        Selection.Interior.ColorIndex = xlNone 
         
        ActiveChart.SeriesCollection.NewSeries 
        ActiveChart.SeriesCollection(2).Name = "salespower" 
        ActiveChart.SeriesCollection(2).Values = Sheets("Spwr").Range("f2:t2") 
         
        With ActiveChart.SeriesCollection(2) 'put labels on 2nd line
            .HasDataLabels = True 
            .DataLabels.NumberFormat = "##" 
        End With 
         
        ActiveChart.SeriesCollection.NewSeries 
        ActiveChart.SeriesCollection(3).Name = "Tests" 
        ActiveChart.SeriesCollection(3).Values = Sheets("Tests").Range("f2:t2") 
         
        With ActiveChart.SeriesCollection(3) 'put labels on 3rd line
            .HasDataLabels = True 
            .DataLabels.NumberFormat = "##" 
        End With 
         
         
        ActiveChart.Legend.Position = xlLegendPositionBottom 
        ActiveChart.HasTitle = True 
        ActiveChart.ChartTitle.Text = Sheets("Spwr").Range("d2:d2") 
        ActiveChart.ChartTitle.Font.Bold = True 
         
    Next arow 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


could anyone help me with this, im very confuzed.

i currently have a table, i want the programme to take data from whichever cells i highlight from on row of data. and to create a graph on a seperate worksheet from this data. it is only a change of one row down for each new set of data.

and it needs done for like 2000 records so would be good if it could loop itself.

i have recorded a macro to do it for one set of data but dont know how to make the range different each time.

my code currently looks like this...


	VB:
	
 test01() 
     '
     ' test01 Macro
     ' Macro recorded 26/01/05 by Test User
     '
     
     '
    Range("G3,H3,I3,J3,K3").Select 
    Charts.Add 
    ActiveChart.ChartType = xlLineMarkers 
    ActiveChart.SetSourceData Source:=Sheets("Table").Range( _ 
    "G3,H3,I3,J3,K3,K2,J2,I2,H2,G2"), PlotBy:=xlColumns 
    ActiveChart.SeriesCollection(1).XValues = "=(Table!R2C4,Table!R3C4)" 
    ActiveChart.SeriesCollection(2).XValues = "=(Table!R2C4,Table!R3C4)" 
    ActiveChart.SeriesCollection(3).XValues = "=(Table!R2C4,Table!R3C4)" 
    ActiveChart.SeriesCollection(4).XValues = "=(Table!R2C4,Table!R3C4)" 
    ActiveChart.SeriesCollection(5).XValues = "=(Table!R2C4,Table!R3C4)" 
    ActiveChart.SeriesCollection(1).XValues = "=(Table!R3C4,Table!R2C4)" 
    ActiveChart.SeriesCollection(2).XValues = "=(Table!R3C4,Table!R2C4)" 
    ActiveChart.SeriesCollection(3).XValues = "=(Table!R3C4,Table!R2C4)" 
    ActiveChart.SeriesCollection(4).XValues = "=(Table!R3C4,Table!R2C4)" 
    ActiveChart.SeriesCollection(5).XValues = "=(Table!R3C4,Table!R2C4)" 
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Graphs" 
    With ActiveChart 
        .HasTitle = False 
        .Axes(xlCategory, xlPrimary).HasTitle = True 
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Test Year" 
        .Axes(xlValue, xlPrimary).HasTitle = True 
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Parts Per Million" 
    End With 
    ActiveChart.HasDataTable = False 
    ActiveWindow.Visible = False 
    Windows("draft sheet.xls").Activate 
    Range("A1").Select 
    ActiveSheet.ChartObjects("Chart 55").Activate 
    ActiveChart.ChartArea.Select 
    ActiveSheet.Shapes("Chart 55").IncrementLeft -104.25 
    ActiveSheet.Shapes("Chart 55").IncrementTop -25.5 
    ActiveWindow.Visible = False 
    Windows("draft sheet.xls").Activate 
    Range("A1").Select 
    Sheets("Table").Select 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
please note it is only cells which are currently on row 3 which i want to move down one. i want G3,H3,I3,J3,K3 to become G4,H4,I4,J4,K4.
but i want K2,J2,I2,H2,G2 to stay the same, as they provide the labels.

thanks

Hi Guys,

I'll pre-empt this by saying that I've never really used Excel for much apart from just recording very simple data, and so the following is quite a leap for me so please be gentle

I have a workbook to help me track of the ranking of a website. The workbook contains several sheets, and basically the data looks a bit like this:

HTML Code: 
                        Google     Yahoo      MSN
Search Term
Term A                    1           3        7
Term B                    7           10       1
Term C                    3           1        10
Although the existing info is useful, what I would like to be able to do is display the info via a chart so that it's visually easier to pic out any trends.

Would someone be able to point me in the right direction to get me started? Is it possible to create charts across multiple workbooks?

There is also a slight complication in that from time to time I add / remove search terms from the results.... as a result, the cell position of a search term may differ from sheet to sheet.

As a result, I guess I need to do something which says: "look for this search term in each sheet, then compare the data, and render in a graph" as opposed to saying "go to cell B7 in each sheet"... as that might display the wrong data - hope that makes sense?)

Any help would be really, really appreciated!

Thanks!
Kessa

Sorry - I forgot to say that I'm using Excel 2007 and 2003

After searching this forum, I think there have already been a few similar posts, but I wasn't entirely sure that they are the same problem as I have.

I have a workbook with three sheets, each of which has matched data. I want to create a bar graph for each of the five rows from the three sheets (i.e. with six columns per graph (two from each sheet)).

I've attached a workbook and an image of how I would like the graph to look. Ideally, I want the x axis values to be the sheet names.

Many thanks

Hi, first time poster,

i have a file which contains a number of sheets(+-1.000), each with 34 lines of data. I would like to copy paste this data into a 'master sheet', creating 34.000 lines of data.

I'm encountering 2 problems when trying to work with record macro.

1. Upon selecting a sheet, the name of the sheet is recorded and the macro doesn't know he needs to select sheet n+1 for the next cycle in the loop
2. Upon pasting the data from a sheet into the master sheet, the cell range is recorded when using the record macro. The first time I select cell A1 and paste, however the second time A35 should be selected and pasted, then A69 etc.

Any help on the matter would be greatly appreciated. Thank you for your time.

Regards,

Simon De Meester

I should have read a few more posts before I asked. Looks like the answer I
will need (I have not read it yet) is he

http://peltiertech.com/Excel/ChartsH...iffSheets.html

"Ezlpo" wrote:

> I am trying to make a chart (line graph) that uses data from many different
> worksheets.
>
> The graph is being created to plot sales data week to week for many
> different salespeople. Each week is its own worksheet.
>
> There are around 70 sales people, but for the report I am only interested in
> 8-25 (but potentially all 70). Their names are listed in column A, but
> because there are so many of them they are not always in the same row as they
> were the week (read: worksheet) prior.
>
> Now I know I can take the time to create a graph for each salesperson, but I
> was hoping there would be an easier solution. I do not have crystal reports
> (not that I would know how to use them) and I don't have any skill using the
> Virtual Basic features (although I fear that for this I will need to learn
> some).
>
> Thank you in advance.
>
>

Is it possible to use data from one workbook (a master listing of restaurants
and managers) when creating formulas in different workbooks? What I would
like to do is when I am creating new workbooks for various reports, I would
like to be able to create a forumla to populate the restaurant and managers
column in the new workbooks with the information from the master listing
workbook. Is that possible?

Thanks for you help
Steven Edmonds

Good morning everyone
Not sure if this is doable or not....

I have a spreadsheet with data in the range: E1:S100.
What I would like to do is this:
Create a macro that creates a graph for each row between 2 to 100 where row 1 is the header for each graph.

Let me know if you need any more detail.

Thanks in advance

Amy xx

Is it possible to use data from one workbook (a master listing of restaurants
and managers) when creating formulas in different workbooks? What I would
like to do is when I am creating new workbooks for various reports, I would
like to be able to create a forumla to populate the restaurant and managers
column in the new workbooks with the information from the master listing
workbook. Is that possible?

Thanks for you help
Steven Edmonds
edmondssteven@hotmail.com