Can't reference 2 other sheets in a third!

Hi All,

I have a workbook with 3 sheets.  In the third sheet, I'm trying to
reference columns in both sheet 1 and 2, to compare data entry by
person 1 and person 2, respectively.  When I put in the references for
one sheet (e.g., ='Bup1'!A2) it's fine.  When I do the same in an
adjacent column so I can compare the two, it only gives me the formula
as if it were just text I typed in.  For example, in sheet 3, when
referenced, Column A and B are comparing dates from the two entries.
Column A shows 9/6/2007, and Column B shows ='Bup2'!A2.  I referenced
these by entering = then navigating to the cell, and also by typing.
Neither works.  I'm in 2007 and I don't know if there's some weird
quirk.  Why would it do this and how can I fix it?

Thanks,
Dara
0
darahx (7)
10/26/2009 4:24:44 PM
excel 39879 articles. 2 followers. Follow

5 Replies
306 Views

Similar Articles

[PageSpeed] 24

Select the cell
Give the cell a General format.
Then hit F2, then hit the enter key.  

Excel will see you're entering a formula.

rocketD wrote:
> 
> Hi All,
> 
> I have a workbook with 3 sheets.  In the third sheet, I'm trying to
> reference columns in both sheet 1 and 2, to compare data entry by
> person 1 and person 2, respectively.  When I put in the references for
> one sheet (e.g., ='Bup1'!A2) it's fine.  When I do the same in an
> adjacent column so I can compare the two, it only gives me the formula
> as if it were just text I typed in.  For example, in sheet 3, when
> referenced, Column A and B are comparing dates from the two entries.
> Column A shows 9/6/2007, and Column B shows ='Bup2'!A2.  I referenced
> these by entering = then navigating to the cell, and also by typing.
> Neither works.  I'm in 2007 and I don't know if there's some weird
> quirk.  Why would it do this and how can I fix it?
> 
> Thanks,
> Dara

-- 

Dave Peterson
0
petersod (12005)
10/26/2009 4:50:13 PM
On Oct 26, 10:50=A0am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> Select the cell
> Give the cell a General format.
> Then hit F2, then hit the enter key. =A0
>
> Excel will see you're entering a formula.
>
>
>
>
>
> rocketD wrote:
>
> > Hi All,
>
> > I have a workbook with 3 sheets. =A0In the third sheet, I'm trying to
> > reference columns in both sheet 1 and 2, to compare data entry by
> > person 1 and person 2, respectively. =A0When I put in the references fo=
r
> > one sheet (e.g., =3D'Bup1'!A2) it's fine. =A0When I do the same in an
> > adjacent column so I can compare the two, it only gives me the formula
> > as if it were just text I typed in. =A0For example, in sheet 3, when
> > referenced, Column A and B are comparing dates from the two entries.
> > Column A shows 9/6/2007, and Column B shows =3D'Bup2'!A2. =A0I referenc=
ed
> > these by entering =3D then navigating to the cell, and also by typing.
> > Neither works. =A0I'm in 2007 and I don't know if there's some weird
> > quirk. =A0Why would it do this and how can I fix it?
>
> > Thanks,
> > Dara
>
> --
>
> Dave Peterson- Hide quoted text -
>
> - Show quoted text -

Works perfectly, thank you!  Why doesn't it do this automatically in
2007?  I have never had a problem with this in previous versions.  Is
there a settings option I don't have checked or something?

Thanks again!
0
darahx (7)
10/26/2009 4:56:45 PM
The cell you entered the formula into was pre-formatted as Text.

This is not the normal default for a new worksheet so assume somebody or a
process changed the format from default General to Text.


Gord Dibben  MS Excel MVP

On Mon, 26 Oct 2009 09:56:45 -0700 (PDT), rocketD <darahx@gmail.com> wrote:

>On Oct 26, 10:50�am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
>> Select the cell
>> Give the cell a General format.
>> Then hit F2, then hit the enter key. �
>>
>> Excel will see you're entering a formula.
>>
>>
>>
>>
>>
>> rocketD wrote:
>>
>> > Hi All,
>>
>> > I have a workbook with 3 sheets. �In the third sheet, I'm trying to
>> > reference columns in both sheet 1 and 2, to compare data entry by
>> > person 1 and person 2, respectively. �When I put in the references for
>> > one sheet (e.g., ='Bup1'!A2) it's fine. �When I do the same in an
>> > adjacent column so I can compare the two, it only gives me the formula
>> > as if it were just text I typed in. �For example, in sheet 3, when
>> > referenced, Column A and B are comparing dates from the two entries.
>> > Column A shows 9/6/2007, and Column B shows ='Bup2'!A2. �I referenced
>> > these by entering = then navigating to the cell, and also by typing.
>> > Neither works. �I'm in 2007 and I don't know if there's some weird
>> > quirk. �Why would it do this and how can I fix it?
>>
>> > Thanks,
>> > Dara
>>
>> --
>>
>> Dave Peterson- Hide quoted text -
>>
>> - Show quoted text -
>
>Works perfectly, thank you!  Why doesn't it do this automatically in
>2007?  I have never had a problem with this in previous versions.  Is
>there a settings option I don't have checked or something?
>
>Thanks again!

0
Gord
10/26/2009 5:38:19 PM
On Oct 26, 11:38=A0am, Gord Dibben <gorddibbATshawDOTca> wrote:
> The cell you entered the formula into was pre-formatted as Text.
>
> This is not the normal default for a new worksheet so assume somebody or =
a
> process changed the format from default General to Text.
>
> Gord Dibben =A0MS Excel MVP
>
>
>
> On Mon, 26 Oct 2009 09:56:45 -0700 (PDT), rocketD <dar...@gmail.com> wrot=
e:
> >On Oct 26, 10:50=A0am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> >> Select the cell
> >> Give the cell a General format.
> >> Then hit F2, then hit the enter key. =A0
>
> >> Excel will see you're entering a formula.
>
> >> rocketD wrote:
>
> >> > Hi All,
>
> >> > I have a workbook with 3 sheets. =A0In the third sheet, I'm trying t=
o
> >> > reference columns in both sheet 1 and 2, to compare data entry by
> >> > person 1 and person 2, respectively. =A0When I put in the references=
 for
> >> > one sheet (e.g., =3D'Bup1'!A2) it's fine. =A0When I do the same in a=
n
> >> > adjacent column so I can compare the two, it only gives me the formu=
la
> >> > as if it were just text I typed in. =A0For example, in sheet 3, when
> >> > referenced, Column A and B are comparing dates from the two entries.
> >> > Column A shows 9/6/2007, and Column B shows =3D'Bup2'!A2. =A0I refer=
enced
> >> > these by entering =3D then navigating to the cell, and also by typin=
g.
> >> > Neither works. =A0I'm in 2007 and I don't know if there's some weird
> >> > quirk. =A0Why would it do this and how can I fix it?
>
> >> > Thanks,
> >> > Dara
>
> >> --
>
> >> Dave Peterson- Hide quoted text -
>
> >> - Show quoted text -
>
> >Works perfectly, thank you! =A0Why doesn't it do this automatically in
> >2007? =A0I have never had a problem with this in previous versions. =A0I=
s
> >there a settings option I don't have checked or something?
>
> >Thanks again!- Hide quoted text -
>
> - Show quoted text -

Great, thanks.
0
darahx (7)
10/26/2009 6:42:49 PM
Just to add to Gord's response...

Saved from a previous post.

Excel likes to help.

Try this on a test worksheet.
Select A1 and hit ctrl-; (to put the date in the cell)
now select B1 and type:  =a1

Notice that excel changed the format of B1 to match the format in A1.

Now format D1 as Text.
put ASDF in D1
put =D1 in E1
You see ASDF.

With E1 selected, hit the F2 key and then enter (to pretend that you're changing
the formula).

Excel has "helped" you by changing that cell's format to text.

I don't know of any way of changing this behavior.

I just select the cell, and reformat it to General (or whatever I wanted).  I
hit F2 and then enter (to reenter that formula).

Sometimes this feature is nice, sometimes it ain't.

=========
(And excel has been helpful for as many generations as I can remember.  You
can't blame it on _just_ xl2007.)

rocketD wrote:
> 
> On Oct 26, 10:50 am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > Select the cell
> > Give the cell a General format.
> > Then hit F2, then hit the enter key.
> >
> > Excel will see you're entering a formula.
> >
> >
> >
> >
> >
> > rocketD wrote:
> >
> > > Hi All,
> >
> > > I have a workbook with 3 sheets.  In the third sheet, I'm trying to
> > > reference columns in both sheet 1 and 2, to compare data entry by
> > > person 1 and person 2, respectively.  When I put in the references for
> > > one sheet (e.g., ='Bup1'!A2) it's fine.  When I do the same in an
> > > adjacent column so I can compare the two, it only gives me the formula
> > > as if it were just text I typed in.  For example, in sheet 3, when
> > > referenced, Column A and B are comparing dates from the two entries.
> > > Column A shows 9/6/2007, and Column B shows ='Bup2'!A2.  I referenced
> > > these by entering = then navigating to the cell, and also by typing.
> > > Neither works.  I'm in 2007 and I don't know if there's some weird
> > > quirk.  Why would it do this and how can I fix it?
> >
> > > Thanks,
> > > Dara
> >
> > --
> >
> > Dave Peterson- Hide quoted text -
> >
> > - Show quoted text -
> 
> Works perfectly, thank you!  Why doesn't it do this automatically in
> 2007?  I have never had a problem with this in previous versions.  Is
> there a settings option I don't have checked or something?
> 
> Thanks again!

-- 

Dave Peterson
0
petersod (12005)
10/26/2009 6:55:41 PM
Reply:

Similar Artilces:

MAPISP32 #2
MAPISP32 has taken over - uses almost all of the CPU (97 to 99%). Can't send or receive email. Restarting and not launching OUTLOOK gives me back my computer, but no email. Using OUTLOOK 97 on an XP machine. Have checked the postings, but found nothing about this problem (I get no error messages). Also tried renaming MAPISP32.dll and then reloading OUTLOOK, no improvement. Will check back later and thanks! ...

Beginning Inventory Balance #2
I am trying to duplicate the Average Inventory, according to KB856731, the formula is; (Beginning Inventory Balance + Summary of each months ending on hand Inventory + Current On Hand Inventory) / (number of months elapsed in the current year +2) Does anyone know what the formula is for the Beginning Inventory Balance? -- Anthony ...

Macro Security #2
Hi, I am trying to set the Macro security level to Low in Access 2003 but cannot find the option in the Tools\Macro menu. It is not even grayed out. The install is a complete install of Office 2003 and I tried a Detect and Repair without luck. Any suggestions? Thank you. Maybe a post in one of the Access newsgroups would get you an answer quicker. Clementius wrote: > > Hi, > I am trying to set the Macro security level to Low in Access 2003 but cannot > find the option in the Tools\Macro menu. It is not even grayed out. The > install is a complete install of Office 2003 ...

Spaces issue not fixed in 12.2
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel The previous threads have all been closed and none of them seem to have an answer. For anyone else struggling with Office not working with Spaces (and therefore having to disable Spaces to use Office 2008), here is the reply I just received from MS Office for Mac Tech Support: "The Spaces issue is still being worked on, please watch for future OS and Office updates" My guess is that as it has not been fixed in two service packs it is unlikely to change until Office 2012 (or whatever the next major release wil...

Conditional Formatting #2
Hello, I have a spreadsheet that has about 1200 rows many of which need to have conditional formatting. I had a template set of rows that I was transferring the conditional formatting from and it worked for 20 or so rows. Then in attempting to both copy/paste and drag formatting even further I noticed that the conditional formatting was not being transfered to the target cells. I have even tried doing one row of formatting at a time but it seems like it will not allow any more conditional formatting. The spreadsheet is a good size (about 2MB). I recieve no error messages about this. It simpl...

Printing 2 worksheets to a 2 sided document
Is there any way to print - front to back - 2 seperate worksheets? Turn the paper over and print the second worksheet???? Maybe you can create a worksheet with a picture of both ranges on it: Insert a new worksheet Edit|copy the first range shift-Edit|Paste Picture Link (on that new worksheet) Back for the second range and shift-edit|Paste picture link (right near your first pasted picture link). (insert a nice page break, too) And by pasting a link, you can change the original range and your picture will update right away. (Keep that worksheet as long as you want and print from there???)...

Export #2
Hi, How to export only the perticular group users. Regards Mustafa What sort of information do you need? "Mohammed Mustafa" <mohammed.mustafa@kharafinational.com> wrote in message news:OwKSDO$yGHA.3568@TK2MSFTNGP03.phx.gbl... > Hi, > > How to export only the perticular group users. > > Regards > Mustafa > See "IMI GAL Exporter" - http://www.imibo.com/imidev/Exchange/imige.htm "Mohammed Mustafa" <mohammed.mustafa@kharafinational.com> wrote in message news:OwKSDO$yGHA.3568@TK2MSFTNGP03.phx.gbl... > Hi, > > How ...

solver and macros #2
Before you use the solver within a macro, you must establish a reference to the Solver add-in. With a Visual Basic module active, click References on the Tools menu, and then select the Solver.xla check box under Available References. If Solver.xla doesn't appear under Available References, click Browse and open Solver.xla in the \Office\Library subfolder. Cheers Nick ...

Closing registers #2
1. Is there any way too remove the closing figure when the registers are closed... I don't want my cashiers to see the closing amounts. 2. Can a group of registers be closed out on a selected machine, e.g Reg 1, reg 2, reg 3, reg 4 are all closed on reg 1. Thanks If you don't want cashiers to see closing amounts, lock them out of doing x/z reports and viewing the journal - apply this setting in SO manager - database - cashiers - properties of each cashier - uncheck these boxes. On question 2, out of the box, RMS does not allow that - each register has its own batch that must ...

CRM v1.2 for MSDN?
We are a UNIVERSAL MSDN Subsciber which means we pay about $4000 a year to get the entire Microsoft Library. I have not seen CRM 1.2 in any of the updates which is very strange since I normally see them before any other subscription. Did I miss an update? I also notice on the MSDN downloads section they still only have v1.0 listed. Thanks! CRM 1.2 is only just shipping to partners etc. I would not expect it to appear on MSDN for a couple of months "MEI" <MEI@MEI.COM> wrote in message news:Of0KXo7sDHA.3496@TK2MSFTNGP11.phx.gbl... > We are a UNIVERSAL MSDN Subsciber whic...

hyperlinks #2
When I click on a hyperlink in outlook IE opens and stays blank, but another window opens and asks me to Locate link browser. I have been closing this window, but it reappears on every link. ??????????????? It may help you http://support.microsoft.com/default.aspx?scid=kb;en- us;329912&Product=out >-----Original Message----- >When I click on a hyperlink in outlook IE opens and stays >blank, but another window opens and asks me to Locate link >browser. I have been closing this window, but it reappears >on every link. ??????????????? >. > ...

Freezing Panes #2
How can I freeze two different rows in a single sheet. -- Naidu Pl don't multi-post. Check responses in .misc -- Max Singapore http://savefile.com/projects/236895 Downloads:17,000, Files:358, Subscribers:55 xdemechanik --- "Naidu" <Naidu@discussions.microsoft.com> wrote in message news:967FF8C4-431D-4BAE-9903-ECCDE18157D7@microsoft.com... > How can I freeze two different rows in a single sheet. > > -- > Naidu ...

Deleting Mail #2
This problem seems to be random over our network. We are running Windows Server 2000 with Outlook 2002. Some users are getting a notification that they have mail and when they check, there is nothing in their inbox. The new mail has automatically gone to the deleted items folder. It doesn't do it with all mail for that particular user or do all the users on the network experience this problem. There are no rules set up to do this. Has anyone else have this problem. If you have a suggestion please respond to my e-mail address DKimball@puc.nh.gov Thanks I have Outlook 2002 on...

Pay commissions to 2 salespersons
Is there a way to pay commissions to two different salespersons for one line item? When I try to enter secont salesperson in "Sales Commissions Entry" window I receive a message "The commission amount may not be greater the sale amount distributed to this salesperson" Commissions in GP are calculated a little differently than what we think about. If I have two salespeople and I want to give each a 5% commission on the sale, then I need to give each of them a 10% commission on 50% of the sale. -- Jim "Pay commissions to 2 salespersons" wrote: > Is th...

Referencing Sheet Tabs
Excel 2003. I produce a weekly cash report. Each new weekly report refers to several cells on the previous week's sheet. I produce each new sheet by copying the last one and changing its name. I then have to update the formulas so that they look at the now previous sheet. Is there some way to enter the sheet tab name into a formula so that the name of the tab is taken from another cell. Hi captain, Lets say cell A1 contains the sheet name cell A2 contains the row no cell A3 contains the column no of the cell you wish to find. Then =ADDRESS(A2,A3,,,A1) will give you the cell addre...

Time #2
I need to be able to click on a cell and have it be populated with the current HH:MM:SS. I know ctrl+shift+; will do the job but I need it to be even more simple than that for the end users. One way: Put this in the worksheet code module (right-click the worksheet tab and choose View Code): Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) With Range("J1") If Not Intersect(Target, .Cells) Is Nothing Then .Value = Time .NumberFormat = "HH:MM:SS" End If End With End Sub ...

hyperlink #2
How do I make a hyperlink without underline? Thanks, Jo refer to http://www.publishermvps.com/Default.aspx?tabid=30 -- David Bartosik - MS MVP for Publisher help: www.davidbartosik.com enter to win Pub 2003: www.davidbartosik.com/giveaway.aspx "jo" <jo@discussions.microsoft.com> wrote in message news:1EA75A54-A237-4090-A4A7-EEEBE9DDA2B7@microsoft.com... > How do I make a hyperlink without underline? > > Thanks, > Jo ...

Bitmap button question #2
I'm trying to create a bitmap button. Here's what I've done: 1. Added a CStatic control to my dialog. 2. Set it's ID to IDC_BMPBUTTON 3. In the classwizzard, member varialbes tab, created a member variable m_Button, of type CBitmapButton (I had to manually change this from CStatic). 4. In the OnInitDialog(), I've added: m_BitmapButton.LoadBitmaps(IDB_BITMAP1, IDB_BITMAP2, IDB_BITMAP3, IDB_BITMAP4); m_BitmapButton.SizeToContent(); m_BitmapButton.ShowWindow(SW_SHOW); When I run my dialog, the bitmap appears grey. And which ...

Outlook and viruses #2
Hi folks, Obviously viruses need to be run, however I have heard of virus infected emails that need to be opened to infect a pc....not the attachment, the email. If this is true then does the mail window which allows one to see an email without opening it pose a risk? I do not open junk email, but it may open in this window. Thanks, Danny Yes, HTML messages can be a risk in older versions of Outlook. One of the first ones that I remember is the KAK worm. This bugger arrived as an innocent HTML message that exploited an Active-X component on the machine to place a file in a pre-d...

I selected row 1 and row 2 to freeze and it is freezing at row 12
I am selecting row 1 and row 2 to freeze and excel keeps freezing at row 12? Select one row only, if you want 2 rows above where it freezes select row 3, that will leave row1 and 2 unaffected when you scroll down -- Regards, Peo Sjoblom "Lori Brooks" <Lori Brooks@discussions.microsoft.com> wrote in message news:C968CA9B-35A3-427E-993C-D36F3B6D37B7@microsoft.com... >I am selecting row 1 and row 2 to freeze and excel keeps freezing at row >12? Hi, To freeze row 1 & 2 select A3 and then Window|Freeze Panes Mike "Lori Broo...

"Ghost" sheet objects returned while using Excel.Application
I am trying to parse a spreadsheet using JavaScript and I'm finding "ghost" Sheet objects in the file. It seems that if the user copied and renamed the file and then deleted some tabs that those tabs are still accessible programmitcally. If I view the excel file in Excel or try to load it into SQL Server then the "ghost" tabs don't show up. But if I read it using the Excel.Application in JavaScript then they show up. Here's an excerpt of what I'm doing: var wb = XLS.WorkBooks.Open(fname); var ws = XLS.Sheets; var cell = null; var e = new Enumerator(XLS....

insert rows--->protected sheet
I have a protected sheet in Excel 2007, and I would like to write a macro that allows a user to insert a new row(s) that also copies the formulas (but not the text/data) from the previous row. When I searched for a possible solution, I found the following macro #2, which works perfectly, if the sheet is unprotected. I use the following code (macro #1) in another macro to unprotect a sheet temporarily to run a macro, then re-protect it. I select a row on the spreadsheet and run the macro InsertRowsAndFillFormulas_caller(). I get a "Compile Error: Invalid or Unqualified ...

Look & Feel #2
Hi all, I use BCMenu (from Codeproject) to give my Win 2000 application an xp style. I love this grayed icons. Is this also possible for the toolbar in Win 2000?! The toolbar is looking very ugly, when an icon (256 color) is dark, so the grayed icon looks only like a grayed anything where I see only the outline. Thanks, Matthias http://www.codeproject.com/docking/ctruecolortoolbar_update.asp Tom "Matthias" <ichwarteaufmail@yahoo.de> wrote in message news:1125661990.026928.209950@g44g2000cwa.googlegroups.com... > Hi all, > > I use BCMenu (from Codeproject) to gi...

Default dictionary #2
I use Outlook at work and I have set the default dictionary to English UK but invariably it reverts to English USA and I have to manually make it UK again. Can anyone tell me what I'm doing wrong and what I need to do to make it stay as UK. The default editor is Word and I wonder if that is the problem as in there is a setting for Default Language but that seems to still hold USA instead of UK so I think this is where my problem lies. I don't have the same problem on my laptop at home, the default for Word stays as UK but I use OE for email. -- Lynn Please remove spamtrap if replyi...

send message #2
i have a dialog created with this code CreateEx(0, NULL, NULL, WS_POPUP|WS_VISIBLE, CRect(pt,CSize(0,0)), pParentWnd, nID); CRect Rect2(10,85,80,110); m_Close.Create("Close",WS_CHILD|WS_VISIBLE|BS_CENTER |BS_NOTIFY ,Rect2,this,IDC_CLOSE); // button CRect Rect(120,85,190,110); m_Open.Create("Open",WS_CHILD|WS_VISIBLE|BS_CENTER |BS_NOTIFY ,Rect,this,IDC_OPEN); // button when i click one of the two button i send message with this code TBNOTIFY mm; mm.hdr.code = NM_CLICK; mm.hdr.hwndFrom = m_hWnd; mm.hdr.idFrom = GetDlgCtrlID(); mm.iItem = 2; mm.cchText = 5...