drag and drop problem with cell references

i have a cell with a formula in one worksheet that references another
cell. the formula looks like this:
=IF((ISBLANK('Draft'!$D25)),"",'Draft'!$D25)
where 'draft' is another worksheet in my workbook.
the formula works fine, it looks empty if there is nothing in the
referenced cell('Draft'!$D25), otherwise it mirrors the content of that
cell.  If i type something in the referenced cell('Draft'!$D25), it
still works great.  But if i drag something to the referenced cell than
the formula gets screwed up and replaces the $D25, with #ref.

I am guessing that there is probably a good reason for this behavior,
but is there a way to work around it, so that the contents of the
referenced cell show in the referencing cell, regardless of whether
they were typed or dragged.

thanks in advance for your help.

0
9/1/2005 5:56:58 AM
excel 39879 articles. 2 followers. Follow

3 Replies
598 Views

Similar Articles

[PageSpeed] 33

Hi!

Try this:

=IF(INDIRECT("Draft!D25")="","",INDIRECT("Draft!D25"))

Drag and drop to your hearts content!

Biff

"natanz" <natan.zimand@gmail.com> wrote in message 
news:1125554218.807581.153250@g44g2000cwa.googlegroups.com...
>i have a cell with a formula in one worksheet that references another
> cell. the formula looks like this:
> =IF((ISBLANK('Draft'!$D25)),"",'Draft'!$D25)
> where 'draft' is another worksheet in my workbook.
> the formula works fine, it looks empty if there is nothing in the
> referenced cell('Draft'!$D25), otherwise it mirrors the content of that
> cell.  If i type something in the referenced cell('Draft'!$D25), it
> still works great.  But if i drag something to the referenced cell than
> the formula gets screwed up and replaces the $D25, with #ref.
>
> I am guessing that there is probably a good reason for this behavior,
> but is there a way to work around it, so that the contents of the
> referenced cell show in the referencing cell, regardless of whether
> they were typed or dragged.
>
> thanks in advance for your help.
> 


0
biffinpitt (3172)
9/1/2005 6:23:29 AM
Use:

=IF((ISBLANK(INDIRECT("draft!D25"))),"",INDIRECT("draft!D25"))

Mangesh



"natanz" <natan.zimand@gmail.com> wrote in message
news:1125554218.807581.153250@g44g2000cwa.googlegroups.com...
> i have a cell with a formula in one worksheet that references another
> cell. the formula looks like this:
> =IF((ISBLANK('Draft'!$D25)),"",'Draft'!$D25)
> where 'draft' is another worksheet in my workbook.
> the formula works fine, it looks empty if there is nothing in the
> referenced cell('Draft'!$D25), otherwise it mirrors the content of that
> cell.  If i type something in the referenced cell('Draft'!$D25), it
> still works great.  But if i drag something to the referenced cell than
> the formula gets screwed up and replaces the $D25, with #ref.
>
> I am guessing that there is probably a good reason for this behavior,
> but is there a way to work around it, so that the contents of the
> referenced cell show in the referencing cell, regardless of whether
> they were typed or dragged.
>
> thanks in advance for your help.
>


0
9/1/2005 6:24:07 AM
thanks

0
9/1/2005 1:46:49 PM
Reply:

Similar Artilces:

Need invisible/shaded/highlighted cell
I'm thinking what I would like to do can *probably* be done in VBA, but I'd like to use the formula in the cell. If it can be done. I have three columns: DOW DATE BILL DTE --- -------- -------- Wed 02/01/06 02/03/06 Thu 02/02/06 02/06/06 Fri 02/03/06 02/07/06 Sat 02/04/06 02/06/06 Sun 02/05/06 02/07/06 Mon 02/06/06 02/08/06 Tue 02/07/06 02/09/06 Wed 02/08/06 02/10/06 . . . . The date col contains a formula to add 1 to the cell above. The day of week col uses the date from the date column. And the bill date col adds 4 days to the date col if it falls on a "Thu...

Transparent bitmap in FlexGrid cell?
I have a bitmap in the resource (IDB_PICTURE). I need to display it on a FlexGrid's cell with transparent background. Could someone show me how? This is the only function available to put a picture on a cell CFlexGrid::putref_CellPicture(LPDISPATCH) Thank you. ...

linking cells #3
I have a calendar created in Excel - each sheet is a new month that contains the following information: the last week of the previous month, the current month, and the first week of the next month. I would like to link the cells from the "overlapping" weeks, but I do not want the "0" to show up in the cells. Is there any way to prevent this? Thanks in advance! Paula Either choose not to show zero values through Tools>Options>View or trap the zero and turn to a blank-looking character. =IF(sheet1!A1="","",sheet1!A1) Gord Dibben Excel M...

Exchange problems after reboot
Hello! I'm having trouble with Exchange 2003 (Ex2003 SP2 running on Windows Server 2003 SP). Every time after reboot Exchange don't start correctly. I have a "8231 MSExchangeAL" and "8247 MSExchangeAL" errors in application log. Also the Exchange Information Store fails during startup and the Exchante MTA Stacks too. After reboot when I log on to server I can start those two services manualy and everything runs fine, so I'm little (or much) confused. I didn't have problems with reboots before. I have checked KB 27529, KB 286356 and few others and ...

a little help with finding and associating cells
ok so here is my dilemma, I need a formula that will look at a cell value on one work sheet, check for the same value on another work sheet in a defined column, and insert a coresponding value from another column...... is that even remotely possible??? any help would be greatly appreciated.... -- rcarrollct ------------------------------------------------------------------------ rcarrollct's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24931 View this thread: http://www.excelforum.com/showthread.php?threadid=384671 It's called VLOOKUP. http://www.offi...

"Diagonal" cells
Hi. I'm doing a tricky poor-man's-Illustrator project with Excel 2007. I'm trying to create the impression of a large cell that's been split in half diagonally (with a diagonal ascending from bottom left to top right). I've created a 2 x 2 grid of cells and put a diagonal border through the lower left and top right cells and removed the vertical, internal borders. Looks great so far. In addition, I'd like to place text in the upper left and lower right cells. Ideally, this text would be wider than the upper left or lower right cell and would flow over into the adja...

insert symbols in text cell
i would to insert "-" to the text cell format, how to do? example: 123456 change to 123-456 -- SelinaT ------------------------------------------------------------------------ SelinaT's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=33675 View this thread: http://www.excelforum.com/showthread.php?threadid=534451 Assuming the data posted is representative (6 digits, with dash to be inserted in centre) and running in A1 down Try in B1: =LEFT(A1,3)&"-"&RIGHT(A1,3) Copy down Then copy col B, and overwrite col A with Paste special &g...

I can not see all contents in one cell. Help me pls!
Please help me! By default when I write some text in cell and the text is longer then cell's width, I can not see his contents in next cells. But in one excel file when I sellect all cells in worksheet and then draging them it can no longer seen all contents in one cell. What I should to do? Thank you in advance! If you have wraptext turned on (format|cells|alignment tab), then the cell will expand its rowheight to show you all the data. (unless you use merged cells or have adjusted the rowheight manually). If you have wraptext turned off, then the text will overflow to the cell to ...

Printing Formatted Cells
I have conditional format on a cell to format with say Fill Red ... It displays OK on the screen but doesn't appear when printed? I'm sure it'll be something obvious! Is your print setting for color, or B&W? Long shot, but I'm not sure of any other reason why what-you-see is not what-you-get. -- Best Regards, Luke M "Charlie" <Charlie@discussions.microsoft.com> wrote in message news:C94E101A-9579-4FDB-8AEF-91D59E1EC3A4@microsoft.com... >I have conditional format on a cell to format with say Fill Red ... It > displays OK on the sc...

Exchange2K problem
Hi, I try in a new threat: I have two different lan/wan�s (and two different domaine�s). Domaine1 cannot recieve mail from Domaine2 - there is no problem the other way. Domaine1 can send mails to Domaine2 but Domaine1 cannot recieve any answers from Domaine2. There is also the same problem from two other domains: Domaine1 cannot recive mails from these two. If there more Domains with problems - I dont know. BUT: Domaine1 can recieve mails from everybody else with no problems. I cannot telnet from Domaine2 to Domaine1 port 25 - no connection can be obtained. No problem to telnet from Domai...

Send/Receive problems #3
When using MS outlook for my mail I'm unable to send/receive, as soon as I hit the 'send/receive' tab I get a message in the bottom bar saying 'Mail Delivery complete' with the animated envelopes. If I am sending an e-mail, it goes straight to the outbox, and stays there. I have checked all my settings, and have used 'detect and repair' but this has not helped. Please can anyone help? Thanks The majority of the time that this error occurs is when the mail server settings are correct but it is not recognizing your user name or password. The second and m...

format to calculation cell
I have a spreadsheet which has a weight column. This is exported as 25kg, 1000kg, 10x2kg from another program. I am trying to use this weight column in a calculation. I have inserted a new column and copied the information over and then used the find/replace to remove the KG to use in the calc cell. This works fine for the 25 / 1000 etc, but the 10x2 will not caculate. Is there a way that when i paste the cells that i can get it to change to 20 automatically. ( i presume that the x will need to be a * ?) thanks. -- mdma --------------------------------------------------------------...

minimum cell value
Hi, I am trying to set up a cell so it has a minimum value. The current formula is: =a1/a2*5 which gives the answer 0 but i need to show a minimum value of 1 is this possible? Hi, Doug, Try: =Max(a1/a2*5,1) --- Regards, Norman "Doug Bell" <Doug Bell@discussions.microsoft.com> wrote in message news:8766BC4D-D3CD-4B4F-AF7B-FD145DE56205@microsoft.com... > Hi, > > I am trying to set up a cell so it has a minimum value. > > The current formula is: =a1/a2*5 which gives the answer 0 but i need to > show > a minimum value of 1 is this possible? > ...

Matrix Problem
I was trying to match a matrix division, that was written in another code. Matrix A = 1 1 1 1 0 0 0 0 1000 100 10 1 0 0 0 0 0 0 0 0 1000 100 10 1 0 0 0 0 4096 256 16 1 3 2 1 0 0 0 0 0 300 20 1 0 -300 -20 -1 0 0 0 0 0 768 32 1 0 60 2 0 0 -60 -2 0 0 Matrix B = 0.11413125 0.30555 0.30555 0.425 0.0085625 0 0.019908333 0 Matrix C = -0.000135571 0.003038657 0.002891898 0.108336265 8.5706E-05 -0.003599653 0.069275 -0.112940741 The calculation that I am looking at has Matrix B / Matrix A = Matrix C I was trying to use a function in excel to match matrix C, but I have been unable to do this...

Printer problem when OPENING reports
Access 2002. Opening a database from another user's machine, and when trying to open up a report, a dlg comes up saying that the printer doesn't exist. The printer it lists, is the one that I was hooked up to on my machine when I last printed the report. The person has his own printer, why wouldn't it just print to that printer. I don't have any code or settings that I know of that have made the report do that. That seems like me sending a word doc, and when you open it up, it says MY printer is not available. Of course not, you'd be printing it on YOUR printer....

Formatting cell for state abbreviations
I am unable to format cell to accept ME, the abbreviation for Maine. It continues to revert to a lowercase "E" as in "Me" while other states such as NH, VA and LA maintain their uppercase second letter. What am I doing wrong? Thank you -- LovesArt247 This is a AutoCorrect item for people who mistype the word "Me" (as in me myself) Tools | Autocorrect; locate and remove this entry -- remember it will effect all Office apps OR: after Excel gives you Me, use CTRL+Z to undo best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme rem...

Excel 2003, when I click on a particular cell it gets deselected
While using Excel 2003, when I click on a particular cell, within 30 seconds, that particular cell gets deselected followed by the workbook. So each time I need to click either the cell or workbook to enter data in that particular sheet. There could be event code that is being fired. Does this happen if you open the workbook with macros disabled? HTH, Bernie MS Excel MVP "towinwin" <towinwin@discussions.microsoft.com> wrote in message news:B7B87AFB-27AC-4235-9AE3-5E7A46310AF8@microsoft.com... > While using Excel 2003, when I click on a particular cell, within 30 se...

PC Charge Problem
We have been having a problem with double charging in some instances. We swipe the card and the PC Charge processing screen gets hung up waiting. Sometimes it cancels itself, sometimes it takes a while and we need to cancel it. We then reswipe and it goes through. What ends up happening is that sometimes the first swipe ends up getting charged to the customers account, but does not get registered as a sale on RMS (the second swipe does). Then, once the customer gets their bill and notices the double charge, I need to refund the card, and this gets relected in my sales on RMS. Has ...

how do I highlite text within a cell (specific characters)
I am trying to high light specific characters within a cell. (similiar to the way text can be high lighted in word. Can this be done in Excell. I dont want to change the colour of the cell or the colour of the text - but do want to high light specific parts of the text with in a cell Just select that part of the text and format as per your requirement by going to cells > format > font. Mangesh "tim" <tim@discussions.microsoft.com> wrote in message news:A9781298-1301-4FE1-9901-9D1C03504504@microsoft.com... > I am trying to high light specific characters within a ...

oulook calendar problems
When I schedule meetings using oulook 2002 for eg at 10am the receipients are getting invitations for incorrect hours. This could be 11am instead 10 am. The computer time and the time zones are correct. Can someone please help? Daylight Savings Time match in Outlook and Control Panel? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, babu datt asked: | When I schedu...

Problem w/MathType and KaleidaGraph embedded objects
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I am having trouble with embedded objects Word 2008 from either of the above programs. Note that I do not have problems with embedded objects created with Office applications such as Excel. I'm not sure about other applications. Here is what happens: If I open a Word Document created with either Word 2003 or Word 2007 on windows that contains an embedded MathType equation or Kaleidagraph graph, when I double click to open it with Word 2008 I get an error that says "Word is unable to locate the server application...

Cell colors
In Format Cells (Excel 2008), there are 40 colors in the top five rows and 16 more colors in the bottom two rows. I see how to change the colors in the top rows (Preferences > Colors), but how do you change the ones in the bottom rows? Charles ...

Weird problem -- formulas get lost but their values stay in place
I have a client who uses Excel files that link out to other excel files. The formulas in the "main" file calculate from data in the linked files. So, the formula is in the main file. When they close the main file and re-open it, the formulas are gone, but the last calculated value remains. Does anyone have an idea why this would happen? Thanks, Bill One way might be because a "Before Save" or "Before Close" Macro is doing a Copy > Paste special > Values........ Vaya con Dios, Chuck, CABGx3 "Bill" <bdotson@gmail.com> wrote in message...

XML serialization problem #2
Hi all, I'm a .NET beginner and I've got a problem on a program where I try to do an XML serialization. I get the following error : "An unmanaged exception of type 'System.IO.FileNotFoundException' occured in mscorlib.dll Additonal information : the file or asssembly named n9gu4-bo.dll or one of its dependencies cannot be found" Here is the code : (I've uploaded the complete project at this url http://netorld.free.fr/Interface2.zip) public class Form1 : System.Windows.Forms.Form { .... .... public Form1() { InitializeComponent(); try { TextReader reade...

excel find problems
I have a database in excel with 4 sheets. When I want to conduct a search, it only searches the active sheet. Therefore I have to select each sepearte sheet and carry out 4 individual searches. Is there any way to search all sheets at once? If there is not, I am aware I can create a user form and use the approproate syntax to conduct my required search. However this will take me a long time. If there is an easier apprach then please let me know. Regards camron What version of excel? How are you searching? In later versions of Excel, you can select multiple sheets and have Edit|Fin...