If function using range of data Results

'checks if volunteer is already listed as having this shift
Dim rngColor As Range
For Each rngColor In ws.Range("PivotTable")
    For k = 1 To 15
    With ws
    If rngColor.Value = Me.Controls("cmbVol" & k).Value And .Cells(rngColor.Row, 2).Value =
Me.cmbEventName.Value And .Cells(rngColor.Row, 3).Value = Me.cmbStartTime.Value And .Cells(rngColor.Row, 4).Value =
Me.cmbEndTime.Value Then
        Me.Controls("cmbVol" & k).SetFocus
        MsgBox "This volunteer is already recorded as working this shift."
        Exit Sub
    End If
    End With
    Next k
Next rngColor
The code is above. For whatever reason it comes up with an error for the workbook when trying to check this function. Workbook is included. This code can be found in form "ShiftPop"

I used it exactly like I use range every time, although normally I don't have a for...next and if in the middle of it.

I can change the name range just keep in mind it is dynamic. The pivot table range I use for a sheet I didn't attach. I was just borrowing since it should be essentially the same range. I tried naming a new range and it didn't fix anything.

The function of it should be:

Any number of events, volunteers, and shifts have been entered using the forms. The "Populated Shifts" sheet is basically a large raw data sheet that other things are calculated based on. When using ShiftPop it will enter the data on a new row for every person. The problem trying to be solved is that sometimes you forget what data you have entered and not entered. So it should check that the exact shift you have entered with each volunteer is not in the "Populated Shifts" already. It should check for each volunteer listed in the combo boxes.

If anything is unclear let me know.


Posted before and got great help...hope someone can come through for me again...

Have some VBA code that imports a selection from a closed workbook. Works great...I highlight my range in my current workbook, hit the macro button, and it imports the selected range from a closed workbook into my current workbook.

I am basically trying to apply the same principle in a new application, however instead of importing a selection, I want to import multiple ranges (they are always the same) from a closed workbook into my workbook.

I have tried every way I know how to modify the code to do this (but I'm a beginner). Gave up after trying and searching different posts. Can't seem to get it right.

Instead of selecting a range, I want to import these ranges when I run the macro (H39:N41, H48:N50, H54:N56)

Can someone help me make the changes I need to make to the code..

Sub TestCopyRangeFromWB()
Dim objTargetRange As Range, strSourceWB As String, strSourceWS As String
    ' determine the source workbook full filename
    If Len(ThisWorkbook.Path) = 0 Then Exit Sub ' this workbook is not saved
    strSourceWB = ThisWorkbook.Path & Application.PathSeparator & "Copy of " & ThisWorkbook.Name
    ' determine the selected cell range
    On Error Resume Next
    Set objTargetRange = Selection
    On Error GoTo 0
    If objTargetRange Is Nothing Then Exit Sub ' no cell range selected
    ' determine the worksheet name
    strSourceWS = objTargetRange.Parent.Name
    Application.ScreenUpdating = False
    If Not CopyRangeFromWB(strSourceWB, strSourceWS, objTargetRange.Address(False, False, xlA1), objTargetRange) Then
        MsgBox "Failed to copy information from " & strSourceWB, vbInformation
    End If
    Application.ScreenUpdating = True
    Set objTargetRange = Nothing
End Sub

Function CopyRangeFromWB(strSourceWB As String, varSourceWS As Variant, strSourceRange As String, _
    rngTarget As Range, Optional blnCopyFormats As Boolean = False) As Boolean
Dim strWB As String, wb As Workbook, ws As Worksheet, rng As Range
Dim p As Long, blnCloseWB As Boolean
' copies the values from a workbook/worksheet range to a given target range
' varSourceWS can be a worksheet name or a worksheet index number
' it is recommended to turn off screen updating before using this function
    CopyRangeFromWB = False
    ' validate input
    If Len(strSourceWB) = 0 Then Exit Function
    If Len(varSourceWS) = 0 Then Exit Function
    If Len(strSourceRange) = 0 Then Exit Function
    If rngTarget Is Nothing Then Exit Function
    Application.StatusBar = "Copying data from " & strSourceWB & "..."
    blnCloseWB = True
    strWB = vbNullString
    p = InStrRev(strSourceWB, Application.PathSeparator)
    If p > 0 Then
        strWB = Mid(strSourceWB, p + 1)
    End If
    If Len(strWB) > 0 Then
        On Error Resume Next
        Set wb = Workbooks(strWB) ' check if workbook is open
        On Error GoTo 0
    End If
    If wb Is Nothing Then
        On Error Resume Next
        Set wb = Workbooks.Open(strSourceWB, , True) ' open a closed workbook, read only
        On Error GoTo 0
        blnCloseWB = False
    End If
    If Not wb Is Nothing Then
        On Error Resume Next
        Set ws = wb.Worksheets(varSourceWS) ' get the source worksheet
        On Error GoTo 0
        If Not ws Is Nothing Then ' found the source worksheet
            On Error Resume Next
            Set rng = ws.Range(strSourceRange) ' get the source range
            On Error GoTo 0
            If Not rng Is Nothing Then
                rngTarget.PasteSpecial xlPasteValues
                If blnCopyFormats Then
                    rngTarget.PasteSpecial xlPasteFormats
                End If
                Application.CutCopyMode = False
                Set rng = Nothing
                CopyRangeFromWB = True
            End If
            Set ws = Nothing
        End If
        If blnCloseWB Then
            wb.Close False ' close workbook without saving any changes
        End If
        Set wb = Nothing
    End If
    Application.StatusBar = False
End Function

Hi All,

I'm hoping someone can point me in the right direction.

I have stumbled across Ron De Bruins website and have found exactly the sort of thing I need to automate the analysis of a large piece of activity based costing work I am about to embark on. It just needs one small tweak which is out of my league I am afraid.

I am using the code from here http://www.rondebruin.nl/copy3.htm, specifically the "Merge a range from every workbook you select (below each other)" piece.

The tweak I want to make is that I want to transpose the data from two blocks of 4 col x 17 rows, into one long row.

I just do not know where to start, I've scoured the internet and my trusty VBA for dummies but have to admit I'm getting no where.

I've attached a worksheet showing the source data, the code I have follows:

#If VBA7 Then
    Declare PtrSafe Function SetCurrentDirectoryA Lib _
    "kernel32" (ByVal lpPathName As String) As Long
    Declare Function SetCurrentDirectoryA Lib _
    "kernel32" (ByVal lpPathName As String) As Long
#End If

Sub ChDirNet(szPath As String)
    SetCurrentDirectoryA szPath
End Sub

Sub Basic_Example_2()
    Dim MyPath As String
    Dim SourceRcount As Long, Fnum As Long
    Dim mybook As Workbook, BaseWks As Worksheet
    Dim sourceRange As Range, destrange As Range
    Dim rnum As Long, CalcMode As Long
    Dim SaveDriveDir As String
    Dim FName As Variant

    'Change ScreenUpdating, Calculation and EnableEvents
    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    SaveDriveDir = CurDir
    ChDirNet "C:UsersPaulDesktopABC PilotData"

    FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xl*), *.xl*", _
    If IsArray(FName) Then

        'Add a new workbook with one sheet
        Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
        rnum = 1

        'Loop through all files in the array(myFiles)
        For Fnum = LBound(FName) To UBound(FName)
            Set mybook = Nothing
            On Error Resume Next
            Set mybook = Workbooks.Open(FName(Fnum))
            On Error GoTo 0

            If Not mybook Is Nothing Then

                On Error Resume Next
                With mybook.Worksheets(2)
                    Set sourceRange = .Range("C4:F20")
                End With

                If Err.Number > 0 Then
                    Set sourceRange = Nothing
                    'if SourceRange use all columns then skip this file
                    If sourceRange.Columns.Count >= BaseWks.Columns.Count Then
                        Set sourceRange = Nothing
                    End If
                End If
                On Error GoTo 0

                If Not sourceRange Is Nothing Then

                    SourceRcount = sourceRange.Rows.Count

                    If rnum + SourceRcount >= BaseWks.Rows.Count Then
                        MsgBox "Sorry there are not enough rows in the sheet"
                        mybook.Close savechanges:=False
                        GoTo ExitTheSub

                        'Copy the file name in column A
                        With sourceRange
                            BaseWks.Cells(rnum, "A"). _
                                    Resize(.Rows.Count).Value = FName(Fnum)
                        End With

                        'Set the destrange
                        Set destrange = BaseWks.Range("B" & rnum)

                        'we copy the values from the sourceRange to the destrange
                        With destrange
                                .PasteSpecial xlPasteValues
                                .PasteSpecial xlPasteFormats
                                Application.CutCopyMode = False
                                Transpose = True
                        End With
Any help will be gratefully received!


I'm attempting to do two things in an Excel workbook, which I will believe require the use of at least one, if not two macros, but I'm clueless as to how to go about this.

The first step is creating some sort of macro to count cells in one column. The count would always begin at the same cell (A17) of a sheet that will always be titled 'Segments'. The count should begin with a stored variable of 1, and add 1 for every subsequent cell in the column it checks that does not include the words 'Total Revenues'. When it reaches these words, the count should not add 1, and cease counting, saving the variable.

The second step is using the number that the counting macro reached to output the content of the rows 17 to (17+n) into a separate sheet in the workbook. Similarly, I would like to be able to do this not just for column A, but for any number of columns. For example, rows 17 to 17+n for columns A, B, C and D will be copied to another sheet, becoming rows 7 to 7+n, for the same columns.

Overall, the function will:

a) Determine how many rows in Column A contain relevant data
b) Export the data from several columns on the sheet to another parallel sheet, with the correct number of rows

Strictly speaking, this operation could be done without the first step, if I was aware of how to do the second step, but have the number 'n', calculated by the macro, be replaced with a manually input number in a 'variable' cell.

Either way, I'm clueless as to how to do this. Can anyone help? It would be much appreciated.

Thanks in advance!

Hi everyone,
Recently I built a spreadsheet for another department for the collection of data. This is a stop gap measure while the online module is built. This department then sends this worksheet to their sub departments to complete. We also built a simple Access database to collate the data and create simple reports once returned.

To keep the data integrity levels high I used VB code and validation rules (I found these in forums like this one). These disable right clicks, cut and paste functions and disallows any blank cells. For repeated data I put in drop down menus.

It worked perfectly, and initially they were pretty wrapped. But now the sub departments have complained that they can’t cut and paste large amounts of data into item description columns and it’s too hard to type it in one cell at a time...If I had my way I’d tell the sub department to suck it up, but in the interests of inter departmental relations I obviously can’t.

So the department wants the macro to allow the pasting of data, but only in one single column and only in a selected cell range of the worksheet - in this case column ‘A’ cell range ‘20:325’). The problem is I don’t know if this is possible and really require your advice on the issue.

I have included the macro I currently use below.

If anyone can help me I would really appreciate it.


Private Sub Workbook_Activate()
Application.CutCopyMode = False
Application.OnKey "^c", ""
Application.CellDragAndDrop = False
End Sub

Private Sub Workbook_Deactivate()
Application.CellDragAndDrop = True
Application.OnKey "^c"
Application.CutCopyMode = False
End Sub

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
Application.CutCopyMode = False
Application.OnKey "^c", ""
Application.CellDragAndDrop = False
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
Application.CellDragAndDrop = True
Application.OnKey "^c"
Application.CutCopyMode = False
End Sub

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Cancel = True
MsgBox "Right click menu deactivated." & vbCrLf & _
"Cannot copy or ''drag & drop''.", 16, "For this workbook:"
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Application.CutCopyMode = False
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Application.OnKey "^c", ""
Application.CellDragAndDrop = False
Application.CutCopyMode = False
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Application.CutCopyMode = False
End Sub

Hi all,

I have to combine / add up 2 regions of data.
To do so I have made a bit of code that determines the "old value" in a cell, then copies the 2nd value in the activecell, and as last, adds up the previous value.

This works if I use a fixed number for the column_index. However, this column_number changes for each column.
Therefore I want to use the value of "j" as column_index.
Simply adding this to the code doesn't seem to work.
This is the code as I thought would do the trick:

    Dim i As Long
    Dim j As Integer
    For j = 1 To NumberOfColumns    		‘NumberOfColumns is determined in 1st part
    For i = 1 To NumberOfRows		‘NumberOfRows is determined in 1st part

        Dim OldValue as long    
        OldValue = ActiveCell.Value
        ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1, C10:C17, j + 1, FALSE)"  

        Dim NewValue As Long
        NewValue = ActiveCell.Value

        ActiveCell = OldValue + NewValue     'This replaces the formula with a value
        ActiveCell.Offset(1, 0).Range("A1").Select
    Next i
               ActiveCell.Offset(-NumberOfRows, 1).Range("A1").Select  
    Next j
I've tried every trick I know to get the column_index working: adding a ", declaring a new variable, (, [, etc etc.
None of these work.

What can I do to make it work ??

Thanks in advance

I am running the followig line of code to calculate the mode(and a similar
line to calculate the average) of a series of data. Just some background, I
have the sheet set up with task in columns, with sub columns MTWRF for the
days of the week. I have rooms set up in the rows. Then where a row and
column intersect an x is placed if the task is completed in that room on the
corresponding day.

The following line of code seems to correctly take the sum of the number of
times per week (based on the number of x's in the row) a task is done for
each respective room and then find the mode for all the rooms.

I have atleast three quesions about that:

1) Using this code I can only have it find the mode of 12 rows at a time, I
need to be able to take the mode of anywhere from 5 to a little over 100
rows. Is that possible?

2) Right now it is set up so a message box appears and displays "Mode for
Trash...." I tried to instead set up to display the mode in a cell on the
spread sheet, but when I used the code
Worksheets("Sheet1").Cells(1,1).Value=lmode, no value appeared in cell A1.
Is there something Im doing wrong.

3)Finally, In some of the rooms a task is done only once a week, once a
month, twice a week or 7 times a week. When that is the case in the cell
that corresponds to Monday for the respective task and room one of those
special cases is entered in (ie "Once a Month", "7 days a Week", "Once a
Week") Also each of these cases results in a set sum for the task in a room.
(ie once a week=1 for the sum, once a mont=.25, 7 days a week =7.) I
thought about using if statements to take that into consideration but I don't
know how that would work using the Application function that I am, If you
have any ideas then let me know.


Sub CalcModeforTrash()
Dim lMode As Long
Dim v(1 To 13) As Long
i = 1
For Each cell In Range("G2:G14")
v(i) = Application.CountA(cell.Resize(1, 5))
i = i + 1
lMode = Application.Mode(v)
MsgBox "Mode for Trash: " & lMode
End Sub


I have an Excel Spreadsheet that contains multiple sheets. 5 of the
sheets are for importing text files, then other sheets used as reports,
have functions that refer to these sheets that are strictly for the
raw, imported data. I import the data using VBA. I have a range name
for each of the first 5 or 6 columns on each of these sheets, and the
data is supposed to fall within these ranges on import. I import the
data beginning on row 2 of each sheet, as I have column headers on row
1, which I use to identify the data. What I am finding is that for 4
out of the 5 sheets, the data comes in exactly as it is supposed to.
For the 1 sheet however, the named ranges get shifted over to the
right, as well as the column headers, and the data falls to the left of
them. I have looked at the VBA for each of these text imports, and for
the life of me I see no difference in them, other than the name of the
sheets. Does anyone have any idea why this might happen? I'm using
Excel 2002. If no one has any ideas, I may have to incorporate the
deletion, then renaming of the named ranges, as part of the VBA.

It just doesn't make sense to me that 4 out of 5 of the sheets are
unaffected by this behavior.


Carroll Rinehart

I was given some guidance the other day with the sumproduct function. The below formula says tell me how many time 4 and 10 are in the same row in the data range i have specified.

Is there a way either through conditional formatting or another method were i can highlight the row if the formula applies which will make it easier to look over the data if there is a large amount of data? I have provided a screen shot to try and simulate what i am trying to acheive. (i have manually highlighted the counted rows)


I have this report I have been working on for a very long time and have made many changes over the past months to this report. It seems everytime I make changes I create more problems for myself. I am trying to eliminate some problems with improving formulas in the excel workbook. I am using 2010 and have never used the vlookup function. I will try to explain as clear as possible what I am trying to do.

I have data in a pivot table called 'PivotTable2' has data in range A15:E28 (Column A has a month in it)
I have a table in range G15:J28
I have a control pivot table on a separate sheet called 'PivotTable4' has data in range A9:E22 (column A had a month in it that matches that of the PivotTable2)

I have been using this formula '=IF(ISBLANK(B15),"",B15/Control!B9)'
In this formula the Control part is the name of the sheet of the control pivot table.

I need to take the above formula and convert it to using vlookup and have no clue how to go about doing this.

Hi there

I have multiple blocks of data in 1 spreadsheet which I manually assigned range name for each block of data.

In another spreadsheet, when the range name is selected (which I use combo box) i need to be able to pull out the whole row if the first column of that row has the text 'Approved'.

The excel is attached.

Only functions. No code or filter function..



I am trying to re-create a "Top 10" list of clients by revenue, like I would use in a pivot table, but using a dynamic formula. I know this sounds a bit odd when a pivot table easily builds this for me but unfortuantley the data I use changes frequently and this isnt the right option.

I have a range of data which has client names which may appear more than once, a division name which is assaigned to each client, a territory code and a revenue amount for each client/divison/territory row. ie

Client Division Territory Revenue
J Smith Marine Japan 50,000
J Smith Energy Europe 20,000
T Jones Marine USA 150,000
T Jones Energy Japan 40,000
B Davis Mining Europe 50,000
J Smith Mining USA 10,000

and the list goes on for thousands of rows......

What I am after is a formula that will give me the top 10 clients by division for each territory, summing the revenue.

I currently do this by using a pivot table linked to pivot chart, the problem I have is that if the data comes in for say Japan and the specific division doenst have any clients or revenue, then the pivot table just picks another division. So I want to take it out of a pivot table and put the results into a grid showing a table for each division their respective top 10 clients by territory.

I have tried using the large function and sumproduct but my knowledge is letting me down.

I am unable to attached a spreadsheet as my company has blocked this feature...

Any ideas would be greatly appreciated.



I have two issues related to the attached excel file:

1. I have designed a spreadsheet for calculating the costs associated with my business. I am trying to make the spreadsheet easier for others to use by having dependent data validation lists. If, for example I select "Offshore Plate" from the dropdown in column C, I want the data validation list in column D to be restricted to the range "tubeg" from sheet "Materials". I want to take this further using an OR function to allow the selection of any of the following options in column C to select "tubeg" as the data validation range for Column D - "Offshore Plate", "Shipbuilding Plate", "Structural Plate", "Pressure Vessel Plate", "High Tensile Plate", "Low Temperature Plate", "SAW Tubular", "Seamless Tubular", "ERW Tubular". If the value in column C is "Structural Section" or "Structural Hollow Section", I want column D to refer to the range "sectiont"

2. I want the data validation range in column E (Grade) to be dependant on the value in column C (Type). The values in column C refer to ranges in the worksheet "Validations". I think there may be too many categories to use an IF function so any suggestions would be more than welcome.

I know that this is a bit of a long winded query but your help would be invaluable.

I have spent most of the day (lots of hours) playing with this. Using examples from Ron de Bruin's site I was able to prety much do what I wanted. I have several areas of data on a page and each area needs to be sent to a different email address. (html format is fine)
I was able to copy a VBA macro from Ron's site and assign it to a button to email a range to the person. Multiple versions of the macro with different email addresses serves sending each to a different person.
The problem I am having is with the auto send function. I can display it and then send manually but can't get the macro to send the email by itself. This is the code that I am using. Changing Send to Display, is supposed to be the difference for auto sending)

A sample file from his site sends automatically as it should but the code is password protected so I can't see what was different for that macro.

Below is the code that I am using.

' You need to use this module with the RangetoHTML subroutine.
' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook
2007, and Outlook 2010.
    Dim rng As Range
    Dim OutApp As Object
    Dim OutMail As Object

    Set rng = Nothing
    On Error Resume Next
    ' Only send the visible cells in the selection.
    Set rng = Sheets("Schedule_Stats").Range("A32:E39").SpecialCells(xlCellTypeVisible)
    ' You can also use a range with the following statement.
    ' Set rng = Sheets("Schedule_Stats").Range("A31:E39").SpecialCells(xlCellTypeVisible)
    On Error GoTo 0

    If rng Is Nothing Then
        MsgBox "The selection is not a range or the sheet is protected. " & _
               vbNewLine & "Please correct and try again.", vbOKOnly
        Exit Sub
    End If

    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
        .To = "someone@optonline.net"
        .CC = ""
        .BCC = ""
        .Subject = "Schedule Stats"
        .HTMLBody = RangetoHTML(rng)
        ' In place of the following statement, you can use ".Display" to
        ' display the e-mail message.
    End With
    On Error GoTo 0

    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Hey everyone, I am just playing around with excel and trying to build a spreadsheet for a game I use. The game involves upgrading buildings at a cost. The spreadsheet I am making is to work out the costs from the buildings current state to the upgraded state of many levels.

Basically I currently use the vlookup function to bring up the stats of the current level but then in the next area I want to put in the desired level which will cost so much silver but i want it to calculate all of the silver used in the levels between the current and the goal level.

So if level 18 is the current level and I want to build it to level 20, I want it to find the cost to upgrade to 19 plus the cost to upgrade it to level 20. Say the upgrade to level 19 is 200 and then to 20 is 300, i want it to look up the values and display it as 500.

Is this possible lol and if it is how do i achieve it. Also the tables of data which i want it to gather the info from is on a seperate sheet within the same workbook.

Hi, I am working on a legal document and our client has requested that we use
this particular worksheet for a particular data.

I'm trying to copy/paste about 3-4 paragraphs of data into one cell, and I
have minimized the font, stretched the row and column, however, I still get
text not appearing in the cell. The amoutn of characters in this particular
cell are possibly around 1,500 range.. I know this is so counterproductive,
however, I do have to stick with Excel program.


I'm not proficient with functions or anything, so if your response is with
functions, etc. can you please be specific :-).. Thanks much.

I trying to use the following worksheet to analysis required and assigned
resources across a wide range of projects.

Project Source Skill 3Q05
AWG 10G Assigned PL 0.00
AWG 10G Required PL 0.00
AWG 10G Gap PL 0.00
AWG 6G Assigned IDFM 0.00
AWG 6G Required IDFM 0.25
AWG 6G Gap IDFM 0.25
AWG 6G Assigned IDFT 0.25
AWG 6G Required IDFT 0.25
AWG 6G Gap IDFT 0.00
GigaDig 12G Assigned IDFM 0.00
GigaDig 12G Required IDFM 0.25
GigaDig 12G Gap IDFM 0.25
GigaDig 12G Assigned IDFT 0.20
GigaDig 12G Required IDFT 0.25
GigaDig 12G Gap IDFT 0.05
GigaDig 12G Assigned SLM-LSP 0.00
GigaDig 12G Required SLM-LSP 0.20
GigaDig 12G Gap SLM-LSP 0.20

Subtotal Assigned
Subtotal Required
Subtotal Gap

Have read several discussions regarding use
SUM(IF(FREQUENCY(NumRange,NumRange)>0,1)) &
SUBTOTAL(3,OFFSET(Range,ROW(Range)-MIN(ROW(Range)),,1)) functions however
none seem to do what I want done.

Goal is as I filter on a Project or Skill Type to have subtotals for
Assigned and Required displayed at the bottom for the filtered data.


I'm new to this forum, and to forums in general,
So please excuse me for any misunderstanding of the forum rules..

I'm using Matlab to export to Excel a data range.

Up until now, the data range had a defined number of columns, and using the OFFSET function, and the Named Range option I got what I needed.
I created a Workbook Template where I defined every series seperately in the 'Select Data...' option for charts and defined them as different series.
from Matlab I would create a renamed copy of the template, poured all the data into it, and that worked beautifully.

Now I want to export a data range with a variable number of columns (2-16).
What I did is to create a Named Range with Offset containing all the data range,
and in the chart 'Select Data...' option, I inserted the Named Range in the "Chart Data Range" box.
The problem is that this box doesn't keep the Named Range but turn in into the absolut range of the function's answer. And, so, I have nothing..

I guess I'm not the first one to ask this question... But I'm an internet moron, and I couldn't find what I want..

I don't know anything about VBS, but I'm quick to learn if it's needed.
If someone could, please, show me how the code to this requirement should look.
Or, can I create it as a macro code, I can manually activate after all the data is alreay saved in to workbook?



Can I get either the Auto or fixed features in format axis to select the value of a cell content as both the start and end points for the x-axis.

These will be date values separated by 1 (i.e one day)

So I know I can can generate hourly tags by making the the major units =1/24.

My problem is if I use the fixed function on the first graph to set the start and end points on the chart (which eliminates the annoying gap Excel insists on putting in when I chose Auto) then it does just that and if I then copy data to a copy of the chart from a new range, the resulting new chart retains the old x-axis range.

As I say if I just start on Auto Excel insists on extending the X-axis beyond the data range at both ends..

(Just out of interest why does Excel do the above, it has always annoyed me.

I have often wanted only to break the x-axis in a controlled number of tags between the highest and lowest X values or as in this case use a time axis that fills the chart window.
But Excel insists on giving me distance between the lowest x value and the y axis and space between the end of the data and edge of the charted area. . Is there some kind of techncial reason (e.g. possible rounding errors) for this?)

hi there,
i have a table of data consisting on monthly info. this data comes from multiple sources which include an IF formula, when it is false it puts on a blank space " ".
im using the offset function to name the dynamic ranges:

=offset(page!$A$2,1,0,COUNTA(page!$A:$A)-1,1) for labels
=offset(labels,0,1) for values

then i substitute the series with

but it doesn't seem to work if the cell is blank but it is actually a formula, it takes a larger range and the chart doesn't look nice...