Mr Excel & excelisfun Trick 34: Dynamic Range for Adding Three Methods

Hi,
I saw EXCELISFUN TRICK 369. I need to do something similar. I tried to follow his code and couldn't. Then I tried copying it and growing his records and still couldn't get it to work.

What I am trying to do is on the first list use an inventory list. Which could be about 2000 - 5000 records or maybe double that (not sure how big of a list I could use in Excel). But lets say it is the max number (if someone could tell me that number I would be most appreciative).

I then will load a second list, or would load a list into second column. I then want the difference (what is missing) from the second list to appear in the third list. If it can give me the row of where it is in the first list that would be great (not a problem if you can't). I just don't know why the code from that video is not working any help would be greatly aprreciated?

Thanks,

Peter Fraga
(fragapete@hotmail.com)

Hi All,

I was setting up a spreadsheet that was based on the following vid:
http://www.youtube.com/user/ExcelIsF...14/tqCEY5YMyqw

Dynamic sub tables based on a master sheet array formula

The formula in question is:
=IF(ROWS(A$7:A7)>$E$1,"",INDEX('2010Corn'!A$4:A$17,SMALL(IF(Table2[From]=$B$1,ROW(Table2[From])-ROW('2010Corn'!$H$4)+1),ROWS(A$7:A7))))

B1 is the customer I'm looking for, E1 is the count for the customer and the master page is 2010Corn. I have 20 sheets looking to this master page for data. It works great, except for an issue when adding a new line in the master table.

What is happening is when I get to the end of a row, I tab to enter a new line in the table. It takes up to a minute for the cell to change color and for me to regain control of the computer.

I have run a performance trace and while the computer is locked, one of the CPU core's is pegged for the whole time with a processor que of up to 10 items at a time.

My question is...does anyone have any hints how to optimize this formula?

Thanks
Tony

So I used Excel Magic Trick 185 to dynamically pull content from a spreadsheet: http://www.youtube.com/watch?v=6bGKhbUYOas

However,
It is only working for a few of my columns though and I am getting reference errors, http://www.mediafire.com/?gje8srbp8x2j48w

Can anyone check into my spreadsheet and help me understand why I get these ref errors? Is it because I have spaces in the column names or within the cells? I know a macro is probably a better way to do this, or an auto-filter, but I need it to dynamically update. Any ideas on how to fix this problem or a better way to solve it?

Thanks!

I'm having a lot of trouble with the Range formula. I think I understand it... although I could be wrong... basically I'm trying to copy the cell contents from one worksheet to another using the Range method along with the Cells method as such:

VB:

Sheets("Output").Range(Cells(1, 1), Cells(m,n)).Value = Sheets("Input").Range(Cells(1, 1),Cells(m, n)).Value 

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




There seems to be a problem with using Cells inside the Range method in this way... it looks like it should work, but it keeps giving me errors... Please help!

I am using MSSOAPLib.SoapClient to call webservices from the server.

Code:

Dim objSClient As MSSOAPLib.SoapClient    
Dim oXML As MSXML2.DOMDocument60  
Dim XMLObject
Dim strWSDL As String 

strWSDL  = "http://localhost:55723/Services/InvokeServices/DataBridge.asmx?WSDL"

Set oXML = New DOMDocument60
Call objSClient.mssoapinit(strWSDL)

If webMethod = "GetServices" Then
  Set XMLObject = objSClient.GetServices 
Else if WebMethod = "GetOperations" Then
  Set XMLObject = objSClient.GetOperations 
End if

So Whenever i create a server method I need to add it in the if conditions ,
Is there a way where we can call webservices methods dynamically. like latebinding the method name during runtime.

Just This Video For Fun

http://www.youtube.com/watch?v=0VvjrldlqI0

I have a Range named "BudgetData" that is from D1:P703. Column D2 to D703 are all unique names. Columns E2:P703 are all number sorted by months. My header row is line 1. What I have been trying to do is to have a formula that will be on another sheet to do look up the correct row in column D then start add across starting at column E and stopping at the number of columns specified in a cell on sheet.

Sorry if this does not make since been working on it for a while

hi, i'm using a formula to derive some data in a column of an excel tab. it is -

=IF(H2=5,(CONCATENATE(YEAR(E2),(0),MONTH(E2))),CONCATENATE(YEAR(E2),MONTH(E2)))

how can i add a dynamic range to this so that it updates automatically when the number of rows expand?

thanks,

nick

Hi All,

The following formula creates a dynamic named range that I use for a data validation drop down box:

=OFFSET('Sheet1'!$A$2,0,0,COUNTA('Sheet1'!$A:$A)-1,1)

This reads a pivot table list.

However, I would like to add one more option ("ALL") to the top of the dynamic list (for the data validation). As the dynamic list is populated using a pivot table, I cannot add the option of "ALL" to the pivot table source data to get it to pull through.

Any ideas?

Hi Gurus

I have a dynamic range "Employees" that is made with the offset and counta functions. It goes from A1:C1 and down to the first empty cell before A20:C20.

The column headings are age, gender, salary

I am creating a userform which I can add new people, or change people's details, or delete them.

I'm really struggling to figure out how I can delete someone in the list (maybe in the middle of the list) but I do NOT want to delete the entire row - just the row from Ax:Cx. When I delete that entry, I would like the entries below to shift up, but I also want the dynamic range parameters to stretch down to A20:C20. This is because I have other tables to the right and below this table.

Suggestions??

Thanks in advance

This is my current code. It does nothing when I click the command button
People List is the A column (names)
txtRows is a textbox at the bottom of the page which says the row number the person is in the list


Code:

Private Sub cmdDeleteEntry_Click()
With Range("People_Table")
     .Range("People_List")(txtRow.Value).EntireRow.Delete shift: x1shiftup
     .Rows(.Rows.Count + 1).EntireRow.Insert
     .Resize(.Rows.Count +1, .Columns.Count).People_Table
end with

end sub