Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Connecting Two Sheets

If data in A1, Sheet 2 = to A1, Sheet 1, then B1, Sheet 2= to B1 Sheet 1, otherwise Nil.

This will be put in Column B1 of Sheet 2 ....

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Connecting Two Sheets ..
I need to connect my two sheets using the value in a column...

Is it possible in Excel?

Actually in one sheet i have only the empno number and his performance report.

(in tabular form)

but in other sheet i have full information about that employee, So I wanna click on any empno and jump to other sheet to see details about that employee...

I cannot use hyper link as i have 50,000 employees and it will be very tedious to hyperlink all of them one by one...

View Replies!   View Related
Connecting The Dots
I have 1 row of user entry cells (A1:Z1). To keep it simple, let’s say A1 is always 0 and Z1 is always 100.

The user can enter any positive number in any of the cells. For Case 1, let’s say ‘20’ in M1.
I want a set of formulas in A2:Z2 that fills in the values with straightlined values from 0 to 20 from A2:L2 and 20 to 100 in N2:Z2.

For Case 2, the user enters ‘10’ in M1 and ‘60’ in T1 and the formulas in A2:Z2 fill in the appropriate values that connect the empty cells (i.e., 0 to 10, then 10 to 60, then 60 to 100).

Basically, a user enters 1 or more numbers in 1 row, and the second row fills in all the non-entered cells with #s that are straightlined, thus ‘connecting the dots’.

I think a bunch of nested IF statements might work, but it’ll be hairy and nested IF statements are a resource hog. Any better ideas? I’m think maybe some INDEX and MATCH functions.

View Replies!   View Related
Connecting Macros..?
I have 14 ranges like this on the same spreadsheet and would like to print all of them based on the result in the target cells for the range of each to print. I currently have a macro button to print each range as you can see. Is it possible to connect these? What I have tried is cutting the End Sub and then the Dim r As Long but get errors.

View Replies!   View Related
Connecting Points In A Graph
I have a data and it has been plotted into a graph. Here's what i have: I have 2 points, point A and point B with caps, 1.00 and 2.00 respectively. So now i need to draw 3 parallel lines, i.e from the caps and the main point itself. How do i do that? Manually will be of course selecting a line from the shapes. Any suggestions?

View Replies!   View Related
Connecting A Date To A Range
I would like to do a sort of lookup function, but am having trouble.

What I would like to do is:

In one column, list all financial years (ie 2006-07, 2007-08 etc - listed in cells A2 - A10). In a particular cell, I will have a date, lets say cell B2. Then in cell B3, I would like to know which financial year that this date in B2 fits in. So for example, if the date was 10/10/2007, it would return the following "2007-08".

View Replies!   View Related
Count Function In VBA When Connecting To Access
I have an access database: 2007.mdb
I have an access table: All Data - Pre07

I have an excel worksheet which connects to 2007.mdb and selects data from table All Data - Pre07 using a 'user defined' filter via an input box. This filter sets the group which is one of the fields on the All Data - Pre07 table.

The code which performs the above function is shown below.

I wondered whether it was possible to perform a Count function in vba (rather than a Select Function) which will just count the data in access and then return the count rather than importing all the data.

For example, I would like to count the number of rows in the Access table - All Data - Pre07 which have a group = 'AA' and then return the count to WorkSheet 'AA'.

Is it also possible to perform multiple count functions in one connection or do I need to reconnect (i.e. write the code again).

Application.ScreenUpdating = False
Dim mygroup As String
Dim datinput As String
mygroup = InputBox("Enter Group", "Please Enter", "AA")
datinput = "'" & mygroup & "'"

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=C:2007.mdb;DefaultDir=C:;DriverId=281;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
)), Destination:=Range("A1"))
.CommandText = Array( _ ................

View Replies!   View Related
Connecting Between VBA Excel And Web Site
I have a some problem with connecting between VBA Excel and web site.

I wrote a macro to insert the data from excel but there is one problem with displaying the correct view.

I use this site to get the price of the bonds:

it is the site of russian exchange

First of all I write the ISIN code, for example RU000A0E6X12, in the "Quote Search" field and then press Enter for getting the data. After it I can choose the History information or Online from the last field in the left margin. I want to chose "History" and after it the dates from and till are appearing automatically. The automatization of it is my task. And I have only one problem in the end.

The program:

View Replies!   View Related
Connecting To A Database Without The Local Machines Odbc
I'm working on a spreadsheet that will connect to a firebird DB (Programmed using VBA and ADO intially).. The spreadsheet itself will reside on a server in a shared folder..

When a user opens the file up, I'd like for the spreadsheet to use the servers ODBC connection, and not the client machine.. This way we don't have to install the Firebird ODBC driver on each client workstation..

View Replies!   View Related
Line Connecting A Cell To A Table....and Locked On The Cell
I have multiple data tables in one sheet...and each one has a that sheet. To better allow the viewer to coordinate quickly which data table corresponds to which cart...i want a line between them. Excel already has the draw line function, but if you move the chart around, the line doesn't move.

View Replies!   View Related
Hiding Sheets But Still Letting Macros Run When Printing Hidden Sheets
i have a workbook that has the following sheets

working sheet
job sheet
receipt of deposit letter
completion sheet
delivery note
delivery note (2)

i want to hide every sheet except the working sheet.
I have tried this but the macros bring up an error when i run the macro

my macros involve printing certain pages dependng on what button is pressed

i get an error whatever
how do i stop this

View Replies!   View Related
Copy Data From Sheets In Workbooks In Folder To Main File Sheets Of Same Name
I would like to use VBA to search a folder and copy data from tabs within the excel files there. The data will be pasted to a tab of same name in the the main file. All the files are in the same format.

So far I have only managed to list the files in the folder using code I found on your site!

View Replies!   View Related
Delete Chart Series Across Sheets But Skip Protected Sheets
I have received following macro from someone to delete series but the problem is that it gives error when some sheets are protected, and I want those sheets to protected. When run it will ignore/leave protected sheets but delete series only from unprotected sheets

Private Sub CommandButton1_Click()
Dim Rng As Range, i As Long, r As Range, lVal, uVal
Dim DeleteCount As Double
Dim lRow As Long
Dim dr As Long
Dim dc As Long
dc = Sheets("Deleted Numbers").UsedRange.Columns.Count - 1
dr = Cells(Rows.Count, Sheets("Deleted Numbers").UsedRange.Columns.Count - 1).End(xlUp).Row + 1
If dr = 60001 Then ................

View Replies!   View Related
Copy From Multiple Sheets (26), PASTE To 1 Sheet From 26 Sheets
I have a workbook with 26 sheets, labelled A to Z. Column A in all the sheets have names from rows A6:A35.

I need a macro or a code to extract all the names from each of the 26 sheets and paste it to a new sheet 'Names' under column A, such that names starting with 'B' paste under all the names 'A' and so forth till 'Z'.

View Replies!   View Related
Copy 2 Linked Sheets Without Links To Original Sheets
I have copied and then pasted & linked numerous cells from one sheet to another within the same workbook. When I copy the 2 sheets (Edit>Move or Copy Sheet>Create Copy) the linked cells on the duplicates remain linked to the original sheet. How can I copy the 2 sheets and have the cells on the copied sheet be linked to the copied sheets and not the original?

View Replies!   View Related
How Increase Sheets Number+1 For All Sheets With Vba?
How increase Sheets number+1 for all Sheets with Vba?

View Replies!   View Related
Move Specific Data From Sheets To Other Sheets
Need to move data from 14 sheets (1 pay period=14 days)(2 showing on attachment for example purposes) to time cards for each person (number of employees will vary).

Key data to move to the time card is the date of the hours, #Reg Hours, #OT hours, the ticket# and job# for those hours.(ie Chris Adams Aug 15, need Chris's Reg and OT hours (8 Reg and 2 OT) on his time sheet, and the corresponding ticket number and job number (in this case from I1, I2).

Unknown number of ticket/job numbers, but the pattern of cell entries will remain the same along the top of the date sheets.(starting at I1, then L1, O1, the next would be R1)

Unknown number of employees but they will continue filling down where the names are on the date sheets (column A). Each employee would have a time card sheet as well.

View Replies!   View Related
Count Sheets To Make Sheets Statistics
how to count sheets to make sheets statistics. (see code)

View Replies!   View Related
Sheets Array To Include All Sheets Between First And Last
how do i create a sheetsarray to include all sheets between First and Last? I plan to loop through each sheet in this array to copy data to a summary page, as per [url]

View Replies!   View Related
Combine To Sheets In One Sheets Using Macro
Combine to sheets in one sheets using macro ...

View Replies!   View Related
Adding & Naming Sheets "X" No Of Sheets Dynamically
I have an Index Sheet where I would be typing the name of the Sheet and a Command button to execute the operation of Adding the Sheet with the name mentioned in the Column C..

View Replies!   View Related
Re-Naming Sheets Per Cell Data & Hiding Sheets
on sheet1 I have a button I need to do the following when clicked:
(1) name the next 30 sheets based on cell values in sheet1
(2) for those 30 sheets, hide some of them based on a y/n input in sheet 1

To clarify: the worksheets do not need to be created, they already exist. They just need to be renamed and hidden based on that y/n criteria. see attachment with just 1 worksheet for clarification. So - The next 30 sheets are to be named by the following ranges (B7:B16), (B21:B30) and (B35:B44). For every product with a "n" in column C of sheet1, the worksheet for that product needs to be hidden.

View Replies!   View Related
Run-time Error '1004' Method 'Add' Of Object ' Sheets' Failed Adding Multiple Sheets
I have been running a simulation for about 18 hours now and just received:

Run-time error '1004':
Method 'Add' of object ' Sheets' failed

I have been creating new sheets, importing data, pulling some values from the data then deleting the respective sheet. I am using:

ActiveWorkbook.Sheets.Add after:=Sheets(Sheets.Count)

The sheet is actually being added to the workbook, seemingly before the error. I resume the code, and a new sheet is placed in the workbook and it errors again. The Debugger stops and highlights on the code above.The sheet count number was 10895 at the error, just as an indicator of how many times the simulation has performed successfully. I am hoping this is something I can fix without having to start over...

View Replies!   View Related
Lock Sheets With Formulas And Lock Sheets With Values
do you know if there is vba code to lock formulas so someone sees but does not touch and the same for values see but cant change??

my sheets with formulas are SUMMARY and Sheet2

sheet with value is TABLE

View Replies!   View Related
Copy Between Sheets
I have data in Sheet1 of the Excel sheet. I want to copy the content of Column C & D of Sheet1 into Sheet2 using a macro.

I am able to do using below logic (But i think there is a simple logic):
Get the Last record count of Col C
For i=1 to Last
Next i

Since I am using for loop, it takes long time if the data is huge.
Could you please let me know whether my approach is good or there exists a simpler method.

View Replies!   View Related
Delete (2) From Sheets Name
How sould I remove (2) from sheets names

Like sheet1(2) change into sheet1

View Replies!   View Related
Comparing Two Different Sheets
I’m trying to figure out how to compare two different versions of a sheet with each other and to identify which lines has changed. Here is a simplified example:

>Microsoft Excel - post data.xls___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutE4J4E5J5E6J6E7J7=BCDEFGHIJ3V1CONCATENATE V2Change check4123123 1012 No Change5456456 465Changed6789789 8 9Changed710 121012 123No ChangeSheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

When using this formula I get an error in cell J12 that reports a no change even though the values has changed. The error occurs due to the blank cell found in cell C7 but I can’t seem to find a way around the problem? (except for adding an if(cell=””;0;cell) before every cell which isn’t really practical in my actual data)

Furthermore my actual data has more the 10.000 lines and +30 columns so if the formula I use for change tracking can be done easier in any other way I would be happy to get some help? Maybe I should even consider a macro?

View Replies!   View Related
Navigate Through Sheets
i have an excel file with over 30 sheets on it. Contained in each sheet is a seperate pivot chart. i am looking for away to navigate around these sheets from the main screen. does anyone have any suggestions or opinions on the best way i can do this? here are my ideas but not sure which would work best

1. Create main sheet at the start and have hyperlinks to all sheets (by doing this how could i navigate back to home page?)

2. Create userform with drop down list of sheet names or search function to search for sheet names and then click on a button to go to sheet? dont know would i be able to do this? will the userform follow to the sheet with the pivot chart so i can select a different one from the same page? how could i hide the userform to view the chart and then bring it back to navigate to another?

View Replies!   View Related
Agents Sheets
attached file which contains about (150) agents sheets,i need a way which will move me to the desired sheet by pressing its name from the list in the main one , i need this because i shall hide all the sheets.

View Replies!   View Related
Hiding Sheets
A workbook with 7 sheets in it. The first sheet contains buttons with macros that when clicked will navigate you to different sheets in the workbook based on what button you click. Here's what I want to do. When the workbook is opened, I want it open to that first sheet with the buttons, but show no other tabs for the other sheets at the bottom. And then when that button is clicked on the first sheet to go to a different page, I want that page to be the only visible tab in the workbook with the rest still hidden. Is that doable? I already have the buttons and codes and everything for the navigation part down, I just need to have the other hidden when I'm on a different sheet.

Sub Button10_click()
Worksheets("Account Type").Activate
End Sub
Sub Button11_Click()
Worksheets("Name Change").Activate
End Sub
Sub Button12_Click()
End Sub
Sub Button13_Click()
Worksheets("Main Menu").Activate
End Sub
Sub Button15_Click()
End Sub
Sub Button16_Click()
End Sub

View Replies!   View Related
Compare Two Sheets
I have two spreadsheets each with "Vendor names" on column A. sheet 1 is my master sheet and sheet 2 is the sheet that has the information i like to carry over to sheet 1 when the conditions are true. Hence when colum A on sheet 1 equals column A on sheet 2 copy the range of cells b-h in sheet2 to match to sheet1.

here is my code i figuere that if i stared with two columns it would be rather simple however i cant even get my own code to work. Any help or suggestions would be greatly appreciated. By the way my spreadsheet is 6,000+ lines long

Here is the code

Sub compares()
Dim rng1 As Range
Dim rng2 As Range
Dim RowNo As Long

Set rng1 = Worksheets("Sheet1").Range("A1", Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp))
Set rng2 = Worksheets("Sheet2").Range("A1", Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp))
For Each c In rng1
If Application.WorksheetFunction. CountIf(rng2, c) > 0 Then
RowNo = Application.WorksheetFunction.Match(c, rng2)
c.Offset(, 1).Resize(1, 2).Value = Worksheets("Sheet2").Range("B" & RowNo, "C" & RowNo).Value
End If
Next c
End Sub

View Replies!   View Related
Lookups Between Sheets
I have a player list, from which i have put a sample below:

Garry Adamson ar 23 2 45 12
Charlie Baxter ar 10 0 23 14
Iqtiwar Chaudray ar 0 0 1 2
Hugh* Doulton ar 0 5 12 10

On the left is the player name and then to the right are their scores for the first 4 weeks. Each team that has entered the league has a tab of its own with the 11 players they have selected on. I would like to just have to update the main sheet and then have a look up in the other tabs on an if the player is ion this squad basis... I have attached an example of the whole player list and an example of a team on the 2nd tab.

View Replies!   View Related
Subtotal All Sheets ..
What i would like to be able to do is subtotal all the sheets (Approx 190 Sheets) with subtotals in Column I and (K to AA) with each change in Column D Period reference D3 till the bottom the data is entered. As an example i have attached a sample with one sheet 77001 showing what result i would like.

View Replies!   View Related
Sumproduct Between More Than Two Sheets
How can I use SUMPRODUCT between more than two sheet (Sheet1 to sheet4)

I can use this formula for one sheet it gives me the correct result. But I have to give sheet range form 1 to 4 How?


View Replies!   View Related
Protecting All Sheets
I am trying to protect all sheets on a Workbook Close event.

I don't want the user to even be able to Select the Loked cells so I set it up that way through formating of cells.

This is what I have tried:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
sh.Protect Password:="P@ssword", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
' sh.Protect Password:="P@ssword"
Next sh
End Sub
I even tried the commented out line in that postion instead of where it is now.

It does protect but still allows selection of locked cells.

View Replies!   View Related
Protection Of Sheets
I have at average 10 sheets in one workbook. I have to protect 9 workbooks.

Can I protect one workbook in one go rather than protecting each sheet in every workbook?

View Replies!   View Related
Print 2 Sheets
I have 2 sheets that I want to print off, these are called Questions & Answers.
I have the following code to print off Answers, how do I amend this to print off Questions & Answers?

Selection.Font.ColorIndex = 1
.Visible = True
End With
End Sub

View Replies!   View Related
Integrate Two Sheets
In Sheet1 I have the following horizontal fields:

Order Qty#

In sheet2 I have the following horizontal fields:

How can I automatically add the address fields from sheet2 to the appropriate line in sheet one with the matching cust#?

View Replies!   View Related
Add Sheets
i want to add sheets to my workbook
and i want evey sheet to get it name form range("b5:b262")

View Replies!   View Related
Same Cells - Different Sheets
I have 20 sheets in my workbook, I know if i use the following code, that it sums all of the values AC10 across my 20 sheets.

However, is there a way that I can show the value of AC10 in each sheet without having to manually select or edit the cell in the function/formula?

View Replies!   View Related
Update Sheets
Could you have a look at my attached sheets?

2 sheets here, one is "TEMP" the other is " SUM".I want to have a command button in the "SUM", in order to do the below actions the right now A columns between "SUM" and "TEMP";

2.If it does not exist in "SUM",i.e. it is new,then move the row to the last row of "TEMP";

3.If it exists already in "SUM",then update the row.

View Replies!   View Related
Delete All (except One) Sheets
I would like to delete all except one sheets in the workbook by pressing onto a button. So I wrote the folllowing code (its obviously only a part)

Application.DisplayAlerts = False
For Each Worksheet In Worksheets
If (Worksheet.Name "Instructions") Then
End If
Application.DisplayAlerts = True

and put it into a Module in the same workbook. The button itself is in one of the sheets to be deleted, so I assign a simple macro to it which is just calling the code above.

Well, sounds simple, but it does not work, with "Subscript out of range" error at the attempt to delete one (not the first one) of the sheets.

Searching around suggested that the problem appears when the worksheet I try to access does not exist (missspelling of a name or so). but in my code I dont really use it.

View Replies!   View Related
Consolidating Sheets One Below The Other
I have written a macro to consolidate multiple sheets into one sheet. I have two sheets which are to be copied one below the other.I am attaching the code for this here.

ub Bingo()
Dim ws As Worksheet, n As Long, flg As Boolean, last As Long
On Error Resume Next
Application.DisplayAlerts = False
Application.DisplayAlerts = True
On Error GoTo 0
Sheets.Add(before:=Sheets(1)).Name = "Consolidated-Input"
n = 3
For Each ws In Worksheets
If ws.Name "Consolidated-Input" And ws.Visible And ws.Name "DCS-User" And ws.Name "Cal" And ws.Name "DCS" = True Then
If Not flg Then
Sheets("Consolidated-Input").Range("a1").PasteSpecial xlPasteValues
Sheets("Consolidated-Input").Range("a1").PasteSpecial xlPasteFormats
flg = True...................................

View Replies!   View Related
Eqaution Into Sheets
I have entered the following eqaution into one of my sheets...


Now, even when AC3=1 and AD3=3, I'm still getting 0 as the result when I
want it to be 6.

I actually have 12 different combos to test for, but I'm trying to get just
one working right now. I'll cross that other bridge when I manage to get past
this one.

View Replies!   View Related
Indirect Using Various Sheets
I have 40 sheets with info and 2 summary sheets. One summary sheet will summarise the data on sheets 1 to 20 and the other 21 to 40.

Im using the following:

This works fine for my 1st summary sheet enabling me to display the vlaue of X10 in sheets 1 to 20 in D10:D29.

However in the 2nd summary sheet I wish to display X10 in D10:D29 but only using sheets 21 to 40.

Is there a way to eliminate sheets 1 to 20 and just use 21 to 40.

View Replies!   View Related
Move To Sheets
looking for the most efficient way of doing this.

I have a sheet and basically they'll be groups of funds

so it looks like this:

Summary of Fund: Name of fund
sub total
Summary of Fund: name of fund2
sub total
Summary of Fund: Name of fund3
sub total

I want to grab each section and place it in its own sheet and have the sheet named after the fund.

View Replies!   View Related
Merging The Sheets
I need help in merging sheets in 4 workbooks, meaning bringing the sheets in all 4 workbooks into 1 workbook.

View Replies!   View Related
Count If Across Sheets
I have a list of Tech IDs in coloumn A with the students' name and number of visits to the tutor center in B and C. On a seperate sheet I have a log of every visit anyone made to the tutor center and their Tech IDs. I need to count the number of times a student's Tech ID is listed in the Intake Log sheet in coloumn F.

I am putting this into coloumn C of every student's row but getting a zero:

=COUNTIF('Intake Log'!$F:$F,"A" & CELL("row"))

I assumed it would count any cells in coloumn F of Intake Log that are the value of coloumn A (their Tech IDs) in the student's row.

I think it's searching coloumn F for "A3" not the value of that cell.

View Replies!   View Related
Deleting All Other Sheets
In writing Macros with Excel 2003, is there a way to have all other worksheets deleted except the present one, other than listing every other sheet?

View Replies!   View Related
Repalcing Sheets With VB
I have a sort of a program in excel, which the point of is for the user never to actually see any worksheets, only my forms.

Theres lots of worksheets, but its all based off one and does work from there. This sheet may get updated from time to time so I would like the user to be able to do this him self so I have two questions

1. If I had a form with a textbox option to enter or a directory (or browse if anyone knows how to make that work) could the user enter the file name for the workbook, and the name of the updated sheet in that work book and click a command and have it replace my sheet?

2. This sheet is a list of equipement and its detials, I would like to tell the user if any new items were added (check by tag number, collumn 3) and also if any details for the already existing equipments were changed, (only collumns 4,9,10,11) Since the row that the equipment was in could change from updated sheet to upfdated sheet, the only way I no of is by searching for the existing equipment on the old sheet, then comparing the collumns, then moving to the next equipment piece. After thats done compare every tag from the new sheet on to the old sheet and see if theres any new ones. Then replace the sheets

View Replies!   View Related
Format All Sheets The Same
On a daily basis I receive a workbook with one single spreadsheet. I then break it into multiple tabs (~ 40 to 50 different sheets). I am trying to apply the same formating as my data sheet (same column width, same row width, landscape for printing, same margins for printing, same headers/footers, etc).

I was originally going to use the macro recorder but ran into the issue that the sheet names are different each day. I tried the following bit of code as a start but keep getting an error.

View Replies!   View Related
Copyright © 2005-08, All rights reserved