I am writting a Macro the following
lnum= Range("d1")
Now this works well when the cell is in that same sheet but lets say I am making reference to a cell that is in a diferent spreadsheet how do I write that?
I want to wirte a macro where i have to calculate the average of Stock Return for each company at the end of each year. Then copy the value and paste it in a diferent sheet.
I have attached the workbook for the same. I have highlighted the area with yellow colour.
sheet1 has a list of names. sheet2 has a list of names and some of the names are the same as sheet1. What I want is for the names on sheet1 to indicate (preferably change text colour) if they are on sheet2.
View 5 Replies View RelatedIn exceltip.com i found many solution of my problems
View 11 Replies View RelatedI'm trying to create a spread sheet for our staff holidays. I have the year dates all mapped out across the sheet, and the staff names running down the first column. I would like to do the following:
1. When an "H" is put into a certain day, indicating a holiday, the cell color changes to i.e. blue. The color will be different for each staff member. We have four members of staff! (big company).
2. The "H"'s are totaled up for each member of staff to indicate how many days holiday they've had. Or if the staff member has 20 days per year, 20 is in a cell next to their name, and each "H" takes a number from that, i.e 19, 18 etc.
Sorry if I sound like a complete noobie!
I have written this code;
VB:
Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Row = 2 And Target.Column = 22 And Target.Count = 1 Then
Sheet("Graphs data ref").Unprotect
Sheet("Graphs data ref").Range("E1:E1").Select
Sheet("Graphs data ref").Protect
[Code]....
The problem is the first code isn't working, it is running the macro whenever any cell is selected and not refreshing the auto filter on my table.
I hope I have provided enough information on here, if I haven't let me know and I will reply as soon as I can.
Data set has three columns: dates, cycle times for event and event type. I want to create a XY chart with cycle time ploted against date (filled circles connected by line). I further want the color of each of these symbols to change as a function of event type. I vizualize a chart where the symbols might be red, red, blue, red, yellow where red indicates event type 1, blue event type 2 and yellow event type 3. how I might build this chart.
View 1 Replies View RelatedFrom the attached file.
Column 1 is a file name
Column 2 is a list of file names (but some of the files have been split into -A and -B parts or suffixed with an A or B
I need to highlight in column B, if the exact file name in column A exists
Example:
Column A: BSDS-0001
Column B: does not have this exactly instead it has BSDS-0001-A and BSDS-0001-B
I need some indication/highlighting that BSDS-0001 does exist on column B
So an indication that the whole of the file name in column A appears partially in column B. I have tried using max character lengths searches etc but cannot get an accurate way of doing this.
I have made the macro that does as ,soon as i open the file, select today's date but only if date is in sheet5... sheet 5 is may so it works. Doing the same job for every sheet in the file.
View 10 Replies View RelatedI seem to running into dead ends with each previous post concerning this similar idea, despite brave efforts of kind people . . .
So here is another thought: As the title suggests, can one help me with code that would take the value in (say) Sheet1A1 (Say Harry) and use that value to search for a sheet with that name (Harry) in the same workbook and then link to that sheet, cell A1 (Harry!A1)?
The other posts are here, should you want to know, but they differ as far as the linking method goes:
Post 1, Title: Newly created Tab Names placed as Hyperlink on Content Sheet
Newly created Tab Names placed as Hyperlink on Content Sheet
Post 2, Title: Automatically redirect hyperlink when cell value changes
Newly created Tab Names placed as Hyperlink on Content Sheet
I am trying to do some thing simple, but i causes me a lot of problem in several areas. I am using the
sheets( range("c5")).select
to simply select the sheet that is named in cell c5 on the current sheet. can anyone give me a correct way to accomplish this that I can use in other applications that i come accross?
I have a matrix with numbers that each cell is composed by a function of two parameters (two columns). The formula is
"=COUNTIFS(T2:T99,"15",V2:V99,"14")",
So it's counts when in one cell column I receive 15 and in the other 14. For instance, I receive the number 3 - so I have three rows that match (the first column with 15 and the second with 14).
I want, when I select the cell from the matrix (table) with the number 3 (that I receive from the formula) it will highlight the relevant rows..
I am having trouble getting the selection of sheets to work. I have a workbook that has multiple sheets and one constant sheet (Summary). There is code to create new forms in this workbook and insert them after the Summary sheet. These forms all have a date input that is formatted as a date (mm/dd/yyyy), these dates get modified on the day the form is created, there may be any number of sheets created during this process. I have to print the summary sheet and only the newest forms created. I need a code to select sheets to print based on the date input of a user for each form. This is what I have so far:
VB:
Dim i As Variant
i = Range("B5").Value >= InputBox("What date to start PDF from? Format = mm/dd/yyyy")
Sheets(Array("i")).Select [code]....
If the dim can be taken out and just included in the line for the array that would be fine with me. The cell "B5" is where the date is located in each form. I want to input a date and the macro will select the sheets where the date is equal to and greater than the date entered. The Summary sheet will always be included in the print set. I have a dialog box for setting which printer to use - this file will be used at different offices and therefor the printers will be different and it will also allow to create a PDF if desired.
I have a command button on sheet MASTER. When the workbook is Activated I want it to check and see if in sheet COSTM, cell B3 there are the words "Project Number", if so then show command button (ClearPrevious), if not, don't show. Also, when the If statement is finished, then the workbook needs to end up showing the sheet MASTER. I have tried various codes and none work, or they are on perpetual loops. I know this has got to be simple, but cannot find an example to take from to solve the issue. Would appreciate any help offered. Below is code I have right now.
Private Sub Worksheet_Activate()
If Sheets("COSTM").Select Range("B3").Select = "Project Name:" Then
Me.ClearPrevious.Visible = True
Else
Me.ClearPrevious.Visible = False
End If
Sheets("MASTER").Select
End Sub
Sheet 1 is my main page containing all my data, full of formulas and is protected. Sheet 2 is a simple sorting page with no color and is simply for sorting and printing.
Sheet 2 has IF formulas refrencing rows and cells on Sheet 1.
Example Sheet 1 A2= Bob
Sheet 2 A2="" until I put in this formula:
=IF(Sheet 1!A2="","",Sheet 1!A2)
Now Sheet 2 A2= Bob
Simple...
But when I delete a row in Sheet 1 (using my cool new macro...long story)
I get #REF! in the cells on my sorting sheet, as I should I guess, because there is no more row there to reference. It's annoying, but what do I do to fix it without just unchecking the #REF! in autofilter on Sheet 2.
I just want to eliminate #REF!
Do I need to put something else in the IF formula?
I work for one half of a joint venture & am responsible for planning & expediting. The other half does purchasing. The bi-weekly PO download reports I receive are less than useful. I have already written the code to delete undesired sheets & add, format, and enter headers for a "Summary" sheet.
I need code to move to the next row, and run formulas to pull data from the next sheets, and repeat until there are no more sheets.
The number of sheets will vary from one download to the next, and the sheet names will vary from one download to the next.
Following are example formulas that need to be run on successive rows while pulling from successive sheets.
I am running Excel 2013 on Windows 8.1
I have a workbook with a Cost Analysis sheet, a master menu sheet, and then all the sub sheets. I have coded command buttons on the master sheet, so once a category is selected, it goes to the appropriate sub sheet. On the subsheet is a list of items of varying sizes, and command buttons beside that which will allow the user to "Select" that particular size. Once the size is selected, the verbage in cell B5 plus the verbage (size) beside the selected amount, and the prices (labor and materials) in the next two columns, need to be transferred to the first available blank line, starting with line C5, to the Cost Analysis sheet. I have never done coding before for a Loop (i.e. For i = ???) and am not sure how to get it to do what I want. After they select that, they can pick a command button that takes them back to the master menu so they can continue picking various items until they are through. Then the Cost Analysis sheet will show all the items they picked, (name of item, material cost and labor cost: Columns B, C, & D). Not everything is coded yet (such as selecting a command button to go back to the main menu, because I wanted to make sure I could solve this problem before doing the easy stuff).
I read over quite a few of the previous threads and am not sure how to change them to suit my circumstance. I know some code (VB), but not much. I do not understand the concept of DIM, and how the loop works to make it happen myself, and all the websites I could find were not suited to my situation of copying three cells of data to the first blank cell on another sheet.
is it possible to hide a sheet automatically if i select the other sheet. For example, I have sheet1, sheet2 and sheet3, I'm at sheet2 and if i select the sheet1, sheet2 and sheet3 will automatically hides.
View 4 Replies View RelatedI want to put a range select statement to select a cell and count down 10 cells and copy.
View 9 Replies View RelatedI have a formula that looks at a cell on a front sheet, and then returns the contents of that cell as the result if it meets the criteria. So for example this formula would be in Cell A1 on Sheet2 IF(SHEET1!A1,"New",Sheet1!A1,"-")
This formula is always in the same cell (different sheet) as the cell that it is looking at, down 1500 rows. Instead of having the formula named for each cell is there anyway to ask excel to 'look at this cell but on this other sheet'.
e.g IF(Sheet1!"This Cell" etc). That way no matter what cell you put the formula in it is always referencing the correct cell for the formula?
I need to set a cell colour by the value of a cell in another sheet so i can't use conditional format. Is there a formula that allows the user to set a cell style, colour, font etc.
View 9 Replies View Relatedif I change the name of my sheet throught this code, how I can selected again? ...
View 9 Replies View RelatedI have a WorkBook with many sheets.
My first Sheet, say Sheet1 has on Column A, the names of the differents Sheets, eg:
A1: Country
A2: Spain
A3: Italy
A4: France
On my Second Column I need a Formula to get:
B2 = I need a formula to get me Spain!A15
B3 = I need a formula to get me Italy!A15
B4 = I need a formula to get me France!A15
But to do this ovbiously I would like to use the country names I can get from the Column A at present A2:A4 = Spain, Italy, France
I have this formula in cell C5 of a worksheet. =HLOOKUP(C2,OB_RB_MBRSHP_2002!$B$25:$M$35,11,FALSE). It is one of several formulas in a block of data referring to worksheets ending in 2002 (the year). Is it possible to take the OB_RB_MBRSHP_ part of the formula and use the 2002 part of the formula which is shown as the year in a C1l and create a formula without using the the OB_RB_MBRSHP_2002 in the actual cell? i.e. =concatenate("OB_RB_MBRSHP_2002",C1) in say c4
Then have a formula like this: =HLOOKUP(C2,C4!$B$25:$M$35,11,FALSE)
I know this method doesn't work because I have tried it, but I wondered if there was another way. My reason for asking is that this data is copied down on a regular basis and it would be far easier to change the year in one cell than to change all of the 2002 references to 2003 for example.
I havenamed my sheets with a date in the format of "mmm-yy".
I need a formula to bring this active sheet label (date) into a cell.
I have column A with various values in cells.
For instance, DG, GS, HG etc
I wanna do if a cell in column A is equal to DG then select the entire row that contains the cell. then call other sub.
I want to develop on the formula I have below to return the value in the same sheet as the formula from Cell AA2 if the result of the formula below returns #N/A
=IF(ISERROR(VLOOKUP(B2,Analyst!$J:$K,2,FALSE)), VLOOKUP(AP2,Analyst!$B:$C,2,0),VLOOKUP('IE STORES AGING 06-05-2014'!B2,Analyst!J:K,2,FALSE))
I found a post to name a sheet using the cell value with VBA but is there a "formula" in Excel that will do the same? I'm wanting only to use the first 10 letters of a cell.
View 9 Replies View RelatedAfter having searched for a while I can't seem to find any formula that will look at a cell for reference as to which sheet to go to and return a certain cell's contents.
For example,
in column A, I have the text "Sheet1" and in column B, I need the formula to return the contents of cell B15 from Sheet1, based upon column A.
I have Excel 2007, here's what i want to do. I've got a workbook created with several worksheets in it. I want to input the name of a project in say, cell A1 on sheet1. Then on sheet2 cell C4, I want the text I input into the afore mentioned cell to show. I know how do do this with numbers (using the autosum button), but I can't figure out how to do it with text, though it's probably very simple & I'll be embarassed when I get an answer. Following is EXACTLY what I'm trying to do if the above example is confusing.
I have a Project Cost sheet and on it I input (text) a description of a building to be constructed. The cells next to it will be the associated costs of that particular building. There will be several buildings listed on the sheet. On a Sales Figures Sheet in the same workbook, I want to list those same buildings and then their corresponding sales prices. I want these descriptions to be automatically pulled from the costs sheet, so that I only have to input the sales figures.