Retaining Mixed Cell References When Linking Worksheets
Jan 3, 2008
I am linking a row of data in worksheet x to a column of data in worksheet Y and would like to be able to drag down the cell reference in worksheet Y in this way:
=MIN(RuleofOriginData!AR$4:AR$63)
=MIN(RuleofOriginData!AS$4:AS$63)
=MIN(RuleofOriginData!AT$4:AT$63)
However, when I drag it down, the formula is automatically absolute for both the row and the column number instead of just the row as I indicated. Can anyone tell me why this occurs and whether there is a way to fix it?
View 9 Replies
ADVERTISEMENT
Feb 9, 2010
I am trying to use a macro to copy a set of cells.
I have successfuly created a macro - the problem is I would like the column reference to be absolute but the row reference to be relative. I can have one or the other but not mixed it seems.
If I have not explained myself well, perhaps this may help - I need to copy cells $J1:$S1 and paste them to $L1:$U1
Currently the macros works if the cursor is in cell J1 when I activate the macro but if I move the cursor to any other cell it copies and pastes the wrong set of cells.
View 9 Replies
View Related
Jun 17, 2006
I've read several threads about switching between relative, absolute, and
mixed references across several cells however these solutions seem to result
in formula with all relative or all absolute or all mixed.
I need to change the formula in lots of cells with a mix of types of
reference. e.g. I need to change "$E$4*AD$2" to "$E4*$AD$2" & would prefer
not to have to go though each of the cells with F4!
View 11 Replies
View Related
May 30, 2014
Would be a massive time saver but can't find anything here or on Google on how to do this.
View 1 Replies
View Related
Dec 1, 2008
I have a problem and wanted to know if there was an easier way to create links to various worksheets. I have a folder with 59 worksheets. The names of the Worksheets are the weeks of the year(example LA-01-01-09) I have another workbook with 13 worksheets one for every month and a sheet that totals the whole year up.
What I want to know is how can I create a link to everyone of those weekly sheets to go to my monthly sheets,so when data is entered into the weekly sheet the monthly sheet will update automatically? The weekly sheets are setup exactly the same. I'm looking only to pull row 3 columns c:d:f:g:h:i over to my monthly sheet.
I have an example of my weekly sheet and monthly sheet.
View 6 Replies
View Related
Oct 13, 2005
I want to pull data from about 100 different Excel files. They are located in
the same folder and drive and the data I want is in consistent cell
locations. I would to find a fast way to reference the files. something like
S:FolderJanuaryCaseNumber1.xls that will allow me to pull-down the
folumula and copy the month and case number from two separate fields on my
spreadsheet. So I would specify the month and case numbers and have them
automatically populate my link formula.
View 11 Replies
View Related
Mar 1, 2007
I compile a report each month. I have a program/macro built to do this so based on the month I select it runs the code below. The code below would run if i selected January. I run this report on a spreadhseet and its for a ton of people, and my code is really long. I know there is someway to shorten it, but i cant figure out how. Below is an example of one persons, and the next persons would begin on the row below the example.
Sub January_NB()
'Week1
'Christie
Sheets("New Business").Activate
With Sheets("New Business")
Range("B8").Select
ActiveCell.FormulaR1C1 = _
"='\lm-file-dfs-01centralPermktUnd-Prod-CtrsProperty Count SheetsNew Work ReportNB Team Count sheetsChristie[Count Sheet January Christie.xls]Tally'!R73C4"
Range("D8").Select
ActiveCell.FormulaR1C1 = _
"='\lm-file-dfs-01centralPermktUnd-Prod-CtrsProperty Count SheetsNew Work ReportNB Team Count sheetsChristie[Count Sheet January Christie.xls]Tally'!R6C4"
Range("E8").Select...........................
View 4 Replies
View Related
Nov 25, 2007
It is suppose to be that if the employee is "FT" and has worked >=4 years the return is 15. But if the employee is FT and has worked 2 years but less than 4 years then it is suppose to return 10 (these are days off) Or if the employee is FT and has worked 1 year, but less than 2 then it should return 5 days off. And all the others in the column get no days off.
I have tried to do it with structured references and with cell references I get a column of zeros!
View 9 Replies
View Related
Mar 10, 2014
I create the billing data for some key accounts and I was asked to build a summary sheet that brought all of them into one place so that the billing department didn't have to open each individual spreadsheet each month to verify their info to mine. What I would like to accomplish is to stay current according to today's date.
So if it's March, 10, 2014, I need to update the spreadsheet data according to the February 2014 spreadsheet a Key Account #1. Then in April, I need it to look for the new March 2014 spreadsheet. I'm assuming I can use an =IF(OR(... formula for this, but I'm just not sure how to get the date thing sorted out.
As far as naming of the the Key Accounts billing spreadsheets, they go something like 0214 "KeyAcct #1".xlsx. Then the new one will be 0314 "KeyAcct #1".xlsx. Even tho the next months billing file isn't created, the formula should be able to check against the date and know when to look for the new one.
View 1 Replies
View Related
Feb 2, 2012
I have a workbook. This workbook is divided into 4 worksheets. I want to add some of the calculations I made on other worksheets onto 1. I looked for the formula on how to do this, and this is the formula that was given:
=Sheet1!A1
I found the formula here: [URL] ........
Does it matter that the cell I'm trying to link from, in this case A1 on Sheet 1, is the result of a formula--a simple addition--and not a number, like 1?
View 5 Replies
View Related
May 9, 2012
I have a workbook with 2 worksheets. I know I can link 2 cells between worksheets, therefore if I change the contents in Cell A1 in Sheet 1, it will then show the updated value in Sheet 2 where I have entered that I want the value in Cell Sheet 2:Z1 is the same value as "Sheet 1! A1"
Is there any way to do this same process for an entire row? I would like to ensure that any change I make to a Row in Sheet 1, is automatically reflected in Sheet 2. I was hoping I could link the entire Row between the worksheets, not just the cells, which is more tedious.
The contents for each cell in the row varies: Text, Hyperlinks, etc.
View 2 Replies
View Related
Feb 21, 2013
I have two worksheets, work sheet A snap shot is
Rows labels
SUM
Catalyst
Credit
Hedge
Cash
A
10000
B
7333.22
C
-60000
Essentially what i am trying to do is link this to the other worksheet which breaks out if A is a Catalyst, Credit,Hedge, or Cash. If it is credit, then derive the the Value in the SUM Column for Credit.
TO explain better worksheet B looks like
A
Credit
B
HEdge
C
Catalyst
The Final product what I am aiming to achieve is
Rows labels
SUM
Catalyst
Credit
Hedge
Cash
A
10000
10000
B
7333.22
7333.22
C
-60000
-60000
How I can link this without having to manually do it every time.
View 1 Replies
View Related
Mar 12, 2009
i am trying to get data from my original (vertical data) to copy to other group of cells (horizontal) without having to refer to every single cell to be copied. is there a way to get excel to do something like this: if b12=x then c12=next - but for 5 rows.
i am such a excel newb but i have asked some guys who say they know excel and i left them scratching their heads. i have all the data in i need in other sheets.
View 9 Replies
View Related
Jul 7, 2006
I have three workbooks stored in the same drive(shared drive). Let's call them 1.xls, 2.xls and 3.xls. Each workbook has one sheet.Column A in all sheets is the same. I want to make the following linking:
-Column A of the sheets in 2.xls and 3.xls to be linked and get data from column A of the sheet in 1.xls.
-Column B of 1.xls to be linked and get data from column B of 2.xls and column C of 1.xls to be linked to column B of 3.xls.
So far it sounds easy. What I cannot find is what to do when I insert a row in the sheet in 1.xls and write something in column A. I managed from DATA- IMPORT EXTERNAL DATA to refresh column A of the sheets in 2.xls and 3.xls, so that these columns contain the updated information. I cannot do the vice-versa procedure: for example, to insert something in column B of the sheet in 2.xls and refresh the values of column B of the sheet in 1.xls. The fact is that when I insert a row in 1.xls the right references get lost and move one cell up. I want the references to remain stable. In a way the sheets are interdependent.
View 2 Replies
View Related
Feb 20, 2007
I am having trouble, with these formulas. I have 3 worksheets - Renewal, Upsell, and Crossell. Right now I have on Crossell the customer information cells = a designated cell on the Upsell sheet. This automatically polpulates the customer's info from the Upsell sheet to the Crossell sheet. What I want to do is have the customer's info populate to the "Crossell" sheet from BOTH the "Upsell" and "Renewal" sheet. That way, if you fill out the Upsell or the Renewal, it will always populate on the Crossell form.
View 2 Replies
View Related
Jan 20, 2009
Using Office 2003.
I have created two workbooks, one which is a master price list (MasterPriceList.xls) and another which is a pricing sheet template that will be used to calculate many different products (Pricing.xls)
I have used a VLOOKUP across the workbooks (thanks to those who helped me with that) but I cannot get a drop list to work across two worksheets. When I enter the source in the "refers to" box, I get a notice saying that you can't use data validation across two workbooks. However, this was the same error message I got when I was incorrectly inserting the reference source for the VLOOKUP function, so I don't believe it Also, there are several tutorials on the web that say this can be done. However, none of them seem to work for me.
I cannot open the Data Validation box and get to the other open worksheet, so I can highlight the area I want, with the range I want to drop down. Until I close the data validation box, I cannot get out of that sheet. I have used cell ranges as well as named ranges.
When I type in a name, I get only the text I entered in the source reference box appearing on the sheet, i.e., the drop down box will only show "=C:Documents And SettingsAllenMy Documents, etc" it doesn't seem to recognize it as a source.
View 10 Replies
View Related
Feb 18, 2012
I have a folder in Dropbox (important that it is stored there, it is shared with 15 people who are on a bunch of different networks) that contains 15 spreadsheets. One of them is the master, containing performance data for 14 people. The other 14 are for each individual person pulling their performance data from the master. Monthly, the master spreadsheet gets updated, and I want the user spreadsheets to update, so I can email the individual spreadsheets to each person so they can track their performance. Of course, they cannot see each others numbers, so they have no access to the folder. There are a handful of people that have access to the master. All the spreadsheets will live in this folder all the time.
The issue is that on my computer the path to the master is different then it is for other users because of where Dropbox stores local data by default, and for a lot of reasons, I cannot change it.
Is there any way to tell excel that the master (or the other 14) live in the same folder so there is no need for the first part of the path?
View 6 Replies
View Related
Mar 21, 2007
The spreadsheet I have designed is to be input offshore by one group, and the values that get put in this shared workbook will feed into another shared workbook (for reference and ease, I will call one offshore. book and the other uk.book).
It seems that I have to save the offshore.book (so could I use some autosave code, for every 5mins or so? I dont have the plugin so would have to write it in), then hit update links in the uk.book (again, I presume I could write this so it triggers off every 30mins or so, but can be done manually if required). It seems abit fiddly doing this, but this is the way they wanted it done (my idea was to put into a database, but got out voted).
The figures in the uk.book would simply be a vlookup. However, we do not want the uk team to be able to look at the other book so would the links still work properly? (ie there would only be one open workbook at a time - direct access to each others spreadsheets shouldnt occur.)
View 9 Replies
View Related
Dec 20, 2013
Column O is names and column P autogenerates email addresses with
=CONCATENATE(SUBSTITUTE(O2," ","."),"@company.com; ")
So, John Smith becomes
"john.smith@company.com; "
That's perfect. I want to move it to another worksheet. When I paste it to Worksheet 2, A1 I get
=CONCATENATE(SUBSTITUTE(Sheet1!O2," ","."),"@company.com; ")
So why does that only generate "@company.com; "?
View 4 Replies
View Related
Dec 12, 2006
I have 3 Sheets (Sheet1, Sheet2, Sheet3)
Sheet 1
---------
ClassID
Sheet 2
---------------
AccountID, Fname
Sheet 3
----------------
FundID, FName
I want to match the value from Sheet3!FundID to Sheet1!classID and Add Corrospinding Sheet3!Fname value to Sheet2(Fname). I tried using VLOOKUP, MATCH & INDEX but somehow not getting the correct formula.
View 5 Replies
View Related
Apr 24, 2014
I am creating a worksheet that uses a series of if formulas to determine the correct cell to use in a table outside the printing/viewing area. Something along the lines of "if X, Y, and z happens then use what is in cell AY34". I have the formula working, so I can get it to go pick up what is in the cell I want.
The problem I am having is that there is a heading to the text in cell AY34 that is bold, but the rest of the text in the cell is not bold. Is there any way to drag the formatting along with the data and not just the values?
View 3 Replies
View Related
Sep 24, 2011
I am trying to use an "IF" statement to either apply a formula or retain the last value of the cell, is there a way to accomplish this.
E.g. = IF( A1=B1, "retain last value", D1)
last value = the value of the cell prior either to A1 or B1 being changed.
View 6 Replies
View Related
Mar 8, 2008
I’m having problems linking data from various worksheets to one master worksheet, all in the same workbook. The workbook contains swimmers PB’s (personal best times) for each stroke and after each gala the swimmers new time is entered onto the worksheet. The worksheet data is then sorted so that the fastest swimmer is at the top.
The master worksheet contains all swimmers by name (alphabetically) and their best times for each stroke, what I am trying to do is have the master worksheet update automatically when I enter the swimmers new PB’s in the individual stroke worksheets, this works when I use ’Paste Special’, but when I go to sort the data with the fastest swimmer at the top, the data in the master worksheet does not reflect the correct times.
View 9 Replies
View Related
Mar 30, 2009
Using Excel 2003,
I'm trying to come up with a formula that will take the students Total Pct. (column G, G12 is first cell & G41 is the last) from the worksheet called Juniors and find their Total grade (Column H, H12 is first & H41 is last) from the worksheet called Total Days. The % column on the Total Days worksheet is A (cells A10:A22) and the Grade column is B (cells B10:B22)
Juniors worksheet
Tot. Pct.
Tot.Grade
119.5%68.3%90.2%80.6%78.0%70.7%95.1%165.2%95.1%200.0%
Total Days worksheet:
%Grade99A+93A91A-89B+83B81B-79C+73C71C-69D+63D61D-60F
View 9 Replies
View Related
Apr 21, 2009
I have a workbook with two worksheets.
Worksheet one is "Company ID" info, in it are the columns:"Company Name", "street add", "city", "state".
Worksheet two is "Company Contact Person" in it are the columns: a reference to worksheet one column one "Company Name", and column two is 'Contact Person name'.
I want to be able to sort worksheet one at will, By city, or state, or any of the various columns. When sorting worksheet one I want the relationships in worksheet to to remain intact.
"Company Name" - "Company Contact Person".
View 6 Replies
View Related
Jul 27, 2014
I have a number of separate worksheets in one spreadsheet all based on the same list (eg customers); the customer's name is the first column and hence the 'key' in each worksheet; when I insert or delete a line in the main worksheet the formulae in the first (customer name) column are amended in the other worksheets BUT I ideally need more than that; when I insert a new customer in the first (main worksheet) I need a new line with that customer name inserting into the corresponding place in the other worksheets; and when I move a line (eg delete a customer and move them to the bottom of the main worksheet list) I need the corresponding lines in the other worksheets moving as well. I guess what I really need is a drill-down function; a main customer list and sub-lists all linking back to the main lists like you would get in database tables.
View 5 Replies
View Related
Nov 28, 2008
I have a template A that has lines on it, sometimes I want to overlay template B which has other lines on it, however when I past template B on top of A the formats of A are overridden by the formats of template B.
Is there any way of "overlaying" B on top of template A without losing the template A formats?
View 7 Replies
View Related
Aug 4, 2009
I have a workbook with a main worksheet where all the data entry and calculations are made and then I have "sub" worksheets which mirror subsets (specific columns) of the information from the main worksheet. When information in the main worksheet changes, or rows/columns are added, I want the sub worksheets to update automatically.
The only way I can figure out to do this is to use absolute references in the sub worksheets. Does anyone know how to change the references to be absolute (using the F4 function) without having to do them one by one? Or can anyone suggest how I can achieve this in another way.
View 3 Replies
View Related
Aug 23, 2012
I'm working on an excel report that handles reporting for a large number of people. Another person at work is doing the VBZ coding, and I'm doing the formulas that aggregates the data. which has led to an interesting problem whenever I use a vlookup. I copied the sheets with the formulas from the test document I had been working on to an updated version. And all the vlookups automatically adjusted themselves to reference the document they had been created in. I had also moved over the tabs they were supposed to reference, but they keep looking at the old document. I spent an hour removing the references last night, but I don't want to do it again when I get the final product.
How to get excel to copy a formula EXACTLY as written, and not att in the name of the original document?
It should say
=VLOOKUP($A438,totals!$A$1:$AH$63,32,FALSE)
and instead it says
=VLOOKUP($A438,'[reporting formulas.xlsx]totals'!$A$1:$AH$63,32,FALSE)
View 2 Replies
View Related
Aug 27, 2013
I have a workbook that has multiple tabs with data already entered. I have also built a series of tables/forumlas to display summaries of the data. My desire is to be able to copy this table with all formulas/formatting/cell sizes to the various existing tabs.
View 3 Replies
View Related
