I'm trying to write a code so If Q2 on sheet1 equals " " then A2 on sheet2 equals E2 from sheet 1. If Q2 doesn't equal " " then " ". Also, I would like for this to work for the entire Q column, starting with Q2 down.
View 4 RepliesI created this hyperlink function. It links to a1 of sheet 1 to a1 of sheet 2. However, when I click on the a1 cell in sheet 1. an error message appears. I have attached the spreadsheet...
View 2 Replies View RelatedI want to do a loop where you can copy say A3 worksheet 1 then add another sheet naming the work sheet "A3" then copying A3 worksheet 1 to A1 "A3". After that looping to A4 to a new work sheet naming the work sheet "A4"copying the value to A1 "A4", etc...
Is there a simply way of doing this loop? I can probably fit my other coding into the structure.
I have some experience with excel, but until now have not ventured into VBA and macros.
I have a workbook which will have the following sheets:
1.Absence Summary sheet - Summarises data from each employee's individual sheet.
2. Template Sheet - A sheet formatted as an absence record sheet, but without data.
3. Individual employee Absence record sheets - Based on the Template sheet.
I have read with interest the various posts and help files on User Forms & Macros, but have got a bit stuck.
My Aim: ....
I want to be able to copy a name from one sheet (Available Players), paste it to a cell in another sheet (Round 1 through Round 20). The cell that will be copied is fixed but the place where it will be pasted will be different and may be on a different sheet.
also i would like to change the color of the copied cell to "greyed" out or cut if it can not be greyed out. I have created a button and put in a macro that i created but have been having problems with it, generic 1004 errors that i can not figure out. i am attaching the document.
I want to create a macro button that can create copy, insert, paste and rename the new sheet in next month's name, like if the active sheet's name is January, I want to copy the whole sheet of January, insert new sheet, paste the new sheet and rename the new sheet to next month like February?
Also rename the new sheet (February) cell B3 the same as new sheet's name (February)
So if month of February is near end, the macro button in February will create the same way as Jan did which means the next sheet will be named March and so on.
Is there a way I can compare sheet 1 Column A, with sheet 2 Column A, and then move any duplicates from sheet 2 over to sheet 3.
For instance,
Employee Numbers that match in both sheet 1 and 2, remove that row from sheet 2 to and insert it into sheet 3.
Example, I have a sheet named DATA1, I want to add a new worksheet, copy a certain range from the DATA1 sheet to the new sheet and rename it the same name DATA1 and delete the old DATA1 sheet.
Also, the sheetname will be unknown, the macro must get the sheet name first.
This work is challenging for me, now am doing Comparing the Data between One Sheet to Another Sheet, and Paste into Different Sheet.
I am Manually Comparing the Data between two different sheet, If the Data is matching, or Not matching, those data i have document in other sheet, So i have lots of records available. so manually doing its very difficult, but macro can compare the data. I will give the details of my process and example sheet is attached here....
Data 1.xls == This sheet have multiple tab with data and different order no.(Multiple Tab with thousands of records available)
orderNumberModified.xls == Modified data available. (Compare the OrderNumber to Data1.xls)
Now we have to compare the modified.xls and Data1.xls... For modified.xls have ORDERNUMBER available, that Numbers match with all tabs from Data 1.xls sheet.
If ORDERNUMBERS are matching, Need to copy the entire row from modified.xls To AVAILABLE TAB from Comparedata.xls sheet
If ORDERNUMBERS is not matching, copy the entire row from modified.xls To NOTAVAILABLE DATA tab from comparedata.xls sheet
I have workbook having around 25 sheets and i want macro to merge all sheets except first (summary) in to last sheet ( mergedata ) inpute data will be start from cell A17 in all sheet. also sheets may be add or delete as per requirement. after merge data from sheet name should add after each entry .
attached sample file for your reference.
[URL]
I'm trying to write a function for one of my reports that would perform a boolean function to see whether or not a cell's value is on another sheet in the workbook. Pretty much (If it's on that sheet, Yes, if not, see if it's on this sheet). Here's what I have so far
Code:
Public Function ISTHERE(CUSIP As String) As Boolean
If Sheet4.Columns("B").Find(CUSIP, LookIn:=xlFormulas, Lookat:=xlWhole) Is Nothing Then
If Sheet5.Columns("B").Find(CUSIP, LookIn:=xlFormulas, Lookat:=xlWhole) Is Nothing Then
ISTHERE = False
Else
[code].....
I would use the function like this in the spreadsheet:
=if(ISTHERE(A2)=TRUE,"Yes","No")
I get yes everytime. What am I doing wrong? Or am I even visualizing the answer correctly?
I have a the following function: =INDIRECT(ADDRESS(SUMPRODUCT((F35:G45=K40)*ROW(F35:G45))-21,SUMPRODUCT((F35:G45=K40)*COLUMN(F35:G45))))
I'm trying to put the get the value of it from a different Sheet.
say this function is in Sheet1 and I wish to place the equation in Sheet3 - what should I change? I tried INDIRECT(ADDRESS(SUMPRODUCT((Sheet1!F35:G45=K40)...
nothing works.
what the function does? there are 2 tables. table 1 has values and table 2 has values as well. K40 is the user input --it reflects the number in table2 and the return value is the 'reference' in table1. for example, if table2 in F41 has the number 54 and the reference for it in table1 is $344, when the user types 54 in K40 the returned result in the function will be $344
I'd like to compare values from different sheets. My sheets contain data from different years. I have sheets named 2000, 2001, 2002, 2003.... I also have a sheet named "compare". Now I'm using functions like: =('2008'!J13/'2007'!J13)-1
which gives me the relative change between years 2007 - 2008 in the cell J13.
I'd like to use the sheet name as variable. For example: "year 1" is given in cell A1 and "year 2" given in cell A2. Then I could enter (for example) 2007 in cell A1 and 2008 in cell A2. How can I use these cell values in the function? I’ve tried something like: =('A2'!J13/'A1'!J13)-1. I assumed that 'A2'!J13 would be the same as '2008'!J13. It didn’t take me long to figure out that doesn’t work. What would be the right way to do this?
I need a function where the sheet-reference can be linked to a cell.
By other words can the content of cell A1 be sheet1, sheet2, sheet3 etc. The cell in B2 shall always point to cell b3 in the choosen sheet, but the sheetreference is dependent on the sheet refererred to in A1
I'm trying to use the value of a cell as the sheet name in the array range for my index function, pointing to data on a different sheet. Here is the formula I have now, which points directly to the other sheet (and works fine):
=INDEX('SheetName'!A4:BE189,MATCH(A87,'SheetName'!A4:A189,0)+36,54)
This formula takes the value in cell A87 and looks for it on the sheet called "SheetName", then returns the value in a cell down 36 cells and across 54 cells. This is perfect as is.
I want to be able to have it look something like this:
=INDEX('CellValue where CellValue=sheetname'!A4:BE189,MATCH(A87,''CellValue where CellValue=sheetname'!A4:A189,0)+36,54). I have tried using the INDIRECT function here to get it to read just the sheetname, but cannot seem to get it to work.
I have I workbook that contains 2 sheets. The first sheet "Business Objects" is the master list. The second sheet "Gene" contains similar data but is incomplete. There is a unique identifer for both sheets and that is item ID. what I would like to do is look at the Item ID column on sheet1 compare it to SS# on sheet two and copy any rows from sheet1 to the end of the sheet in sheet 2. I have found in my searches on this forum a bit of code that identifies the ones in sheet1 that are not in Sheet2 and highlights them red(which is not neccessary for me, but I am struggling to figure out how to take that and paste it to sheet2.
View 3 Replies View RelatedEXAMPLE: Complete Sheet called "Day1". When day1 is complete you click on button and it then copies itself and creates and renames new sheet to "Day2", then when "Day2" is complete you click on button and it then copies itself and creates and renames new sheet to "Day3", and so on and so forth to "Day30".
View 9 Replies View RelatedEach sheet contains the test data for an individual class and I am trying to create a "Totals" sheet where I can show the average that each class had on a particular test and the letter grade breakdown. I am trying to create one 'average' formula that I may then copy across that will change the sheet reference automatically without me having to manually re-create the formula every time I add a new class sheet. I tried using the INDIRECT function but could not get it to work. Keep getting #Ref error.
View 3 Replies View RelatedI need to include in my roster sheet that if a client is "present" my instructor will check the box. This (idealistically) should add +1 on the column labeled "classes attended" in sheet labeled 5 groups. I've been at this for 3 hours and still have gotten NOWHERE
View 5 Replies View RelatedI have a check box on a worksheet that when checked calls for a timer code from a module. This works fine however what I am finding is that the code in the module is applying the result all the sheets in the workbook not just the sheet where I have activated the check box. I am a novice at VBA and no doubt there will be a simple solution. How do I limit this to my active sheet. This is the code in the module
Code:
Public Sub Watch1()
If Sheet2.CheckBox1 = True Then
Range("AH2") = Range("AH2") + TimeSerial(0, 0, 1)
[Code]....
I have created a simple Macro to change the Tab color of a worksheet based on the value of a specific cell. This spreadsheet has many worksheets (>20) and this sheet I have used as an error detection. If it see's an error on some other sheet, this cell will change from "OK" to "Error". The Macro then changes the Worksheet tab color to Red. The Macro works fine but ONLY if that worksheet is the open/active sheet (which kind of defeats the purpose). How can I trigger this Macro when this Worksheet is not the active sheet? Here is a listing of the Macro
Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCell As Range
Set KeyCell = Range("A11")
If KeyCell.Value = "OK" Then
Me.Tab.ColorIndex = 6 ' Yellow
Else
Me.Tab.ColorIndex = 3 ' Red
End If
End Sub
I have this MAXIF array function that I am trying to use but I need it to reference the Last Row and not the absolute refererence. I am a novice at VBA. The first max if array is the one with my effort to try and get the last row. It doesnt work. The second one works but references the cell.
Code:
Range("U2").Select
Selection.FormulaArray = _
"=MAX(IF('Monthly Production'!R2C1:RC1 & LR=RC[-20],'Monthly Production'!R2C19:RC19 & LR))"
Range("V2").Select
Selection.FormulaArray = _
"=MAX(IF('Monthly Production'!R2C1:R8729C1=RC[-21],'Monthly Production'!R2C21:R8729C21))"
I have 2 pivot table in 2 different sheet and i want to consolidate the data in one sheet.
Pivot table:1(order processed) as below
date
MR X
MR Y
MR Z
MR S
[Code].....
I need the data as below in separate summary sheet. 2 column in each employee(1 is processed and completed), i dont have option here to create 2columns here hence i put "/" in between.
date
MR X
processed/Completed
MR Y
Processed/completed
[Code].....
How should this read? So SheetName is the variable in the range funtion?
Dim SheetName As String
SheetName = ActiveSheet.Name
Set AllCells = Range('" & SheetName & "'!B7:'" & SheetName & "'!B60000")
I have a number of worksheets with data in various rows, all rows of data have UID's. I want to create a search function, which returns the location of the of the UID. Or better still the cursor will jump to the cell where the UID is located. It's very much like the way the 'Find & Replace' function works.
E.g. If I type the UID of 1234 into cell A1 I want to be able to press a 'Find' button (which will be located alongside cell A1) that will search the entire workbook for 1234.
Cell F4 contains a worksheet name which was “calculated” by an IF function (IF something, then ‘T2’, otherwise ‘T3’).
Cell F5 must then look at either sheet T2 or sheet T3 (depending on what it is told to do by reference to cell F4 just above) and report what it finds in cell A1 there.
Clearly I must use the INDIRECT function. But I have tried every imaginable combination of single and double quotation marks to produce the equivalent of
=T2!A1
all to no avail.
Is it something to do with letter and number combinations?
Alternatively, how else should I achieve what I want?
PS F4 could have been “calculated” by any other means, e.g. =MONTH(TODAY()).
Excell 2003
I put = cell("filename") in cell A1 on every sheet to display the full pathname of that sheet.
it seems that the path does not always display the correct sheet name- rather it seems
that when changing from one sheet to another the path is not always updated.
Is there a switch that needs to be activated to ensure that the path is updated correctly when changing sheets on the same worksheet?
I have a vlookup formula which can refer to any one of several sheets labelled by month.
I need to be able to show in a different cell which sheet (month) the formula refers to.
In other words - displaying part of a formula in a different cell.
How do I incorporate sheetnames into the vlookup function? I need the following sheet name: SALES (located in A1) to be incorporated into the function: vlookup("Sales"?,a1,n84,2,false). .
View 5 Replies View RelatedI want to create a formula that reads cell B6 of a different sheet. The sheet name is in cell A1, and I want to get the results in B1.
Later I will change A1, and I want to get in B1 the cell B6 from the different sheet. What is the formula in B1.