Free Microsoft Excel 2013 Quick Reference

If function using range of data Results

Can we use the Indirect functions to decide the range of data in Graphs Excel 2007

I would be having dynamic data, dynamic as in the Rows would change always if anything is entered in the Main Data sheet.

Now based on this data in the Result sheet the graph should get formed..

If I have 4 rows of data then there would only appear 4 slices in a Pie Chart, however if the data increases to 5 rows then it should dynamically take 5 rows of data and come up with 5 slices or more..

[ = INDIRECT("'RESULT'!$B$2:$C$"& COUNT(C:C)+1) ]

I want to use the above formula in which the COUNT(C:C) would decide the number of rows present as it contains mathematical data and can be counted easily.

The range of the data should be dynamic for the Pie chart...

Thanks in anticipation...

Can we use the Indirect functions to decide the range of data in Graphs Excel 2007

I would be having dynamic data, dynamic as in the Rows would change always if anything is entered in the Main Data sheet.

Now based on this data in the Result sheet the graph should get formed..

If I have 4 rows of data then there would only appear 4 slices in a Pie Chart, however if the data increases to 5 rows then it should dynamically take 5 rows of data and come up with 5 slices or more..

[= INDIRECT("'RESULT'!$B$2:$C$"& COUNT(C:C)+1) ]

I want to use the above formula in which the COUNT(C:C) would decide the number of rows present as it contains mathematical data and can be counted easily.

The range of the data should be dynamic for the Pie chart...

I want this to be solved using MS Excel and not VBA preferably..

Thanks in anticipation...

Please anyone someone ........help me or offer a solution...

I'm reposting this to the charting forum to see if anyone knows the
answer to my problem.
The chart can be found here:

http://www.dciu.org/cspd/Generic%20P...0Template2.xls

Hi everyone,
You've all been able to help me a ton in the past and I'm hoping you
can do it now as well.
I have a chart that utilizes dynamic name ranges (OFFSET formulas) and
a dynamic graph that uses those ranges to automatically update the
graph using SERIES. This used to work great until I "tweaked" it.
Here's the problem: I added a new formula to the bottom of a column
with data in it that will add data to this column if another column
gets data added first.
e=2Eg.
Currently Column D has the #28 in it. If the user types 29 in the cell
below it, using an IFstatement, Column F applies the formula in it.
Here is the formula in cells F38:F1000-
=3DIF(D39=3D"","",$F$8+($A$12*D39))

Now that I have formulas in the "empty" cells of column F, the graph
thinks there is data there and puts placeholder 0s there.

How can I tell my graph to ignore the formulas and only add data if the

range includes numbers only? Thank you, cabybake

Reply

2. ScottO
Jan 11, 10:18 pm show options

Newsgroups: microsoft.public.excel
From: "ScottO" <scott_orchard_REMOVET...@hotmail.comTHISTOO> - Find
messages by this author
Date: Thu, 12 Jan 2006 14:18:37 +1100
Local: Wed, Jan 11 2006 10:18 pm
Subject: Re: Dynamic Range with unused formula messing up x axis on
dynamic graph
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse

If you replace the "" for the True result with NA(), then the chart
will show the unused rows as blank rather than zero. But this will
still extend the axis values beyond the 'used' range.
If you want to restrict the axis length to the 'used' range, then
you'll need to modify the Offset formula. One way would be to refer
to column F and use something like CountIf <>"".
hth
ScottO

3. cabybake
Jan 12, 2:34 pm show options

Newsgroups: microsoft.public.excel
From: "cabybake" <cabyb...@yahoo.com> - Find messages by this author
Date: 12 Jan 2006 11:34:09 -0800
Local: Thurs, Jan 12 2006 2:34 pm
Subject: Re: Dynamic Range with unused formula messing up x axis on
dynamic graph
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse

I tried a few things with the COUNTIF and SUMIF functions, but I can't
seem to get it to work. Could you be specific in how it would work in
an OFFSET formula? Thanks, caby

Reply

4. Peo Sjoblom
Jan 12, 4:22 pm show options

Newsgroups: microsoft.public.excel
From: "Peo Sjoblom" <terr...@mvps.org> - Find messages by this author
Date: Thu, 12 Jan 2006 13:22:47 -0800
Local: Thurs, Jan 12 2006 4:22 pm
Subject: Re: Dynamic Range with unused formula messing up x axis on
dynamic graph
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse

Assume the offset looks something like

=3DOFFSET($A$1,,,COUNTA(A:A),)

instead of COUNTA you can use

=3DOFFSET($A$1,,,SUMPRODUCT(--(A1:A65535<>"")),)

--

Regards,

Peo Sjoblom

"cabybake" <cabyb...@yahoo.com> wrote in message

news:1137094449.323619.137230@z14g2000cwz.googlegroups.com...

- Hide quoted text -
- Show quoted text -

> I tried a few things with the COUNTIF and SUMIF functions, but I can't
> seem to get it to work. Could you be specific in how it would work in
> an OFFSET formula? Thanks, caby

Reply

5. cabybake
Jan 17, 10:43 am show options

Newsgroups: microsoft.public.excel
From: "cabybake" <cabyb...@yahoo.com> - Find messages by this author
Date: 17 Jan 2006 07:43:20 -0800
Local: Tues, Jan 17 2006 10:43 am
Subject: Re: Dynamic Range with unused formula messing up x axis on
dynamic graph
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse

This didn't work yet. Here is my OFFSET formula as it works with the
graph (but not with the hidden formulas)
"F" refers to the line and Dynamic Range called "Aimline"

=3DOFFSET('Generic Template'!$F$7,1,0,COUNTA('Generic
Template'!$F:$F)-1,1)

I tried what you suggested by putting in this:

=3DOFFSET('Generic Template'!$F$7,1,0,SUMPRODUCT(--'Generic
Template'!$F8:$F1000<>""))

Can you see what I did wrong? Using this formula, the Aimline did not
show up on the graph at all and the Dates associated with this also
did not show up. Only one point showed and it was in the middle of the
graph. Thank you, caby

Reply

6. cabybake
Jan 25, 8:02 am show options

Newsgroups: microsoft.public.excel
From: "cabybake" <cabyb...@yahoo.com> - Find messages by this author
Date: 25 Jan 2006 05:02:20 -0800
Local: Wed, Jan 25 2006 8:02 am
Subject: Re: Dynamic Range with unused formula messing up x axis on
dynamic graph
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse

bump

Reply

7. Debra Dalgleish
Jan 25, 1:40 pm show options

Newsgroups: microsoft.public.excel
From: Debra Dalgleish <d...@contexturesXSPAM.com> - Find messages by
this author
Date: Wed, 25 Jan 2006 13:40:45 -0500
Local: Wed, Jan 25 2006 1:40 pm
Subject: Re: Dynamic Range with unused formula messing up x axis on
dynamic graph
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse

You may get a response if you post your question in the Charting
newsgroup, and include some detail on the formula, and the chart.

cabybake wrote:
> bump

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Reply

=AB Start of topic =AB Older Messages 1 - 7 of 7 Newer =BB End of
topic =BB

Hi

I need to create a scatter plot of three ranges of data and then have three
lines for three different ranges. Is this possible?

I need to depict three classes of item and straight lines that separate
them. The items are plotted on the graph using a scatterplot as three
separate ranges. The lines (which represent mathematical functions) are to
be plotted as line graphs.

This is to depict a neural network classification problem.

I have tried editing the custom types by taking a copy of one that is
similar and then trying to modify it. Unfortulately only one of the ranges
has X and Y data ranges (which are needed for the plot) and the others have
just one axis (which is fine for the lines).

If anyone knows what I am trying to say, and has any ideas then please let
me know - I would be grateful

Cheers in advance

Tim

I am using the LINEST function to extract the coefficients from a second order polynomial graph using the following syntax

=LINEST(F3:F10;E3:E10^{1,2})

This formula is in an array of cells and the results it produces are good enough for what I have in mind.

However, the range of cells the trendline is constructed from is taken from another sheet using the index function.

=IF(B3<>"";(INDEX(DateWT!$5:$971;MATCH(B3;DateWT!$A$5:$A$971;0);25));#N/A)

Again this works OK but if the data isn't there it is set up to put a #N/A in the cell. This works on the graph i.e. it doesnt plot a value and doesnt fall over when there isn't any numerical data in the cell but the LINEST function returns a #value comment - I presume because there isnt a numerical value in the cell. Is there anyway I can make sure the LINEST function only uses the numerical values and not the comments or is there another way of putting a null value into a cell that wont be recognised by the chart OR the LINEST function??

Any help/suggestions would be greatly appreciated!!!

Thanks

Sy

I am trying to enter a formula(s) to show when certain conditions are met, looking at three data points. To give some background info on what I am looking to do, I have three boilers that I want to operate in a certain manner to generate the lowest cost steam. To meet the required steam demand, which is constantly changing, the steam load on each boiler can be adjusted. Each boiler has a minimum and maximum steam load, which are as follows:

#6BLR
MIN=100KPPH
MAX=240KPPH

#7BLR
MIN=180KPPH
MAX=300KPPH

#8BLR
MIN=280KPPH
MAX=240KPPH

The steam demand typically ranges from 600-900KPPH. Based on fuel costs, I want to maximize #6 first, then #8, and then #7. I want to enter a formula that will tell if conditions are met, based on the steam demand. For instance, I want to make sure #6BLR is greater than 215KPPH, as long as the steam demand is greater than 675KPPH. This scenario would result in #7 and #8 being operated at their minimum (total of 460KPPH) with #6 making the up the rest of the demand. If the steam demand is greater than 730KPPH, I want to show that #6 is greater than 215KPPH, and #8 is greater than 335KPPH, before increasing the load on #7 above its minimum of 180KPPH. I want to show an error message if they do not fit this scenario.

I have tried several nested IF functions, the use of IF and AND, etc., but I can't get it to work how I want it to. Any help you can offer will be greatly appreciated. Thanks in advance...

I have tried to look for a solution to this but as of yet nothing seems to work.

I've attached an example of what I am trying to get excel to do.

In sheet2 I have a list which links to sheet1. I need some way of removing the "blank" cells in each of the column ranges in sheet2 so that for column A, B, C I only have the relevant data rather than blanks. However the cells are not blank as they all contain a formula. As such using the ISBLANK formula doesn't help. I can create an IF function to return TRUE or FALSE but then I am not sure how to use that to remove blanks.

The reason for doing so is so that I can then create each column as a dynamic range for data validation lists in another sheet. So that in another sheet I can select A, B, or C and then have a drop down menu that has only those relevant ranges rather than including all the blanks.

Hello All,

First time posting in an Excel forum, and would appreciate some good advice to achieve my aim with my spreadsheet.

Sheet 1 contains 7 columns, all with raw data, some columns require date dd/mm/yyyy, some have names, some have numerical quantities with no decimals, some have numerical quantities with 3 decimals. This data is all raw data to be entered by me.

Sheet 2 contains a range of search criteria to extract data from the database in sheet 1. I would like sheet 2 to offer a range of search functions to show data from sheet one. So in its simplest terms, sheet 2, I would select 2 date ranges, and it would then display the data from sheet 1 where these data ranges are met. In its most complex form, sheet 2 may be used to select data between 2 ranges, for a particular customer, between certain price ranges.

I have attached the spreadsheet.

Please advise or ask any more questions if possible.

I have been trying to come up with a formula to lookup a value in one worksheet to put into another worksheet based on a date range from a date provided in the target worksheet.

I have two streams of data on different worksheets. In worksheet 1, I have in columns B through D: item description, Period, Cost. In Worksheet 2, I have in columns B through E: Item Description, From Date, To Date, Cost.

I am trying to find the cost, to put in Column D in Worksheet 1, found in column E in worksheet 2 for each "item description" (in column B) that matches to the "item description" in Worksheet 2 (column B), where the Period (in column C, Worksheet 1) is on or after the From Date (column C, Worksheet 2) AND on or before the To Date (column D, Worksheet 2).

I have attached an example worksheet for reference. I am confused if I should use VLOOKUP or SUMPRODUCT functions (or some other function for that matter).

Thanks for your help.

Larry

(UPDATED 3/15/12 17:11, WITH CORRECTED SPREADSHEET ATTACHED)

I've been testing some use of Percentile, and am not sure how to specify the correct range of values that I want to analyze. So I've got it set here for the first 999 rows, but what I want to do is to analyze *all* the data in the column. As shown below, currently set to B2:B999 - so how do I set the range ending value to be the last non-blank value in the whole worksheet?

=PERCENTILE(IF(G2:G999=K1,B2:B999, "" ),0.95)

This example shows that I'm also doing kind of a filter or conditional statement - the IF(G2:G999=K1 portion. Am I doing this right? So that if the value in G matches what's in K1, then that entry will be considered in the percentile calculation.

I've searched several postings here on this topic first. If I missed one, point me to it so you don't have to repeat things. Using Excel 2000 SP3, but this is pretty generic so the version may not matter. If I need to attach a segment of the file, I can do so. Thanks!

I am trying to write a UDF to give a result based on the the =TREND() function in Excel 2003. This should take the defined range and given the fixed start point, should calculate the trend using the data to the minimum point only. Below is the code I have so far.

Function TRENDTOMIN(Index_Values As Range, Index_Dates As Range, To_Date As Double)
    
    ' Function TRENDTOMIN is written to utilise the trend function to extrapolate from the _
    start data point to the minimum of the data set.
    
    
    Dim Index_Values_Min_Area As Range
    Dim Index_Dates_Min_Area As Range
    
    Dim Index_Values_To_Min() As Double
    Dim Index_Dates_To_Min() As Double
    
    Dim Index_Min As Double
    Dim Index_Min_Cell As Double
    
    Dim Index_Values_Start_Row As Long
    Dim Index_Values_End_Row As Integer
    Dim Index_Values_Column As Integer
    
    Dim Index_Dates_Start_Row As Long
    Dim Index_Dates_End_Row As Integer
    Dim Index_Dates_Column As Integer
    
    Dim i As Integer
    
    
    ' Determines Minimum of Data Range
    Index_Min = Application.WorksheetFunction.Min(Index_Values)
    Index_Min_Cell = Application.WorksheetFunction.Match(Index_Min, Index_Values, 0)
    
    ' Determines Rows and Columns of Values to Trend
    Index_Values_Start_Row = Index_Values.Row
    Index_Values_End_Row = Index_Min_Cell
    Index_Values_Column = Index_Values.Column
    
    ' Determines Rows and Columns of Dates to Trend
    Index_Dates_Start_Row = Index_Dates.Row
    Index_Dates_End_Row = Index_Min_Cell
    Index_Dates_Column = Index_Dates.Column
    
    ' Redims to reset range for trend
    ReDim Index_Values_To_Min(Index_Values_Start_Row To Index_Min_Cell) As Double
    ReDim Index_Dates_To_Min(Index_Dates_Start_Row To Index_Min_Cell) As Double
    
    ' Moves through cells in new range to apply to arrays
    For i = Index_Values_Start_Row To Index_Values_End_Row
        Index_Values_To_Min(i) = Cells(i, Index_Values_Column)
        Index_Dates_To_Min(i) = Cells(i, Index_Dates_Column)
    Next i
    
    ' Returns trend to the new date point
    TRENDTOMIN = Application.WorksheetFunction.Trend(Index_Values_To_Min(), Index_Dates_To_Min(), To_Date)
    
End Function

Using the data (see below) and a to date of 29/01/2010 with this code I get the answer 3061.506, however if I manually select the range to the minimum point and use the =TREND() formula, I get the result 3087.098.

22/01/2010	2836.8
15/01/2010	2940.25
08/01/2010	3017.85
01/01/2010	2964.96
25/12/2009	2957.03
18/12/2009	2871.22
11/12/2009	2862.32
04/12/2009	2910.33
27/11/2009	2831.14
20/11/2009	2833.06
13/11/2009	2883.04
06/11/2009	2794.25
30/10/2009	2743.5
23/10/2009	2886.08
16/10/2009	2893.53
09/10/2009	2882.18
02/10/2009	2760.6
25/09/2009	2831.95
18/09/2009	2887.24
11/09/2009	2831.37
04/09/2009	2743.34
28/08/2009	2803.65
21/08/2009	2745.62
14/08/2009	2669.41
07/08/2009	2706.22
31/07/2009	2638.13
24/07/2009	2582.76
17/07/2009	2469.2
10/07/2009	2281.47
03/07/2009	2376.48
26/06/2009	2389.91
19/06/2009	2434.77
12/06/2009	2509.22
05/06/2009	2503.17
29/05/2009	2451.24
22/05/2009	2433.52
15/05/2009	2364.1
08/05/2009	2462.39
01/05/2009	2375.34
24/04/2009	2319.89
17/04/2009	2341.15
10/04/2009	2247.89
03/04/2009	2198.75
27/03/2009	2118.76
20/03/2009	2050.96
13/03/2009	1969.52
06/03/2009	1817.24
27/02/2009	1976.23
20/02/2009	2011.68
13/02/2009	2228.29
06/02/2009	2343.66
30/01/2009	2236.98
23/01/2009	2147.87
16/01/2009	2281.45
09/01/2009	2486.59
02/01/2009	2536.47
26/12/2008	2377.42
19/12/2008	2444.14
12/12/2008	2418.91
05/12/2008	2252.09
28/11/2008	2430.31
21/11/2008	2165.91
14/11/2008	2456.43
07/11/2008	2602.54
31/10/2008	2591.76
24/10/2008	2333.28
17/10/2008	2532.17
10/10/2008	2421.87
03/10/2008	3113.82
26/09/2008	3156.46
19/09/2008	3253.52
12/09/2008	3278.02
05/09/2008	3185.83
29/08/2008	3365.63
22/08/2008	3312.41
15/08/2008	3367.62
08/08/2008	3408.48
01/08/2008	3316.61
25/07/2008	3351.13
18/07/2008	3321.53
11/07/2008	3197.78
04/07/2008	3275.2
27/06/2008	3340.27
20/06/2008	3426.58
13/06/2008	3562.67
06/06/2008	3596.7
30/05/2008	3777.85
23/05/2008	3725.82
16/05/2008	3862.91
09/05/2008	3801.59
02/05/2008	3877.5
25/04/2008	3794.39
18/04/2008	3808.59
11/04/2008	3699.99
04/04/2008	3795.2
28/03/2008	3641.05
21/03/2008	3505.9
14/03/2008	3566.59
07/03/2008	3576.68
29/02/2008	3724.5
22/02/2008	3737.11
15/02/2008	3719.28
08/02/2008	3701.17
01/02/2008	3867.47
25/01/2008	3777.06
18/01/2008	3995.17
11/01/2008	4225.31
04/01/2008	4270.53

Thanks in advance for any help.

I want to produce a spreadsheet which automatically tidies up a transferral of data from a programme called sage in to a customer service spreadsheet for my company.

I am a bit of a novice but a keen one so please be patient! I do know what I want to achieve in the long run!

The problem I have at the moment is recognising products in one column and getting it to return the value in or out of stock in another. So far I have got it to recognise one product and return the correct statement (using the "IF" function) but i need it to do it for the whole column and for a range of products.

Anyone got any ideas??

Thanks

Excel currently does not have a formula to find unique values in a range. I
have needed to do this a number of times to provide useful analysis of data
sets. There is a way to find unique values by nesting several other
functions, however it is cumbersome to use and even more difficult to edit
correctly. Since the capability exists in a round about way, it should be
easy to craft into a stand alone function.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...heet.functions

I know that there have been many posts regarding using MEDIAN Arrays (in
place for the missing "MEDIANIF" function in Excel.)

I was wondering if it can be taken a step further!

Currently, I am using a median array that only performs the MEDIAN function
if the four left characters match what I specify, however can I specify two
sets of data instead of one? Here's what I have now:

{=MEDIAN(IF((LEFT('Sheet1'!$J$2:$J$5000,4)="1234"),'Sheet1'!$A$2:$A$5000,""))}

As you can see, it is scanning the first four characters of column "J" and,
if it matches "1234" it will use the value in column "A" for the MEDIAN
function.

The problem is that I want to take the median of values that start with
"1234" and "5678" at the same time. Is this possible? I think I had already
tried a nested IF statement, but I'm thinking I did it incorrectly because I
got nothing from it.

As usual, thanks to everyone in advance!

Ronny Hamida

Hello,
I will be glad, if anyone could help me I'm writing thesis about automated garage. I have an application(InTouch) that animates inserting cars into garage. This application sends data via DDE to excel into 6x6 table. In first sheet goes vehicle license number, second its weight... What I need to do is to trigger macro that will collect data from other worksheets on position where number plate is inserted and push them in one line in last sheet. I wrote a macro that works good if I use manual change, but doesn't work when value is changed via DDE. I googled that none DDE action triggers Worksheet_Change

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Value <> "" Then
    Dim last As Long
    last = Worksheets("zoznam").Range("A65536").End(xlUp).Row
    
    Worksheets("zoznam").Cells(last + 1, 1).Value = Target.Value                             //license plate
    Worksheets("zoznam").Cells(last + 1, 2).Value = Worksheets("vaha").Cells(Target.Row,
Target.Column).Value               //weight
    Worksheets("zoznam").Cells(last + 1, 3).Value = Worksheets("vyska").Cells(Target.Row,
Target.Column).Value             //height
    Worksheets("zoznam").Cells(last + 1, 4).Value = Worksheets("prichod").Cells(Target.Row,
Target.Column).Value           //time of arrival
    Worksheets("zoznam").Cells(last + 1, 6).Value = Target.Address                                                 
                        //cell address
    
   Else
    here = FindRowByValue(Target.Address)                                                            //vehicle is taken out,
add time when left
    If here <> 0 Then           
        Worksheets("zoznam").Cells(here, 5).Value = Worksheets("odchod").Cells(Target.Row,
Target.Column).Value
    End If
   End If
End Sub

Public Function FindRowByValue(Value As Variant, Optional col As Long = 6) As Long         //function to look up where should
be departure time added
  Dim i As Long
  For i = 1 To Worksheets("zoznam").Range("A65536").End(xlUp).Row
    If Worksheets("zoznam").Cells(i, col).Value = Value Then
      FindRowByValue = i
    End If
  Next i
End Function
I used Worksheet_Change, because it knows Target and I can easily collect data from other sheets with it. I read that cell change via DDE can trigger Calculate(), but then I have no info, which cell was changed.
Please I need your advice, or at least point me to right direction. Many thanks

Sorry for my english, I'm not a native speaker

Greetings folks !

I've been bashing my head against the wall for awhile on this one. Here's what I'm trying to do :

Sheet1 = Several columns of time stamps
- The user clicks a check box to determine if they want the include this column in a data analysis
- The user presses an 'Analyze' button and it copies the data to a new column in sheet2 where I can do all kinds of wonderful math against it
- Each individual column is then put together in one column on Sheet2 and sorted

The last step is where I'm stuck. The data will need to start in cell A40. Cell A39 is populated by a cell with the string "Sorted Data" in it. My sort algorithm works, but I can't get the data properly into one column starting in A40. Here's the function I'm using for one of the columns :

If Sheet1.[include1].Value = 1 Then
Sheet2.Range("A1") = Sheet1.Range("B5")
Sheet1.Range("B6", "B40").Copy
Sheet2.Range("A2", "A36").PasteSpecial Paste:=xlPasteValues
Sheet2.Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
End If

The last line is the problem. It ends up overwriting my data above A40, as if there's nothing in A39.

I've also tried the following :
Sheet2.Range("B6").Offset(Application.WorksheetFunction.Count(Range("B6").EntireColumn), 0).PasteSpecial paste :=xlPasteValues

The above works when I'm inserting the time stamps in the columns for Sheet1 through a Command button click with this :

Sub Button1_Click() Sheet1.Range("B6").Offset(Application.WorksheetFunction.Count(Range("B6").EntireColumn), 0).Value = Time
End Sub

It doesn't seem to like that I'm using Sheet2.Range instead of Activesheet.Range or just Range and gives me an user object defined error. I cannot use either of the former options since Sheet2 is going to be hidden from the person using the workbook.

Any help would be appreciated !

Thanks,

-Budd

Thanks in advance for any help.

Let me first describe my table: I have a table in Excel that is tied to data in Access. There is a button that I can press that will automatically import the data from Access based on a date and set of times inputted by the user. If the data pulled fills in 15 rows, then the table expands or shrinks to 15 rows. If it's 30, then it grows to 30. The user then can add to the data and export it back to Access.

What I am working on doing is adding an Update button. This will work similarly to the Import button, but will recognize any data that has been added to the Excel table and instead of overwriting that data, will simply update and add or remove any rows based on the new parameters.

My problem: To update and not replace current data in Excel, I am using the following code:

Sub updatetest()

Dim AccessID(), ID_Count, z As Integer
Dim Switched(), Reason(), Verbiage() As String
Dim rID, rReplace As Range

ActiveSheet.Unprotect

Set rID = Range("Import[Access ID]")

'Count the number of IDs
ID_Count = rID.Count

'ReDim to reflect how many units are the in the Access ID, Switched, Reason, and Verbiage arrays
ReDim AccessID(ID_Count), Switched(ID_Count), Reason(ID_Count), Verbiage(ID_Count)


'Assigns each value in the Access ID, Switched, Reason, and Verbiage arrays a variable
For z = 1 To ID_Count
    AccessID(z) = Range("B11").Offset(z, 0)
    Switched(z) = Range("P11").Offset(z, 0)
    Reason(z) = Range("Q11").Offset(z, 0)
    Verbiage(z) = Range("R11").Offset(z, 0)
Next

'<Insert UPDATE code here>

For z = 1 To ID_Count
    On Error Resume Next
    Set rReplace = rID.Find(What:=AccessID(z), LookAt:=(xlWhole))
    Range(rReplace.Address).Offset(0, 14).Value = Switched(z)
    Range(rReplace.Address).Offset(0, 15).Value = Reason(z)
    Range(rReplace.Address).Offset(0, 16).Value = Verbiage(z)
Next

'ActiveSheet.Paste

Range("B12").Select

ActiveSheet.Protect

End Sub
If the updated table is the exact same number of rows, it works fine. If its fewer rows, it works fine. The problem is if the table expands. What will occur is that it will only search the same number of rows that were in the table initially. So, if I have 15 rows and update it to make 30, it will only search the first 15 rows on the new table and the rest of the data I am looking to replace gets lost.

Sorry about any confusion in my explanation, but I am happy to clarify any details you may need to offer assistance.

Thanks again!

Ok, so the below is functioning in that the first connection file created does point to the correct URL however, when moving on to the next URL in the sequence the data that is actually placed on the sheet is that of the first "page" in the URL and not as the url has pointed. So, when the connection is created based on the address provided on row 1 and the macro moves on to row 2 the two connection files are of separate pages but still the sheet only displays the first... here is the real kicker, when i open the actual connection file and edit, the page refreshes from the previous to the url which is in the connection file..

i am completely at a loss her folks, trying to pull from trulia.com, the first connection would be page 1 of a query and then on through the pages...

Option Explicit 
Dim w As String 
Dim p As String 
 
 
 
Sub gotsawork() 
     '
     ' Macro2 Macro
     '
    Sheets("pageNo").Select 
    Range("A1").Select 
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select 
    Selection.Copy 
    Sheets("Sheet1").Select 
    Range("A1").Select 
    ActiveSheet.Paste 
     
     
    Do 
         
        ActiveWorkbook.Worksheets.Add 
        p = "URL;" & Sheet1.Range("B1").Value 
        w = Sheet1.Range("A1").Value 
        
     With ActiveSheet.QueryTables.Add(Connection:=p, _ 
            Destination:=Range("$A$1")) 
            .Name = w 
            .FieldNames = True 
            .RowNumbers = False 
            .FillAdjacentFormulas = False 
            .PreserveFormatting = True 
            .RefreshOnFileOpen = True 
            .BackgroundQuery = True 
            .RefreshStyle = xlInsertDeleteCells 
            .SavePassword = False 
            .SaveData = True 
            .AdjustColumnWidth = True 
            .RefreshPeriod = 0 
            .WebSelectionType = xlEntirePage 
            .WebFormatting = xlWebFormattingNone 
            .WebPreFormattedTextToColumns = True 
            .WebConsecutiveDelimitersAsOne = True 
            .WebSingleBlockTextImport = False 
            .WebDisableDateRecognition = False 
            .WebDisableRedirections = False 
            .Refresh BackgroundQuery:=False 
        End With 

        Sheets("Sheet1").Select 
        Rows("1:1").Select 
        Application.CutCopyMode = False 
        Selection.Delete Shift:=xlUp 
         
    Loop Until IsEmpty(ActiveCell) 
     
     
     
End Sub
here is the kicker.. if i use the code as below.. whamo.. it works, the two sheets are acurrately displaying the two unique lists and the connection file is good as well.. is this something to do with the string applied??? ('Dim w AsString')

Sub secondtry() 
     '
     ' Macro1 Macro
     '
     
     
     '
    With ActiveSheet.QueryTables.Add(Connection:= _ 
        "URL;http://www.trulia.com/for_sale/Franklin,TN/1_p", Destination:=Range( _ 
        "$A$1")) 
        .Name = "1_p" 
        .FieldNames = True 
        .RowNumbers = False 
        .FillAdjacentFormulas = False 
        .PreserveFormatting = False 
        .RefreshOnFileOpen = False 
        .BackgroundQuery = True 
        .RefreshStyle = xlInsertDeleteCells 
        .SavePassword = False 
        .SaveData = True 
        .AdjustColumnWidth = True 
        .RefreshPeriod = 0 
        .WebSelectionType = xlEntirePage 
        .WebFormatting = xlWebFormattingAll 
        .WebPreFormattedTextToColumns = True 
        .WebConsecutiveDelimitersAsOne = True 
        .WebSingleBlockTextImport = False 
        .WebDisableDateRecognition = False 
        .WebDisableRedirections = False 
        .Refresh BackgroundQuery:=False 
    End With 
    Sheets("Sheet2").Select 
    With ActiveSheet.QueryTables.Add(Connection:= _ 
        "URL;http://www.trulia.com/for_sale/Franklin,TN/2_p", Destination:=Range( _ 
        "$A$1")) 
        .Name = "2_p" 
        .FieldNames = True 
        .RowNumbers = False 
        .FillAdjacentFormulas = False 
        .PreserveFormatting = False 
        .RefreshOnFileOpen = False 
        .BackgroundQuery = True 
        .RefreshStyle = xlInsertDeleteCells 
        .SavePassword = False 
        .SaveData = True 
        .AdjustColumnWidth = True 
        .RefreshPeriod = 0 
        .WebSelectionType = xlEntirePage 
        .WebFormatting = xlWebFormattingAll 
        .WebPreFormattedTextToColumns = True 
        .WebConsecutiveDelimitersAsOne = True 
        .WebSingleBlockTextImport = False 
        .WebDisableDateRecognition = False 
        .WebDisableRedirections = False 
        .Refresh BackgroundQuery:=False 
    End With 
End Sub


Hello again,

This code concerns two of the WS's in a WB. The main WS ("BatteryData") and a secondary WS ("ChargingData"). A Command Button on main sheet contains the following code. The OP selects a cell and the code behind the button, stores the value of that cell in "x" and "Clears Contents" of that row. The sheet is then sorted. Up to this point, the code works fine.

Then the code selects the next sheet, using "x" as a comparison, finds a matching cell and "Clears Contents" of that row. Then it is suppose to sort that sheet but, after highlighting the correct Range, fails to sort. The sheet does sort by going to the sheet and manually (using the dropdown Sort function) sorting it.

The first Sort works fine and no errors are generated but the second sort doesn't do anything. The only difference in the two sorts is the Sheet they occur on, the Range and the Col to sort to.

Probably something very simple but it's got me baffled.....any help would be appreciated.

Private Sub
CommandButton3_Click()

' Delete Selected Battery From "BatteryList", From "ChargingData" And
' Sort Data Both Sheets
   
   On Error Resume Next
   
   Dim x, i, LastRow As Integer
   
   x = ActiveCell.Value
   
   With ActiveCell.EntireRow.ClearContents
   End With
   
   With ActiveSheet.Range("A2:H25").Select
      Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
      OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
      DataOption1:=xlSortNormal
      Range("A2").Select
   End With
   
   Sheets("ChargingData").Select
   With ActiveSheet
      LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
   End With
      
   For i = 2 To LastRow
      If x = Sheet3.Range("B" & i).Value Then
         Sheet3.Range("B" & i).EntireRow.ClearContents
      End If
   Next
        
      Sheets("ChargingData").Range("A2:G25").Select
'****Code runs fine and does not give any errors but here it fails to sort.*****

      Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
          OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
          DataOption1:=xlSortNormal
      Sheets("ChargingData").Range("D12").Select
    
    Sheets("BatteryList").Select
   
End Sub
TIA...Don

My sheet is setup for when you double click a cell in Column A. It archives specific column info in that Row into a second sheet. At the moment when you double click its not doing anything other than curse stay in the cell.

What could be the problem

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'set variable types
Dim endrw, arendrw As Integer
Dim wksht, arcwksht As Worksheet

'set variables
'I set worksheets like this for easy changes in the future
Set arcwksht = Worksheets("Archive")
Set wksht = Worksheets("Calc")
'last used cell on the Calc page in column A
endrw = wksht.Range("A65536").End(xlUp).Row
'first unused cell on the Archive page in column A
arendrw = arcwksht.Range("A65536").End(xlUp).Row + 1

    'if advert ends today, copy to fist blank row in Archive sheet
    If wksht.Range("H" & Target.Row).Value = wksht.Range("A1").Value Then
            'setting values equal to each other will be faster
            'and use less resources than copy and paste
            'if you have a LOT of data, then copy and paste could be faster
            arcwksht.Range("A" & arendw).Value = wksht.Range("A" & Target.Row).Value
            arcwksht.Range("B" & arendw).Value = wksht.Range("C" & Target.Row).Value
            arcwksht.Range("C" & arendw).Value = wksht.Range("G" & Target.Row).Value
            arcwksht.Range("D" & arendw).Value = wksht.Range("H" & Target.Row).Value
    End If
'clear variables
Set archwksht = Nothing
Set wsksht = Nothing


Hello . . . I have a couple functions that I would like to happen on "_beforeclose" event:
1. Sort certain ws by names alphabetically (staring with sheet 36)
a. Only sheets ending with "sd"
b. Some will be hidden, some not - (I believe we have to unhide any
that are still hidden first?)
2. Sort the data on the ws alphabetically using col A and ascending
a. There are headers on all sheets
3. Re-hide all the sheets except "order"

I have stolen, begged and borrowed some code to get this far: (no laughing out I'd rather offer up something than seem ungrateful and not even try.)

Private Sub
Workbook_BeforeClose(Cancel As Boolean)

Application.ScreenUpdating = False
  
'unhide sheets ending with 'sd'
Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Sheets
        If LCase(Right(ws.Name, 2)) = "sd" Then
        ws.Visible = True
    End If
    Next

'sort all of the unhidden sheet names alphabetically, starting with sheet 36
Dim N As Integer
Dim M As Integer
Dim FirstWSToSort As Integer
Dim LastWSToSort As Integer
Dim SortDescending As Boolean
SortDescending = False
     
    If ActiveWindow.SelectedSheets.Count = 1 Then
        FirstWSToSort = 36
        LastWSToSort = Worksheets.Count
    Else
        With ActiveWindow.SelectedSheets
            For N = 2 To .Count
                If .Item(N - 1).Index <> .Item(N).Index - 1 Then
                    MsgBox "You cannot sort non-adjacent sheets"
                    Exit Sub
                End If
            Next N
            FirstWSToSort = .Item(1).Index
            LastWSToSort = .Item(.Count).Index
        End With
    End If
     
    For M = FirstWSToSort To LastWSToSort
        For N = M To LastWSToSort
            If SortDescending = True Then
                If UCase(Worksheets(N).Name) > UCase(Worksheets(M).Name) Then
                    Worksheets(N).Move Before:=Worksheets(M)
                End If
            Else
                If UCase(Worksheets(N).Name) < UCase(Worksheets(M).Name) Then
                    Worksheets(N).Move Before:=Worksheets(M)
                End If
            End If
        Next N
    Next M
      
 'Alphabetize the data on sheets ending in "sd"
 For Each sht In ThisWorkbook.Sheets
    Selection.sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
          
 'Hide all sheets except order
Dim wkst As Object
For Each wkst In ThisWorkbook.Sheets
If wkst.Name <> "Order" Then
wkst.Visible = xlSheetHidden
End If
Next
End Sub
It seems redundant in some parts (and it doesn't work past unhiding!) I'm not sure how to clean it up. Thanks in advance!!