Can't Reference Cells Across Worksheets

Apr 3, 2007

I've got an error whereby if i open a new workbook and enter the formula "=Sheet2!A1" in cell A1 on sheet1, Excel throws up a circular reference error. If i then try to select sheet2, excel highlights both sheets 1 and sheet 2 (i am not clicking incorrectly here).

Excel then thinks that i've entered the formula into the second worksheet - it dissapears from the 1st and the circular reference makes sense. In other words, it appears that i'm not actually entering a forumla on the hightlighted worksheet.

This was happening in Excel 2003 so i updated to 2007. I'm guessing this is something to do with an incompatibilty with another application but i can't work out what i might have installed. Does anyone have any idea of apps which might cause this type of error?

View 9 Replies


ADVERTISEMENT

Cell Reference- Able To Reference Two Cells To The Left Even As More Cells Are Added

Jun 11, 2008

I have 5 columns set up: A,B,C,D,E
D is the sum of A and B
E is the sum of A,B,and C

As I add in a new column to the right of C (call it C2), I need D (which has shifted over one) to sum A,B, and C.

I also need E (which has also shifted over one) to sum A,B,C, and C2.

Essentially I need a function in a cell that will be able to reference two cells to the left even as more cells are added.

View 9 Replies View Related

Cell Reference To Other Worksheets

Jan 31, 2009

I basically have the following in my 1st sheet ( [indicates cells] )

[3][10]@[65] - which in laymans terms would mean 3 sets x 10 reps @ 65 percent of your 1 rep maximum.

Moving on, what I would like, is to get cell [3] to reference to 3 cells (without the number 3 showing up) on the 2nd sheet that has the workout program on it.. And adjacently to those cells, I would like [10] to be displayed as the number of reps and [65] to be referenced as the intensity as well.

So in the end it looks like this on the other sheet:

[65%] [10]
[65%] [10]
[65%] [10]

How would I go about doing that? If you don't know the exact answer but could point me in the right direction that would be great!

#2

Is it possible to get a cell in the 2nd sheet to reference 5 merged cells (containing text) in the 1st sheet?

Basically, all I want to do is get the name of an exercise on the 2nd sheet to say the same thing that it does on the 1st sheet. The only problem is that the name of the exercise on the 1st sheet is contained within merged cells.

While I am pretty sure I could break-up the merged cells and just type the name of the exercise in 1 single cell in the 1st sheet to get it to show on the 2nd sheet without a problem, it would make the 1st sheet (which is the overall plan) much harder to read.

View 9 Replies View Related

Cross Reference Worksheets

Apr 22, 2006

I am trying to match/cross reference (check by ZIP CODE) two extremely large databases/spreadsheets in MS EXCEL. The vast majority of matches will turn up negative (no match) and only like <5% of all the rows/fields in my 31000+ excel sheet will actually match by zip to the records contained in another sheet (actually this second sheet is a large Seibel Sales database that I exported to Excel for easier access/manipulation) So it would be very tedious and a time waster to manually match the records by hand. My question would be, is there an automated way in Excel (or using any number of Excel addons such as ASAP Utilities etc) that would let the computer do the work for me?

Basically I have two large records, one that I exported from Siebel online to Excel, and the second one is the original work Excel database that I am to work on (ie find matches by zip code and mark them as matched)

Is there a quick way to do this? The power of the computer should really be put to use, but I just don't know /cant figure out how to go about this.

here is the screenshots and explainations if you don't know what I am talking about:
[url]

Just to clarify (I'm not sure I explained it very good)

Imagine two sheets

Sheet #1 <-(Seibel converted to xls) Sheet #2 (xls sheet to be worked on)
12345 --------------------------------54986 (mark as NO MATCH)
84596 --------------------------------25746 (mark as NO MATCH)
24578 --------------------------------12345 (mark as MATCH!)
96328 --------------------------------15789 (mark as NO MATCH)
45897 --------------------------------96328 (mark as MATCH!)

The problem is Sheet #2 is not only five rows, it is like 30000+ rows, and I
will have to do another 30000+ rows soon, so 60000+ to do by hand is very slow indeed.

Also, >95% of all the rows in Sheet #2 will be a NO MATCH with no matching zip codes to ANY of the zip codes of Sheet #1. So less than 5% will actually be a match, but the thing is I have to check ALL of them to actually know which ones match and which don't.

IS there a utility / macro or function in Excel that can quickly let me do this the automated way?

View 9 Replies View Related

Cell Reference :: Formula To Reference New Cells

Feb 15, 2010

I have lets say 12 months of data. I have formulas that reference the latest 6 months. When I insert a new column to input a new month, how can I make the formulas include the new months without manually updating them.

EXAMPLE:

12 months of data exist in cells B3:M3 going from B3(oldest) to M3(newest). Formulas reference latest 6 months of data in cells H3:M3. When a new month hits, I insert a column after column M.I would like the formulas to now reference cells I3:N3 which is now the newest 6 months.

View 14 Replies View Related

How To Reference Cells Value To Define Range Reference

Jul 14, 2014

Is it possible to reference a cells value to define a range reference?

[Code] ......

I am trying to define the row value in the range reference with a value in a secondary cell?

View 3 Replies View Related

How To Reference Worksheets Inside Workbook

Jul 3, 2013

I do a payroll workbook for my small company. Each sheet inside the workbook is a month. Each sheet totals the monthly labor categories. I would like to keep a "year to date" total on each employee and therefore would have to reference each sheet.

Setting up a formula (or tell me where to look) on how to total worksheets inside a workbook?

Example: the workbook contains a total of 12 sheets, representing the 12 months of the year. Add "cell reference" from sheet 1 to sheet 2 to sheet 3 to reach a total, then when sheet 4 is completed it contains the cumulative totals of all 4 sheets, etc., etc. Where I can look at this "year to date total" and compare to last years "year to date total".

View 2 Replies View Related

Formula To Reference Different Worksheets When Copied/Dragged

Sep 21, 2006

My worksheet "Ledger" has a column of 1-31 days, with row headings of MC/Visa, Amex, Disc & Cash. I also have sheets "1-31" within the same workbook that contain the data I need under each heading. I can get the data for "Amex" in sheet "1" to appear in the first cell underneath "Amex" in the sheet "ledger" by typing the formula ='1'!$B$29.....

View 6 Replies View Related

Dropdowns Used To Reference Information In Various Worksheets To Update Charts?

Apr 23, 2014

I need to create a page (call it a dashboard) in excel whereby drop down fields (doesn't have to be drop downs, just how I imagine it to work at the moment), are used to refer to information in another worksheet, which updates several charts on the dashboard.

So, for example, I have several additonal tabs in the spreadsheet called 'Company 1', 'Company 2' etc, which in each tab contains information (monthly results) in exactly the same layout and format. However, on the front page, I would like to be able to select 'Company 1' (or 'Company 4') and then sub-select 'March 2014' results within that, referring to the data in the 'Company 1' tab. If possible, it would be handy to have this information displaying in a grid on the dashboard, which updates when you change between months and/or company. This could be the source data for the chart.

View 1 Replies View Related

Copy Cells / Range From Worksheets Positioned Between Two Worksheets

Jul 7, 2014

Let's say I have a workbook with 7 worksheets named, for example, "Instruction", "Begin", "Worksheet 1", "Worksheet 2", "Worksheet 3", "End", and "Data". (in that order)

What I want to do is run a macro to go to whatever worksheet that is in between "Begin" and "End" and copy, for example, cells $C$1:$D$10; then paste as formula into worksheet "Data" starting from cell C1 and then down a list (i.e., copied cells from "Worksheet 1" get pasted as formula into "Data" cells C1:D10; then copied cells from "Worksheet 2" get pasted as formula into "Data" cells C11:D20, and so on and so forth).

But if I were to add more worksheets (e.g., "Recipe" and "ToDo") positioned in between "Begin" and "End" and run the macro again, it'll either 1) re-copy all the formulas from the included worksheets back into "Data" including the formulas from the newly added/placed worksheets or 2) it'll add the formulas from the newly added/placed worksheets and paste into "Data" at the end of the list.

Can create the macro to run based on the position of worksheet, and not based on the name of worksheet, since ultimately there will probably be over 10 worksheets between "Begin" and "End".

View 4 Replies View Related

Copying Formulas Between Worksheets While Keeping Reference To Original Worksheet?

Mar 4, 2014

I have a series of formulas in Row 1 across the columns in worksheet A. I would like to copy these formulas to a single column and down rows in worksheet B while retaining the references to worksheet A. I know that I can manually enter the references in worksheet B but that would take a long time to do.

View 1 Replies View Related

Compare Data On 2 Separate Worksheets And Pull A Reference Cell

Mar 14, 2009

I have on inventory sheet that has all the data in each cell 2612 to be exact! That changes month to month with deletions.

The format is: Sheet 2
A/B/
334702/UEMR88QX
334703/UEMR85QX
334704/UEMR81QX
334707/UEMR8JQX

The master worksheet has about 5000 items and the (A and B) data are both on it with other data ranging from (A-Y). My question is how do I have a cell look up data and return that it exists or doesn’t exist on the inventory sheet?

Master
A/B/C/D/E/
35/ 465/881676311350/311350/UEMR8ZTU
36/469/881676310722/310722/UEMR8V5V
37/483/881676310924/310924/UEMR8XYU
38/805/881676331096/331096/UEMR8J13

My other problem I need to take that months inventory list and have it look at the master list and return the system number from the master list (B) next to the matching inventory number (C). Allowing me to cross match inventory to master each month and save inventory worksheet each month.

I know I can copy the column and past is as a value so I won’t lose the numbers as the master changes but getting the data onto that months inventory has been a pain.

View 6 Replies View Related

Formula Copy & Paste: Reference Other Worksheets Within The Workbook, Are Copied Over As Links

Apr 28, 2007

I'm trying to perform a copy & paste operation by hand which to me should be easy in theory. I have a worksheet whose formulas have been corrupted. I have a backup of this sheet- I'd like to copy the backup sheet into the workbook. The problem is that the formulas, which reference other worksheets within the workbook, are copied over as links to the backup workbook instead of the new workbook they are being copied too. This seems simple, but how do I copy the worksheet and the references without having to go through all of the references by hand to only apply to the local book.

View 4 Replies View Related

Fill Empty Column Cells With Reference To Adjacent Cells

Aug 11, 2008

I would like a macro that when run, finds empty cells in a column within the used range and fills them with the same formula in the other cells in the same column but relative to the row.

I have a basic understanding of VBA so if someone can set me on the right track i'll have a go myself as i appreciate this would take a while to write out from scratch.

View 9 Replies View Related

Find Function Not Working On Cells Which Reference Other Cells?

Oct 31, 2011

I am using the find function to establish the row number in a spreadsheet for an edit process.

Code:
wks.Range("B:B").find("something").Row

the cells in Range("B:B") are linked to another worksheet. If I copy paste values on the cells in Range("B:B") the code above works a treat.

View 4 Replies View Related

Protect Certain Locked Cells From Editing And Allow Certain Unlocked Cells To Be Changed On Multiple Worksheets?

Jan 31, 2014

1.I need to protect certain locked cells from editing and allow certain unlocked cells to be changed on multiple worksheets.

2.When all of the changes are made to the unlocked cells, I need to password protect the entire workbook (except one worksheet) from any changes. (i.e. Prevent even the unlocked cells from being edited)

3.I also need a password to un-protect the workbook and return it to the state described in # 1. above .

View 1 Replies View Related

SUMIFS To Sum Cells If Reference Cells Are Blank?

Dec 19, 2013

I am trying to use the SUMIFS function to sum the cells in one column if the cells in a reference column ARE blank, and if the cells in a different reference column fall within a date range.

-Column F contains currency, and is the sum range
-Column H contains dates as well as blanks, and I need the criteria to look for cells that ARE blank.
-Column A contains dates, and I need the criteria to be <= 01 Feb 2013 and > 31 Dec 2012

This is the formula I have tried, but it keeps adding the cells in F if the cells in H ARE NOT blank. Clearly, it's an issue with "<>" and probably a very simple solution, but I just can't figure it out.

=SUMIFS('2013'!F9:F59,'2013'!H9:H59,"<>",'2013'!A9:A59,">"&DATE(2012,12,31),'2013'!A9:A59,"<"&DATE(2013,2,1)

View 3 Replies View Related

Reference Cells Into Merged Cells

Nov 6, 2013

I have a spreadsheet list that has no merged cells. I need to copy that spreadsheet into another spreadsheet where each row is actually 2 merged rows. I thought that I could manually reference the first few rows, then drag them down to copy and hope that excel would be smart enough to pick up the pattern. But it doesn't, and keeps copying over every other row. Is there a way to quickly copy a spreadsheet with individual rows into a spreadsheet with merged (two rows) cells? Seems like it should be doable.

View 6 Replies View Related

Cells With A Choice Of 4 Reference Cells?

Nov 22, 2013

I have cells that all reference from a 4x147 table, each row has one piece of data in, but only one piece from a choice of 4 cells (3 of which are blank).

the cells that are referencing this data, i would like to make them reference to all 4 cells, ignore the blanks and choose the one with the data.

View 1 Replies View Related

Use Cells To Create A Reference?

Jan 22, 2014

how to use cells to create refrences in a formula so I don't have to hand write each refrence in a cell. I wanted to do this by creating a row with accending numbers (1,2,3,4,etc.) and a column with decending characters (a,b,c,d,etc...). Can this be done?

So for example I have a cell formula that reads the following

'Raw Data'!A2

Can I modify this formula so that the formula will automaticly refrence a different letter that corresponds with the cells location? So I would use this cell to change the (A) automatically depending on where the cell is in the spreadsheet. Likewise can I do that with the (2) the same way?

Does this make sence, or should I just continue to hard write each cell?

The reason why I need to do this is because the raw data is in a single column and the formulas that I need to use this data for needs me to convert the data into rows instead of columns.

View 7 Replies View Related

Reference Cells In Different Rows?

Dec 7, 2008

I paste a weekly information sheet into a workbook. I pull information from cells within the sheet to form a weekly report. The sheet I paste is identical in columns but not always identical in rows; sometimes it may not have any information on one of the accounts. That account will be deleted and the sheet is 10 rows shorter. The sheet is over 1000 rows so it is too time consuming to add and delete the rows each week to match. My guess is I need to use VLOOKUP & OFFSET but I cannot get any of the formulas I tried to work. The account number appears on each 10 lines for that account. I have made up an example workbook to give an idea of what I need.

View 2 Replies View Related

Countif Reference Two Cells

Nov 20, 2009

i have this formula:

=COUNTIF(INDIRECT(A2&"_RES"),"Yes")
A2 refers to a cell to complete the indirect query (DCA_RES) which refers to another range in the workbook.

Now I want to have count if A2&"_INIT" is "BB" AND if A2&"RES" is "YES"

So that I get the amount of records of resolved queries (Yes) that match my Initials (BB).

I cant think of a different formula that might work?

View 10 Replies View Related

Cross Reference Cells

Jul 29, 2008

I am involved with regional sales and have developed a spreadsheet to track various statistics and information regarding the various cities and clients.

On Sheet1 I have Column A for the city names. Columns B through R are various statistical information (all numerical) associated with that city.

I would like to use Sheet2 to quickly draw that information into a generic "printable" spreadsheet such that I type a city name into Sheet2 A1 and the rest of the cells automatically draw the information for that city into their respective cells.

Looking through other threads I thought that maybe VLOOKUP would be the ticket, but it's only returning a #VALUE! error.

View 9 Replies View Related

Max Of 5 Cells Using Offset Reference

Feb 6, 2012

Essentially i'm trying to get the max of 5 cells at a time using the 'offset' reference. Here's my erroneous code:

Dim maxCell As Range
For Each maxCell In Range("y9:y150")
If maxCell.Value "" Then

maxCell.Offset(0, 1).Formula = "=max(maxCell.offset(0,-20): maxCell.offset(-5,-20))"

'maxCell.Offset(0, 1).Formula = "=max(maxCell.Offset(-5, -22) : (0, -22))"
End If
Next maxCell

I have two slightly different lines of code here (highlighted in red), one is commented, but i've been changing both to no avail.

I only want this to be triggered when the cell to the left (maxCell) is not empty, hence I use the offset feature. I've tried to use absolute references for the max formula, but then each cell has the same answer.

View 5 Replies View Related

How To Reference Adjacent Cells

May 23, 2013

I need the syntax that allows me to reference the cell directly beside my target cell for each row in a column

Like:

Col A Col B
S D
T F
S F

If i was targeting Col B

View 6 Replies View Related

VBA To Reference To A Sheet For 2 Cells

Jul 8, 2014

i have a code to save file referencing to a certain cell on a sheet, but how do i reference to 2 of them, since one is for a month and the other one is for year:

ChDir "X:SSS2014" ActiveWorkbook.SaveAs Filename:="X:SSS2014SSS Report* " & Format(Sheets("CP").Range("D3").Value, "mmmm yyyy") & ".xlsm", FileFormat:=".xlsm

so i have D3 and F3 to reference to together?

View 3 Replies View Related

Reference Existing Cells

Nov 19, 2007

I have a rather extensive, for me that is, excel report of daily stats with rows for daily, weekly totals, monthly totals. All have various formulas along the way for various needs - some are simple sums, some are averages, some are percentages.

I need to make some new reports for various purposes that pull the existing information/calculations over into another sheet(s).

For instance - I want to do a report for *just* the week totals so I can do more analyzinig, but I don't know how to pull the already calculated info over so if it ever changes (as it sometimes does) - then my whole dataset reflects the change.

View 9 Replies View Related

Concatenation Of Cells Used To Reference

May 18, 2006

I need to reference a cell based on the results of two others - basically concatenate the two and then use the result.

A1 = G
A2 = 5

Then in B2, I need the value of the item in G5. So I need B2 to contain something like:

="[Value of A1]+[Value of A2]"
=G5

B2 now reflects the value in G5.

I know what I want to do, just can't get the syntax.

View 4 Replies View Related

Reference Cells Using Variables

Feb 4, 2008

I am having an error when I try to run it and it appears not to like my destination (line bolded) - WHAT AM I DOING WRONG.?? Numrows = Worksheets("File1"). Range("A65536").End(xlUp).Row 'get the Row count

' LOOKUP Looklist! (Col B)
'----------------------------
For d = 1 To 228
' looks in Looklist B2(2,2), check against G1(1,7)
If Worksheets("Looklist").Cells(d + 1, 2).Value = Worksheets("File1").Cells(1, d + 6).Value Then
' If matches then copies G-x(lastrow,7) to looklist L2(2,12)
Worksheets("File1").Cell(Numrows, d + 6).Copy _
Destination:=Worksheets("Looklist").Cells(d + 1, 12)
End If
Next d

View 4 Replies View Related

Reference Cells One To One In Conditional Formatting?

Jul 24, 2014

I'm using conditional formatting where one column of cells is referencing another column of cells. For example, I want cell K2 to highlight if the date entered doesn't match the date in F2. I then want cell K3 to highlight if the date entered doesn't match the date in F3. I want cell K4 to highlight if the date entered doesn't match the date in F4 and so on. I'm not having any issue putting conditional formatting on the cells/row references individually, but when I try to copy the formatting (by using format painter) to save time all of the cells in column K just reference the first cell in column F since the first cell in column K references that F cell but not any other F cell. How do I copy these row by row references between columns so that each reference is unique?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved