Is it possible to link a cell e.g. A10 that has a time value in it (there is a formula in this cell that puts in the time value) to another cell in a range of cells e.g. B1:B1000 that has the same time value?
I know someone out there knows how to do this.
I have two worksheets, one with detail monthly information and one with YTD information. So let's say the three numbers I want to capture in the YTD sheet are in columns B, G and I on the monthly sheet. January's data might be in B5, G5 and I5. February's data is in B12, G12 and I12 and so on.
On the YTD sheet in cell C2 I link to Monthly!B5 and in C3 I link to Monthly!B12 so cells C2 to C13 on the YTD sheet show the monthly totals from column B on the detail sheet. On the YTD sheet, cells C20 to C31 show the monthly totals from column G on the monthly sheet, so cells G5, G12, etc. And finally, cells C40 to C51 on the YTD sheet show monthly totals form column I on the monthly sheet.
In the past I've always created all these links manually. After creating the links in C2 to C13 on the YTD sheet, is there a way to use a formula in C21 that uses the link in C2 to create a link for G5?
if it is possible to link the name of a tab to a cell in its corresponding worksheet.
View 9 Replies View RelatedIs there a way to link a cell to the next worksheet in line and not to a specific worksheet?
I have a summary worksheet that needs revolving values, based on the 5 most current worksheets (all of which are identical).
I have a workbook with daily sales information for each week in a period (4,5 or 6 weeks) depending on the fiscal year calendar. I have a dashboard to select the weekending date in cell D7. I used mscal 7.exe to embed the calendar. I need the date to remain the same, but want the calendar to show the current date. Cell H5 shows the current period;i.e. 1,2,3,4 etc. Cell H7 shows the week number of the period (1,2,3,4,5 and 6 for December).
I need vba code to showthe appropriate week's daily sales worksheet based on the info in cell H7 on the dashboard. The week number on the dashboard page is obtained fromthe calendar worksheet with a vlookup formula. These sheets are named "week 1, week 2, week 3, week 4, week 5 and week 6". I need to assign this code to a shape with the caption Create New Week.
I have built a userform and want the text boxes to access information from a worksheet when I click on the button that displays the userform.
View 3 Replies View Relatedsetting up a worksheet that forces a link to another worksheet when text is entered.
For instance, I will have several columns in the first worksheet (Sheet 1). For each entry, a tally ("X") will be added under the applicable columns. Most of the column headings are pretty straightforward. To keep things uncluttered, I want one of the columns to have an auto-link (?) feature so that when the user adds an X in this one column (we'll call it Column D), the user will then be auto-linked to another worksheet (Sheet 2) in the workbook. Sheet 2 will have room for more information regarding Column D, Sheet 1.
I'm creating a spreadsheet which will have a grid of hundreds of check boxes (used to record progress on a project). In order to use the value of the check box in calculations, I'm linking each checkbox with another cell. So, if the checkbox is in cell F10, then i might link it with cell AF10, which is where the TRUE and FALSE indicators are.
If I copy the last row (which has checkboxes--let's call it row 99), and copy it below to expand the grid, the check boxes in the new row are still linked with row 99 cells (i.e., all the new check boxes in column F are still linked with AF99). So, presently I'm having to go through and manually re-link each pasted check box to a new cell.
Is there a way, using something along the lines of the INDIRECT function, where I can have a formula dictating what cell each checkbox is linked with, so that when I copy and paste a large number of these, they automatically point to the new cell (such that, F99's check box points to AF99, but if I copy this F99 check box into cell F115, then it would automatically be linked with cell AF115)?
Can cell comments be returned together with the results of Index / Match function. If not is there any way to link the coments ?
View 3 Replies View RelatedIf I have a two files called test1.xls and test2.xls for example. Is it possible to link test1.xls to test2.xls by putting the filename in a cell?
i.e. In test2 put the filename in say A1 and in the link put the path and instead of naming the file you want to link to put in A1 instead.
I know it doesn't work if you do it that way I was just wondering if there was a way to do it.
I have a problem that may be a little bit diff. from must of the Readonly post that I have found here. Most people would like to have all of there files opened as readonly files, but I don't in this case. I am copying a number of files that has VBA code in just about all of them. I am using a CD writer to copy the files to a CD using Windows Explorer and that is working just great, No problem copying the files.
My problems comes in when I copy the files from the CD to another computer, all of the files are Readonly files on the new PC. I have not saved them as readonly and don't want them to be readonly files. Does anyone have any ideas why this is happening what I need to check or change to get the files to another computer without being a readonly file.
I have an excel that has dropdown column from form control, in F4 there is a formula of vlookup that take the range from A2 to B20 in consideration but the reference cell that is given to vlookup formula to pick corresponding value is E4 which is also the reference cell of dropdown box and therefore its not showing any name rather than a value, problem with the formula is that when we select any name from dropdown it does not respond to any value in F4 despite changing values in E4.
I am using these setting for the first time.
Image & excel file is attached
drop down.png
I have a worksheet. In sheet3 The value of column L2 is the value of column M2 in sheet . Everytime I copy the new sheet, I have to re type the formula for this new sheet. Is there a way to give formual such that when I copy new worksheet the formula will come automatically as it always has to link from the M2 column of the previous sheet?
View 9 Replies View RelatedI have a column who's content is determined via about 6 nested if statements from data on that row. That cell is then conditionally formatted to a certain color based on the text that is ultimately printed from the nested ifs (simply an extra visual legend for the text). All of this contributing info and about 1000 lines items make a very large and difficult to print page. What I am trying to do is a make a summary sheet that simply takes the index number of these 1000 rows and copies or links the conditional formatting of the mentioned cell onto this number on another sheet. I have already linked this status cell and put it adjacent to the index number which works well in that with two columns I can show the index and status but if I could combine the color of the status cell onto the index cell, it would be even better. I am pretty sure another conditional format for this summary sheet would not be possible or be extremely complex since the contents of the index cell I am conditionally formatting have no bearing on the conditions for the format. Was hoping there is some VBA magic that could simply mimic the conditional formatting from one cell and put it on another.
View 1 Replies View RelatedI have a macro that inputs a sheet and cell from the user. when the macro tries to make a formula it outputs =data!'AE3'. I need it to output =data!AE3
Dim Sheet As String
Sheet = InputBox("Take Data from where?")
Dim startcell As String
startcell = InputBox("Start Cell?")
ActiveCell.FormulaR1C1 = "=" & Sheet & "!" & startcell & ""
I have a formula to extract the worksheet name and add the "!" to it so it can be used in a copied formula... I'm just having trouble writing the formula to reference the text in the cell with the worksheet name. If it's easier, I also have the list of worksheets without the "!" - e.g., Sheet1, Sheet2, Sheet3, etc...
I want the formula to calculate, in my example, the sum of 2 cells on a different worksheet.
I am trying to use the Indirect function to use the value in a cell to select a sheet with the same name as the value of said cell. I have looked at several posts and attempted multiple methods. I still get an error. I cannot find a thread describing exactly what I am trying to do, which is:
reference a cell value to direct the formula to the correct sheet to then complete an array Sumproduct formula.
Since this description barely makes sense to me reading it, I have attached an example. The problem is in cell C7. Basically, I want cell c7 to look at cell c1 and then go to the tab with the matching name as cell c1 and complete the calcuation.
Is there a formula I can use to return a worksheet name. I want cell A1 on Sheet1 to = the name of Sheet2
View 4 Replies View RelatedOne of my macros is a tied to a worksheet_change. The macro runs and gives me the result that i want, however excel flips to the sheet that the sub is running for.
example:
Sheet1, Change the cell
excel flips the visible to sheet2
macro runs
Its kinda of annoying. I would like excel to stay on sheet1 where i am trying to finish data entry. I suspect it is how i have writen the macro. Any help at all on how to clean it up.. or a better way to write the same thing would be very appreciated.
Sub TermPlacement()
' Term_Placement Macro
' Uses the Value of Term to insert a formula repeatedly in a colum.
Dim sNFormula As String
Dim rTerm As Range
Dim rStart_Cell As Range
Dim rNpayment As Range
'The reference cell located directly above the first cell in column
Set rStart_Cell = Worksheets("Floor Plan").Range("$B$13")
'Additional set values....................
For example, Cell A1 = July 2009 Cell B1 = July 2009 MTD. how to change 'July 2009' in cell B1 automatically in respond to the changes in cell A1.
View 5 Replies View RelatedI am working on a file with multiple tabs. I have an output sheet which brings values from different tabs on to the output.
I am bringing the values using a simple VLOOKUP.
What I would like is to bring to the output sheet not only the value from the inner sheets, but also the cell color.
The cells are not colored using any logic or using conditional formatting, otherwise i would use the same logic or put the same conditional formatting for the output. The cells are colored by the users arbitrarily.
For Example, please refer the sample file. In this file, when I select Company A, the values are from the tab Company A, but the cell colors should also be the same as in COmpany A. When i select Company B, along with values, the cell colors should also change as per what is on the tab company B. Is this possible?
I want to create a summary page with the tab name and a link to the contents in a specific cell. In the code below, I can get the sheet I want but in the second column, I would like to link to the data instead of getting the data in the cell. Can I do this with a simple modification to this
Dim a As Long
Sheets.Add.Name = "Notes"
For a = 1 To Sheets.Count
Worksheets("Notes").Cells(a, 1) = Worksheets(a).Name
'The next line gets me data, but I want to link to this cell in all worksheets
Worksheets("Notes").Cells(a, 2) = Worksheets(a).Cells(15, 18)
Next a
I'm hoping to hover my mouse over a cell to display text as popup. This text is linked to a cell and is dynamic.
I'm only wanting this feature to display info (in the popup) not link to a website
Currently I've dodged my way around the issue by using a hyperlink for the sole purpose of taking advantage of its hover popup feature.
The problem is that a hyperlink popup is limited to 255 characters so i can't display much text.
Is there is any VBA code or add-in's that take advantage of the mouse cursor position to display a text popup?
Kind of like the Comment feature but for dynamic use.
The content of cell "animal!A1" will change according to a simple vlookup table. Let's say the value can be "dog", "cat", or "horse". In cell "animal!A5", I want to duplicate the content either "dog!A5", "cat!A5", or "horse!A5", depending on the current value of "animal!A1".
I've tried to do a simple reference like:
="A1"!A5
or
=A1!A5
wanting the A1 to actually read either dog, cat, or horse so the reference would refer to the worksheet of the same names. This doesn't work, so I need to know if there is a way to do this.
I'd like to display a formula contained in a cell within the worksheet. How can I do that?
View 4 Replies View RelatedThe formula in cell A1 of the attached inserts the worksheet name, but it doesn't work when pasted to a group of worksheets. The formula doesn't change from sheet to sheet, yet the results refer to the workheet name where the "paste" was originally done. Does anyone know of a similar formula that would work right when applied once across a group of worksheets?
View 2 Replies View RelatedAttached file 140515 Matchsheet.xlsx has formula that finds costs matching two criteria.
But the formula depends on me typing in the specific Worksheet Name: So to find the cost for 'Type 1' products, I enter:
=IFERROR(INDEX('Type 1'!$E$5:$F$11,MATCH($C$4,'Type 1'!$C$5:$C$11,0),MATCH($D$4,'Type 1'!$E$4:$F$4,0)),"")
I want the formula to "see" the sheet name as whatever is in a specific cell.
E.g. Cell B4 says "Type 2". The formula will be:
=IFERROR(INDEX('[WHATEVER NAME IS IN B4]'!$E$5:$F$11,MATCH($C$4,'[WHATEVER NAME IS IN B4]'!$C$5:$C$11,0),MATCH($D$4,'[WHATEVER NAME IS IN B4]'!$E$4:$F$4,0)),"")
How to create a search formula that will look up a person's name in a given worksheet in a specified cell?
View 8 Replies View RelatedI am trying to apply named cell from another worksheet to existing formula.
For example I have a formula in sheet2 which looks like =sheet1!A1+sheet1!B1. I then subsequently name sheet1!A1 = Jan and sheet1!B1 = Feb. Thinking that if I apply these names the formula in sheet2 would look like =Jan+Feb. However I get the message "Microsoft Office Excel cannot find any references to replace" when I try to apply the names using Excel menu "Insert - Name - Apply".
I need to hide/unhide a couple of rows based on the result of a formula in the Target Range. Basically, Cell D2 contains the results of a sum (a+ B), if this is greater than 10,000, unhide the next row.