Reference Cell From Another Excel Worksheet?
Dec 6, 2013
I've done this loads of times before but doing it this morning seems to not be working at all.
Previously I'd be in Sheet 1, I put the equals sign, I click into Sheet 2 in the cell I want, hit enter, and hey presto the external cell is referenced in Sheet 1.
However, what I'm trying to do here is not reference individual sheets within an excel document, but reference a totally separate Excel document. Can I do this or not?
If so how? Because when I select the second worksheet after I've entered the = sign, it just takes me to the second sheet - it doesn't seem to 'remember' that I'm in the middle of a formula.
View 4 Replies
ADVERTISEMENT
Dec 18, 2013
I am trying to create a budgeting spreadsheet that contains 26 worksheets that represent 2 week expense cycles. The worksheet will tally all the expenses in its 2 week cycle, compare them to a target budget, and calculate the remaining budget. I would like to roll the remaining budget over to the next 2 week budget cycle by having a cell refer to the remaining budget in the previous worksheet.
I am trying to accomplish this using the following VBA script. I get an error (Run-time error 1004: Application-defined or object-defined error) on the 3rd to last line of the code. I think the issue is with how I am constructing the wsReference variable. I have seen other codes do something similar (VBA - How to refer to a different worksheet cell) but I am not having any luck.
Sub Budget_Rollover()
Dim i As Long
' Loop through all the worksheets, starting with the second sheet
For i = 2 To Worksheets.Count
Dim wsReference As String
Dim wsName As String
' Define the name of the previous expense cycle worksheet
[code]......
View 2 Replies
View Related
Nov 28, 2013
I've got 2 worksheets in the same workbook.
Sheet 1 contains huge amounts of data - thousands of rows and multiple columns
Sheet 2 - I want to extract the data from sheet 1 column A into sheet 2 column A but only the data from every 21st row.
I want to be able to copy the formula automatically down, otherwise it will take hours to do it manually. So far I can only get the cell reference to increment by 1 each time after copying.
So what I'm trying to achieve is :-
Sheet 2 A1 = Sheet 1 A1
Sheet 2 A2 = Sheet 1 A22
Sheet 2 A3 = Sheet 1 A43
View 3 Replies
View Related
Oct 25, 2012
I have the need to email the current worksheet in Excel 2007, which I have been able to do with the following ...
Sub SendTab()
'Declare and initialize your variables, and turn off screen updating.
Dim wks As Worksheet
Application.ScreenUpdating = False
Set wks = ActiveSheet
[Code] .......
Is there a way I can also get it to also rename the sheet from the default "Book1" to the information in a referenced cell.
View 7 Replies
View Related
Jul 20, 2014
I have a workbook with 1000+ worksheets, all of which have 3-letter names. On a master sheet, I would like to make a query of how many non-empty cells there are on a subsidiary worksheet. This works:
Code:
=COUNTA(ABC!A:A)
What I'd like to do from time to time is input in column A a varying set of 3-letter worksheet names, say
AAB
ABC
CDE
And have a formula in column B that converts this to
=COUNTA(AAB!A:A)
=COUNTA(ABC!A:A)
=COUNTA(CDE!A:A)
I've learned that simply substituting the cell references A1, A2, A3 for AAB, ABC and CDE doesn't work. What do I need to do to achieve this?
View 2 Replies
View Related
Aug 26, 2010
Here is my situation:
I created one worksheet called: Assumptions. In this worksheet I have 6 different columns representing 6 different cases.
I have completed the first case in a different worksheet called: WL. I would like to copy this case/worksheet WL and be able to change all the reference cells used in case#1: WL to case#2: FA. So all the cells used in my Assumptions worksheet were in the B columns for case 1: WL, for case 2: FA they will all be in the C column, same row, different column.
Any quick way to do the changes?
My case #1 worksheet as over 10 000 formulas, I really don't want to change each and every formula.
I was thinking there was a way to highlight ALL the reference cells used in my Assumption worksheet, so I would have had to drag from B to C cells, only 50 cells. But I can't find it.
View 12 Replies
View Related
Jul 4, 2014
I am completely new to Macro's and VB and the macro below has been generated using the "Record Macro" function in Excel 2013 with a couple of very minor modifications based on some research I have done (hyperlink & input box). My ultimate goal is to make a copy of my "TEMPLATE", which is hidden and the copy could have a variety of names, then create an entry in my "SUMMARY" table that references cells on the newly created sheet. The new entry on the SUMMARY page should be entered in the next available row ... at the moment I need to make sure I have my cursor in the right place before I run the macro. I also want the first cell in the new "SUMMARY" row to create a hyperlink to the newly created worksheet.
The macro does what I need it to do, as long as I name the new sheet "Test", what I would like is for the Macro to recognise the name of the new worksheet and create links to that name. The rows and columns in each new sheet will remain the same, hence the R##C## part will always work.
The "SUMMARY" and "TEMPLATE" worksheet names will not (ever) change.
View 4 Replies
View Related
Aug 5, 2014
I have a worksheet with additional worksheet name listed in Column G, starting in G3.
I want to run this formula on each sheet, using the value in Column G as the WORKSHEETNAME.
=COUNTIFS(A7:A1000,">12/31/2010",A7:A1000,"<1/1/2012",F7:F1000,"Excluded")
Faulty example of what I'm trying to do:
=COUNTIFS('WORKSHEETNAME'!A7:A1000,">12/31/2010",''WORKSHEETNAME'!A7:A1000,"<1/1/2012",''WORKSHEETNAME'!F7:F1000,"Excluded")
I've tried INDIRECT, but I cant seem to make it work.
View 4 Replies
View Related
Mar 5, 2012
I have created a macro in excel 2010 which enable the file to save (extract) data into separate location and name. The vba code for macro is as follows: Question: How can I save this workbook with reference to the value containing in cell B2? (it is named temporary now - as defined in the code)
Sub aaa()
'
' aaa Macro
'
[Code].....
View 1 Replies
View Related
Apr 10, 2014
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.
View 6 Replies
View Related
Jan 7, 2014
Been having some trouble with this using VBA. I can't seem to get the code to run properly.
Actually, if there is a way to get a right footer on one worksheet to be referenced in a cell on another, that would be even better but I haven't been able to find anything like that.
View 2 Replies
View Related
Jul 15, 2014
I need a VBA code to reference the cell value to activate a particular worksheet name. In the sheet "Ini", Range ("B4"), there is a text of "Rawdata" This text is used to reference to the specific Sheet name and activate it.
However, I am unable to get my code to work. Thus, I changed it with a ' with green highlights for the following lines:
'Worksheets.Open Filename:=DataRawSheet
'ActiveSheet.Range("A1").Select
Reference_Cell_to_Sheet.xlsm
View 4 Replies
View Related
Aug 12, 2006
I have recently learned a little about Excel. Enough to be able to do our payroll for my company on it. My question is: On the 1st page of my workbook, is my quarterly report which shows all taxes, social security, and medicare we hold out each payday for each employee. The rest of the worksheets are for each employee. How do I have each employees withholdings automatically go to my quarterly report page?
View 5 Replies
View Related
Feb 27, 2007
I'm trying to reference a cell on another worksheet in my criteria and the formula doesn't seem to be working. All I get is a value of 0.
= SUMIF(G3:G76, ">='City Wide'!B6",I3:I76)
If I replace 'City Wide'!B6 with an actual number the formula works. This does not help as 'City Wide'!B6 is a value that can change.
View 4 Replies
View Related
Jan 4, 2010
I am trying to open a stored file on a network drive (with the file location stored in cell E4). When I try to run the macro, I get an error message on Line 3. (Sheets(E4.Value).Activate)
Does anyone know what I can do to fix this, or have a better method to open the closed files?
I've read that it's possible to reference information from a closed sheet, but I don't think that I'm experienced enough in VBA to take on that effort yet.
View 7 Replies
View Related
Jun 14, 2006
I've been tasked with redoing a revenue report at work, and in my head I've conjured up what could potentially be a huge time saving way of doing things in the future, although have searched help files and looked around online and can't find the formula/macro (if it exists) to enable me to do this.
The problem is this:
Sheet 1 of the workbook is a large input sheet. Every row contained in that sheet will, in column A, be titled 'red', 'yellow' or 'green'. Columns B onwards contain other data which (at the moment) is irrelevant to the problem.
Now what I want excel to do after I've put the raw data into the input sheet (sheet 1), is to read the text in column A for each row, then automatically copy ALL data in that row over to the next empty row on another worksheet.
ie Sheet 2 will have all rows that have 'Red' in column A on the input sheet, Sheet 3 will have all those titled 'Yellow' and Sheet 4 will have all those titled 'Red'.
I know I can use a filter on the input sheet to just show the data I want, but each colour coded row will contain different data to another, and if there is a formula/macro setup i can use to do the above, then I can set the subsequent worksheets up to hide the superflous columns from the input sheet.
View 9 Replies
View Related
Apr 11, 2008
I've got 3 sheets, A, B, and C. In sheet C, I have a cell named "element". That cell can have either the letter A or the letter B in it. In my VB code, I want to reference the sheet which correlates with the "element" cell. So,
Worksheets("element").Select
when the user clicks my "go" button, it calls this logic, and I want the worksheet that correleates to the letter in the cell in worksheet C - to open.
The reason for my posting - this is not working, and may not even be possible. Perhaps someone has experience in this and knows how to do it - or knows a different way to do this.
View 4 Replies
View Related
May 27, 2014
I am trying to find a way to automatically highlight all the cells specified by the cell references in a column on another worksheet (which will constantly have new values added).
On the attached sample, the 'Data Before' tab shows the base data before any highlighting. As cell references are added to the 'References' tab in 'column A' I want the respective cell to be highlighted on the 'Data Before' tab resulting in the 'Data After' tab and each time a new cell reference is added to the 'References' tab, the respective cell is then highlighted on the 'Data Before' tab.
I would like to have control over the highlight colour so that a different highlight colour can be specified for each date change, i.e. all cell references that are shown on the same date are the same colour but where the date changes a different highlight colour can be specified.
Highlight by Cell Reference.xlsx
View 9 Replies
View Related
Jan 24, 2012
Trying to use INDIRECT to sum the contents of a column on another worksheet upto a certain cell reference which is in another cell on the worksheet.
=SUM(INDIRECT("Sheet1!A4:Sheet1!"&B1))
I have taken it back to simply having sheet1 with numbers in A4 to A23, then sheet2 with A23 in cell B1, and the formula above it C9, but I keep getting #REF!.
The formula works fine if on sheet1 without the worksheet names in it. Formula evaluation gets to =SUM(INDIRECT("Sheet1!A4:Sheet1!A23")) then gives =SUM(#REF!)
View 6 Replies
View Related
Feb 12, 2009
I have a macros ( code inserted ) as you can see in my code the macros save the workbook in any file name you chose just by changing the any filename option.
I would like this to be changed so that it saves based on a cell reference, say i has a name in lets say B10 i would like it to save as the name in B10.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayAlerts = False
Dim bk1 As Workbook
Dim bk As Workbook
Dim myfilename As String
View 9 Replies
View Related
Sep 26, 2013
This is a continuation of thread 182629 [URL] ....
The End User wanted originally to type characters into cell C1 of a worksheet that the macro searched for in another worksheet, copying across every row containing the input.
The responses gave me the following code which worked perfectly:
VB:
With Sheets("PICKLIST")
myFind = "*" & .[c1].Value & "*"
End With
If myFind = "*" & "" & "*" Then Exit Sub
Unfortunately, the user NOW wants a Text Box instead of putting the characters into a cell.
I added a textbox (originally called 'TextBox2' but renamed "Choice") and "Dimed" CHOICE As Range.
Then tried the following code:
VB:
With Sheets("PICKLIST")
myFind = "*" & CHOICE & "*"
End With
But it generates a "RunTime Error 91" - Object variable or With block variable not set.
View 8 Replies
View Related
Dec 28, 2013
If C5 refers to the value in cell C100, C6 refers to the value in C110 ten rows below. C7 refers to C120, ten rows below that and so on. Is there a formula that you use within excel (not a macro) that allows me to copy the C7 formula to C8 so that C8 will reference C130?
I have tried using INDIRECT and OFFSET formulas but cannot do it without an absolute reference to a fixed cell, which defeats the purpose, since I can go into C8 and manually change it to =C130, C9 to =C140 etc.
Currently using Excel 2007 at work and Mac Pro at home. Thought about R1C1 reference, but don't even know how to change to that style on my Mac.
View 9 Replies
View Related
Feb 14, 2012
I am fairly new to macros and have trouble with VBA. I have a file with multiple worksheets. Each worksheet contains the name of a specific location in cell A8. I want this name in cell A8 to be the name on the worksheet tab for each worksheet in my file but do not know how to accomplish this. Is that even possible?
View 3 Replies
View Related
Aug 11, 2013
How to link a particular cell to many pages in excel. In my when i drag the pages, Rollno are not coming sequentially.
Here is my worksheet (Result.xls)
View 5 Replies
View Related
Jun 4, 2014
Excel 2010. When the result of the formula in Col. E is less than zero, I need to move the row to another sheet, and then delete the row.
I modified another poster's script as follows:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
' Move rows with negative value in E to Flagged Items sheet
Dim rng As Range
' Set Target Range
Set rng = Target.Parent.Range("E2:E200")
[Code] ........
The script works perfectly if a negative value is manually entered into a cell in Col. E, but not when the result of the formula produces a negative number.
I can't find a way to make the Case Is < 0 act on the resultant value rather than the formula.
Is this possible in the script or is there another way to achieve the desired result?
View 4 Replies
View Related
Feb 19, 2010
i have two excel files, with many data inside
i need a function that do this:
in the cell A1 i want to visualize the cell A1 of second file
in the cell A2 i want to visualize the cell A13 of second file
in the cell A3 i want to visualize the cell A25 of second file
i explain: in the first file i need a column that in each line show the first cell + 12, then +24, then +36.
file 1 is 1.xls
file 2 is 2.xls
for example A1 have the value 1
if in 1.xls cell A1 I insert =[2.xls]worksheet1!A1 and in A2 =[2.xls]worksheet1!A1+12 in A2 i obtain the value of A1+12=13, but i need to obtain the valour of A1+12=value of A13.thanks
View 10 Replies
View Related
Feb 4, 2014
I have encountered some difficulty in modifying a macro I wrote into what I need. I created a macro that searches a column (Column C) for a cell value of, "stop", and then it copies everything above that cell and pastes it onto another sheet. In the sample data set that I was using, "stop" first occurred in cell C541, so the macro copies C1:C540 and pastes it onto another sheet. The problem is that the macro created an absolute reference to C540. What I desire is for the macro to use the 'Find' function to locate the first occurrence of, "stop", offset one cell above that cell, and then reference the active cell (which was positioned by these last two steps) in the range that should be copied. Basically, I'm hoping to have cells C1 through the active cell copied and then pasted onto another sheet.
Code below.
Sub FAIL()
'
' FAIL Macro
'
'
Sheets("Reformatted").Select
Columns("C:C").Select
Selection.Find(What:="stop", After:=ActiveCell, LookIn:=xlValues, LookAt _
[Code] .......
View 4 Replies
View Related
Jul 24, 2013
The spreadsheet works in Excel 2000 when you open it in any newer version it is corrupted. Which is fine so we are rewriting it in Excel 2010/2013.
We have data in columns A:E which comes from the refreshable query. Data in F:I are different formulas based on the data from the query. The number of rows we get will vary from query to query. The headers never change just the data. How can we make it so that when no matter the data length the formulas will always autofill up or down depending on the data length?
View 9 Replies
View Related
Mar 21, 2014
I have done a Scenario and they list the cell address (example R2C35) in the Report - the cell has the text "R2C35" in it.
I want to get the contents of a cell 2 columns to the left of Cell R2C35.
So what would I use for the Reference Cell in the Offset formula?
View 8 Replies
View Related
Aug 1, 2013
I'm working on a project that has a master workbook and multiple files that link to the master. The master workbook calculates values based off of a ton of information: account info, pricing info, quantity info, etc. It is a pretty massive excel file, but that is not where the problem arises.
The files that I am trying to link contain relevant information for specific accounts, including prices. The cells that contain product prices are linked to the master workbook. Example: Destination file, lets call this "Company A", Source file, lets call this "Master". In Company A's spreadsheet there is a column that contains pricing specific to that company. This pricing comes from the Master. Most of the pricing is in column C. What I have been doing is copying the relevant cell in the master and "Past Special, Paste Link" in Company A. I do this for every product in Company A's worksheet.
The goal of this is to automate pricing, so that when there are price changes or account changes, one would only have to update the master and all the separate account worksheets would populate with the correct prices. The method I've been using was working perfectly, until I had to edit the master file. I had to insert and delete a few rows from the master file. I work in excel quite often and link cells alot, so I figured that the linked cells would update to the new format, but they aren't. Ex. If a cell in Company A's worksheet is referencing F46 in the master and I delete row 44, I would like the cell to now reference F45, because that is the cell with the correct information. But instead Company A's worksheet isn't adjusting for the deleted row and is still referencing F46.
The only solutions I have found for this issue are to either have all the linked files open when I am editing the master. But seeing as there are almost 25 files, that doesn't seem very practical. The other solution is to never insert or delete rows, just to add on to the end of the master spreadsheet.
View 5 Replies
View Related
