=CELL("filename") returns #VALUE!
in [General]
|
From: Dave Peterson on 8 Apr 2010 08:05 Just to add to Bob's response... Try opening two files (so that they've been saved). Then make a new window and make sure that each window shows a sheet from a different workbook. Then try each of the formulas (with the cell reference and without the cell reference) and recalculate. You'll see the problem (or feature if that's actually want you want <vbg>). Bob Phillips wrote: > > You are wrong. > > If you have two workbooks open and that formula in WB1, if a full recalc > happens whilst WB2 is open, the formula will update and point at the wrong > workbook. > > -- > > HTH > > Bob > > "????? (????) ?????" <micky-a*at*tapuz.co.il> wrote in message > news:5F4159A0-8414-44C7-8412-5D2B83C7A905(a)microsoft.com... > > To the best of my knowledge, "filename" returns the WB name and as such it > > will not cause any problem without using the A1. > > However, > > =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,31) returns the > > ONLY SHEETS NAME and therefor needs the A1. > > please, correct me if I'm wrong. > > Micky > > > > > > "Gord Dibben" wrote: > > > >> After you have found the Turkish equivalent to "filename" change your > >> formula slightly. > >> > >> =CELL("filename",A1) > >> > >> Elsewise you can get wierd results when you switch sheets back and forth. > >> > >> > >> Gord Dibben MS Excel MVP > >> > >> On Wed, 7 Apr 2010 13:19:01 -0700, SupperDuck > >> <SupperDuck(a)discussions.microsoft.com> wrote: > >> > >> >Dear Friends, > >> > > >> >When I use =CELL("filename") in Turkish regional style, it gives #VALUE! > >> >error but if I change the settings to USA, it works! > >> > > >> >Why? And how I can solve this ? > >> > > >> >PS: I use an english office 2007 and I use the formulas in English, no > >> >problem with any of them. But I got error with this. > >> > > >> >Thanks & Regards, > >> > >> . > >> -- Dave Peterson
From: מיכאל (מיקי) אבידן on 8 Apr 2010 10:57 Thanks. Every day I learn somethig new... Micky "Dave Peterson" wrote: > Just to add to Bob's response... > > Try opening two files (so that they've been saved). Then make a new window and > make sure that each window shows a sheet from a different workbook. > > Then try each of the formulas (with the cell reference and without the cell > reference) and recalculate. > > You'll see the problem (or feature if that's actually want you want <vbg>). > > Bob Phillips wrote: > > > > You are wrong. > > > > If you have two workbooks open and that formula in WB1, if a full recalc > > happens whilst WB2 is open, the formula will update and point at the wrong > > workbook. > > > > -- > > > > HTH > > > > Bob > > > > "????? (????) ?????" <micky-a*at*tapuz.co.il> wrote in message > > news:5F4159A0-8414-44C7-8412-5D2B83C7A905(a)microsoft.com... > > > To the best of my knowledge, "filename" returns the WB name and as such it > > > will not cause any problem without using the A1. > > > However, > > > =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,31) returns the > > > ONLY SHEETS NAME and therefor needs the A1. > > > please, correct me if I'm wrong. > > > Micky > > > > > > > > > "Gord Dibben" wrote: > > > > > >> After you have found the Turkish equivalent to "filename" change your > > >> formula slightly. > > >> > > >> =CELL("filename",A1) > > >> > > >> Elsewise you can get wierd results when you switch sheets back and forth. > > >> > > >> > > >> Gord Dibben MS Excel MVP > > >> > > >> On Wed, 7 Apr 2010 13:19:01 -0700, SupperDuck > > >> <SupperDuck(a)discussions.microsoft.com> wrote: > > >> > > >> >Dear Friends, > > >> > > > >> >When I use =CELL("filename") in Turkish regional style, it gives #VALUE! > > >> >error but if I change the settings to USA, it works! > > >> > > > >> >Why? And how I can solve this ? > > >> > > > >> >PS: I use an english office 2007 and I use the formulas in English, no > > >> >problem with any of them. But I got error with this. > > >> > > > >> >Thanks & Regards, > > >> > > >> . > > >> > > -- > > Dave Peterson > . >
From: Gord Dibben on 8 Apr 2010 11:14 In addition.............. =CELL("filename") Returns not just workbook name but full path including sheetname. So the incorrect results will occur even in a single workbook. Gord On Thu, 8 Apr 2010 07:57:01 -0700, ????? (????) ????? <micky-a*at*tapuz.co.il> wrote: >Thanks. >Every day I learn somethig new... >Micky > > >"Dave Peterson" wrote: > >> Just to add to Bob's response... >> >> Try opening two files (so that they've been saved). Then make a new window and >> make sure that each window shows a sheet from a different workbook. >> >> Then try each of the formulas (with the cell reference and without the cell >> reference) and recalculate. >> >> You'll see the problem (or feature if that's actually want you want <vbg>). >> >> Bob Phillips wrote: >> > >> > You are wrong. >> > >> > If you have two workbooks open and that formula in WB1, if a full recalc >> > happens whilst WB2 is open, the formula will update and point at the wrong >> > workbook. >> > >> > -- >> > >> > HTH >> > >> > Bob >> > >> > "????? (????) ?????" <micky-a*at*tapuz.co.il> wrote in message >> > news:5F4159A0-8414-44C7-8412-5D2B83C7A905(a)microsoft.com... >> > > To the best of my knowledge, "filename" returns the WB name and as such it >> > > will not cause any problem without using the A1. >> > > However, >> > > =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,31) returns the >> > > ONLY SHEETS NAME and therefor needs the A1. >> > > please, correct me if I'm wrong. >> > > Micky >> > > >> > > >> > > "Gord Dibben" wrote: >> > > >> > >> After you have found the Turkish equivalent to "filename" change your >> > >> formula slightly. >> > >> >> > >> =CELL("filename",A1) >> > >> >> > >> Elsewise you can get wierd results when you switch sheets back and forth. >> > >> >> > >> >> > >> Gord Dibben MS Excel MVP >> > >> >> > >> On Wed, 7 Apr 2010 13:19:01 -0700, SupperDuck >> > >> <SupperDuck(a)discussions.microsoft.com> wrote: >> > >> >> > >> >Dear Friends, >> > >> > >> > >> >When I use =CELL("filename") in Turkish regional style, it gives #VALUE! >> > >> >error but if I change the settings to USA, it works! >> > >> > >> > >> >Why? And how I can solve this ? >> > >> > >> > >> >PS: I use an english office 2007 and I use the formulas in English, no >> > >> >problem with any of them. But I got error with this. >> > >> > >> > >> >Thanks & Regards, >> > >> >> > >> . >> > >> >> >> -- >> >> Dave Peterson >> . >>
From: מיכאל (מיקי) אבידן on 8 Apr 2010 12:19 I just typed: =CELL("filename") in sheet1 of a previous saved WB. I copied it to sheet2 and upon switching between those 2 sheets I dont get any error and/or a different result. What am I doing wrong here ? Micky "Gord Dibben" wrote: > In addition.............. > > =CELL("filename") > > Returns not just workbook name but full path including sheetname. > > So the incorrect results will occur even in a single workbook. > > > Gord > > On Thu, 8 Apr 2010 07:57:01 -0700, ????? (????) ????? > <micky-a*at*tapuz.co.il> wrote: > > >Thanks. > >Every day I learn somethig new... > >Micky > > > > > >"Dave Peterson" wrote: > > > >> Just to add to Bob's response... > >> > >> Try opening two files (so that they've been saved). Then make a new window and > >> make sure that each window shows a sheet from a different workbook. > >> > >> Then try each of the formulas (with the cell reference and without the cell > >> reference) and recalculate. > >> > >> You'll see the problem (or feature if that's actually want you want <vbg>). > >> > >> Bob Phillips wrote: > >> > > >> > You are wrong. > >> > > >> > If you have two workbooks open and that formula in WB1, if a full recalc > >> > happens whilst WB2 is open, the formula will update and point at the wrong > >> > workbook. > >> > > >> > -- > >> > > >> > HTH > >> > > >> > Bob > >> > > >> > "????? (????) ?????" <micky-a*at*tapuz.co.il> wrote in message > >> > news:5F4159A0-8414-44C7-8412-5D2B83C7A905(a)microsoft.com... > >> > > To the best of my knowledge, "filename" returns the WB name and as such it > >> > > will not cause any problem without using the A1. > >> > > However, > >> > > =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,31) returns the > >> > > ONLY SHEETS NAME and therefor needs the A1. > >> > > please, correct me if I'm wrong. > >> > > Micky > >> > > > >> > > > >> > > "Gord Dibben" wrote: > >> > > > >> > >> After you have found the Turkish equivalent to "filename" change your > >> > >> formula slightly. > >> > >> > >> > >> =CELL("filename",A1) > >> > >> > >> > >> Elsewise you can get wierd results when you switch sheets back and forth. > >> > >> > >> > >> > >> > >> Gord Dibben MS Excel MVP > >> > >> > >> > >> On Wed, 7 Apr 2010 13:19:01 -0700, SupperDuck > >> > >> <SupperDuck(a)discussions.microsoft.com> wrote: > >> > >> > >> > >> >Dear Friends, > >> > >> > > >> > >> >When I use =CELL("filename") in Turkish regional style, it gives #VALUE! > >> > >> >error but if I change the settings to USA, it works! > >> > >> > > >> > >> >Why? And how I can solve this ? > >> > >> > > >> > >> >PS: I use an english office 2007 and I use the formulas in English, no > >> > >> >problem with any of them. But I got error with this. > >> > >> > > >> > >> >Thanks & Regards, > >> > >> > >> > >> . > >> > >> > >> > >> -- > >> > >> Dave Peterson > >> . > >> > > . >
From: Dave Peterson on 8 Apr 2010 13:12 Make sure you recalc. And it's easier to see if you have two windows open, too. ????? (????) ????? wrote: > > I just typed: =CELL("filename") in sheet1 of a previous saved WB. > I copied it to sheet2 and upon switching between those 2 sheets I dont get > any error and/or a different result. > What am I doing wrong here ? > Micky > > "Gord Dibben" wrote: > > > In addition.............. > > > > =CELL("filename") > > > > Returns not just workbook name but full path including sheetname. > > > > So the incorrect results will occur even in a single workbook. > > > > > > Gord > > > > On Thu, 8 Apr 2010 07:57:01 -0700, ????? (????) ????? > > <micky-a*at*tapuz.co.il> wrote: > > > > >Thanks. > > >Every day I learn somethig new... > > >Micky > > > > > > > > >"Dave Peterson" wrote: > > > > > >> Just to add to Bob's response... > > >> > > >> Try opening two files (so that they've been saved). Then make a new window and > > >> make sure that each window shows a sheet from a different workbook. > > >> > > >> Then try each of the formulas (with the cell reference and without the cell > > >> reference) and recalculate. > > >> > > >> You'll see the problem (or feature if that's actually want you want <vbg>). > > >> > > >> Bob Phillips wrote: > > >> > > > >> > You are wrong. > > >> > > > >> > If you have two workbooks open and that formula in WB1, if a full recalc > > >> > happens whilst WB2 is open, the formula will update and point at the wrong > > >> > workbook. > > >> > > > >> > -- > > >> > > > >> > HTH > > >> > > > >> > Bob > > >> > > > >> > "????? (????) ?????" <micky-a*at*tapuz.co.il> wrote in message > > >> > news:5F4159A0-8414-44C7-8412-5D2B83C7A905(a)microsoft.com... > > >> > > To the best of my knowledge, "filename" returns the WB name and as such it > > >> > > will not cause any problem without using the A1. > > >> > > However, > > >> > > =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,31) returns the > > >> > > ONLY SHEETS NAME and therefor needs the A1. > > >> > > please, correct me if I'm wrong. > > >> > > Micky > > >> > > > > >> > > > > >> > > "Gord Dibben" wrote: > > >> > > > > >> > >> After you have found the Turkish equivalent to "filename" change your > > >> > >> formula slightly. > > >> > >> > > >> > >> =CELL("filename",A1) > > >> > >> > > >> > >> Elsewise you can get wierd results when you switch sheets back and forth. > > >> > >> > > >> > >> > > >> > >> Gord Dibben MS Excel MVP > > >> > >> > > >> > >> On Wed, 7 Apr 2010 13:19:01 -0700, SupperDuck > > >> > >> <SupperDuck(a)discussions.microsoft.com> wrote: > > >> > >> > > >> > >> >Dear Friends, > > >> > >> > > > >> > >> >When I use =CELL("filename") in Turkish regional style, it gives #VALUE! > > >> > >> >error but if I change the settings to USA, it works! > > >> > >> > > > >> > >> >Why? And how I can solve this ? > > >> > >> > > > >> > >> >PS: I use an english office 2007 and I use the formulas in English, no > > >> > >> >problem with any of them. But I got error with this. > > >> > >> > > > >> > >> >Thanks & Regards, > > >> > >> > > >> > >> . > > >> > >> > > >> > > >> -- > > >> > > >> Dave Peterson > > >> . > > >> > > > > . > > -- Dave Peterson
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 Prev: Custom Views Next: How do I sort a column using the last number in the cell? |