Free Microsoft Excel 2013 Quick Reference

- Dynamic Range with unused formula messing up x axis on dynamic graph
- Scatter plot and line graph combination
- LINEST Function
- Use of IF and AND to Meet Certain Conditions
- How do I remove "empty" cells in a range?
- Multiple search and data return formulas
- Find data between worksheets based on date range
- Percentile function - help setting range + conditional
- Creating a UDF to show a trend to the minimum point in a range
- Recogising various products in 1 column + returning "in" or "out" of stock in another
- "unique" forumla in excel
- "MEDIANIF" Array combining two ranges
- DDE link changes value, how to trigger macro to write data to another worksheet
- Combining columns by finding last used row in a column
- Find Function using a Dynamic Table in Excel
- ActiveSheet.QueryTables.Add connection established not pulling current page data
- "Trouble With Sort Function"
- Double Click Function
- Unhide sheets, sort sheets and data and rehide

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...

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

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

=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

#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'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.

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 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)

=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!

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 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

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

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

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 FunctionI 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

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

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 SubIf 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!

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 Subhere 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

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 SubTIA...Don

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

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 SubIt seems redundant in some parts (and it doesn't work past unhiding!) I'm not sure how to clean it up. Thanks in advance!!