referencing cell contents, after deleting and shifting new contents into cell

Greetings all,

on Sheet 2 of my workbook I reference some cells from Sheet 1.   ie.
"=Sheet1!D3"

I have Sheet1 setup as a schedule.   Since sheet1 is a schedule, its columns
are days of the week.. and I constantly add days to the end of it (Columns
F,G,H,I..etc..etc).  When today is over, I delete columns A and B, and
columns C and D shift over and become the new A and B.

However on sheet 2, the reference is lost even though new values are shifted
over into Columns A & B.

Is there a way for the Sheet2 to keep the referencing A and B from sheet1
once the columns in Sheet1 are deleted and new information is shifted to
into them?

Thanks in advanced,

HM






0
anon4665 (2)
2/6/2004 10:17:02 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
467 Views

Similar Articles

[PageSpeed] 17

You can try use 

=INDIRECT("Sheet1!D3)

Davi

--
Message posted from http://www.ExcelForum.com

0
2/6/2004 10:56:02 PM
Reply:

Similar Artilces:

Number of characters in a cell
I want to count the number of characters in a cell. I have been told there is a way/formula you can use, rather than importing the table into word and using word count. Is there formula, if so what is it. =LEN(A1) -- Regards, Peo Sjoblom "Bonny" <anonymous@discussions.microsoft.com> wrote in message news:C900F604-3E15-4F40-A1A8-87259225042D@microsoft.com... > I want to count the number of characters in a cell. I have been told there is a way/formula you can use, rather than importing the table into word and using word count. Is there formula, if so what is it. ...

Names and numbers in different cells
I am trying to add the totals given in Columns C, E, G etc etc of each suburb listed in columns B,d,F etc etc. When the formula finds the total is also needs to give me the name in column 1 for that area. (Two areas given in example but not all week) Eg: Find "PNS Sound Hutt" in column B, & column D, add 2.0 (from column c) & (1 from column e) then show John. I need to know how often each person visits each suburb. Name Suburb No Suburb No A B C D E etc etc John MONDAY ...

Cannot Delete #2
Howdy- I cannot delete anything from Outlook 2000 at all. When I try I get the lovely "The messaging interface has returned an unknown error. If the problem persists, restart Outlook." message. What to do? >-----Original Message----- >Howdy- > >I cannot delete anything from Outlook 2000 at all. When I >try I get the lovely "The messaging interface has returned >an unknown error. If the problem persists, restart >Outlook." message. What to do? >. >Try Selecting/highlighting the email then press Shift- Delete (together). Mahonek H...

Referencing merged cells
I'm having trouble referencing a merged cell in another workbook. Say I merge cells A1 to C1 in workbook 1. When I make this the active cell, the Name Box says "A1". When I go to another workbook (say, workbook 2), go to cell B1, type "=" and then go to the merged cell and select it, I get 'workbook1'!$A$1:$C$1 as the formula and, of course, the "#VALUE" error. I've successfully tried typing "=sum(" without the quotes hen clicking on the contents of the cell and then adding the ")" and it works O.K. but there should be an easi...

Highlight a Cell When Working in Another Cell
If I am working in, say, cell B5, is there a way I can have cell G5 highlighted? When I am inputting numbers into column B, there are instructions in column G that I would like to have highlighted so that the instructions for that particular row are easier to read. I can't think of a good way to do this. I'd appreciate any ideas. Thanks! Private Sub Worksheet_SelectionChange(ByVal Target As Range) Cells.FormatConditions.Delete With Target If .Column = 2 Then .Offset(0, 5).FormatConditions.Add Type:=xlExpression, Formula1:="TRUE" Wi...

Publick Folder Content Removal
I am using Exchange 2003 Standard on a Win3K Standard edition server. I have a folder in the 'Public Folder' area that I have the users move all the e-mails (spam) they want blocked. I have now almost 50,000 e-mails in the folder that I've already processed, how can I delete the entire content of the folder without having to delete 4,000 at a time? Thanks for your help. -- :: Lohan Roberto :: :: YoungAdmin :: You can set Age Limits on the properties of PF through Exchange System Manager. FYI: I would suggest looking GFI Mail Essentials www.gfi.com which does a grea...

Show dynamic content in dialog box
I want to achieve the same effect as a tab control but I don't want the tabs at the top to be visible... so the shown form is switched programmatically. Any advice or pointers are welcome ! Thanks Mike You would pretty much do the same thing as you would with tab. Place all the controls for each group inside a seperate child dialog. Then simply show and hide the different dialogs in the main dialog. AliR. "Mike Gleason Jr Couturier" <mikejrMAPSON@videotron.ca> wrote in message news:enxUBEH5EHA.1120@TK2MSFTNGP11.phx.gbl... > I want to achieve the same effect as &...

Shift + arrow to highlight and copy cells
Normally, I like to use the keystroke method of copying cells by copying a cell, then hold Shift down while I do the arrow key. (it's easier on my wrist than hold and dragging). But, for some reason, now when I hold the Shift and do the arrow, it is not moving. Anyone know what happened? -- -- BPod Never mind. I've got an answer now. Mike H. answered me in the General Excel questions. Way to go, Mike! -- BPod "podbay" wrote: > Normally, I like to use the keystroke method of copying cells by copying a > cell, then hold Shift down while I do the arrow key. (i...

cells protection
Dear sir, with my regards, i have a workbook with about ( 10 sheets) full of formulas,to send these formulas away from spongers , i protcted some cells or columns which contain them, when i want to work on any sheet i cancell the protection and forget to protect again . Is there any way in excell 2003 to protect these cells directly when i close the sheets. Thanks in advance. -- tofimoon3 Sure with VBA. Place this code in a workbook module, if you don't know where that is, check out this site http://www.contextures.com/xlvba01.html#Workbook Place this code there Private Sub Workbo...

Copy value from cell with custom format
Hi, I want to copy values from cells with a special custom number format. i.e. the cell value is displayed as 1060028 but it's literal value is 28. When I look at the number format in "format cells" it says the number format is 10600##. If I copy this cell from one to another even using "paste special values" it only returns 28. How can I copy the cell value 1060028 as a complete string? Would I use a function or would I have to use some VB script? thanks. The format should copy along with the value. Just use Paste, not PasteSpecial -- Gary''s Stud...

Hide Row If Any Cell In It = 0
Hello All Searched high and low amongst the forums for this and found some close solutions, but can't seem to make any work. I have Rows 1-100 & Cols A-J all with formulas or text in them. If the result of any of the formulas in any of the that range is 0, I want the entire row hidden. Cheers Hi Try this: Sub aaa() Dim targetRange As Range Dim tRange As Range Application.ScreenUpdating = False Set targetRange = Range("A1:J100") For Each r In targetRange.Rows Set tRange = targetRange.Rows(r.Row) For Each cell In tRange.Cells If cell...

Cells(my_crt_row,my_crt_col+1) crashes for my_crt_row>65536
I tried this in Excel 2007, where the worksheets may have 1M rows. Why does this crash? In Excel 2000 to 2003, this thing worked fine for my_crr_row<=65536, as I expected. But now, having more rows in a sheet, why does this not work for a larger row number? Hi Bede, If you're running in 97/2003 compatibility mode (as you must if the wb is in xls format), you're still limited to 65536 rows and 256 columns per sheet. -- Cheers macropod [Microsoft MVP - Word] "BEDE" <BEDE@discussions.microsoft.com> wrote in message news:527B2833-CEE3-41E4-9BD7-3...

How do I start a new break so that I can have a different header?
I have a word 2000 document here that I need to have 3 different headers for each different section eg: Test Report - Tested to Standard 'A'/Standard 'B'/ Standard 'C' It's a very long document so it isnt practical to head up each page using anything but headers as there is alot of information and certification logos within each header part. Any help on this is very much appreciated. Insert a Section Break (Next Page). Then you get a new set of up to three headers. On Feb 23, 4:29=A0pm, Jalanda <Jala...@discussions.microsoft.com> wrot...

Contents of cell in footer #2
I want the contents of a specific cell to be in a footer, can I do this? This code, placed in the ThisWorkbook code module, automatically picks up the cell value when printing. Private Sub Workbook_BeforePrint(Cancel As Boolean) ActiveSheet.PageSetup.LeftFoot�er = Activesheet.Range("A8").Value End Sub -- HTH RP (remove nothere from the email address if mailing direct) "dbhenkel" <dbhenkel@discussions.microsoft.com> wrote in message news:6952C152-C970-45CD-A9BC-126C7645C389@microsoft.com... > I want the contents of a specific cell to be in a footer, can...

I have deleted my recycle bin by mistake..How do I get it back??
I have deleted my recycle bin by mistake..How do I get it back?? TIA Regards Paul "Shiner" <me@ntlworld.con> wrote in message news:9E721A39-703F-4847-97E4-5A1F2C772FD6@microsoft.com... > > > I have deleted my recycle bin by mistake..How do I get it back?? > TIA > Regards Paul > You`ll find the answer here Paul. http://blogs.techrepublic.com.com/window-on-windows/?p=541 bw.. computer keylogger - Auto Keylogger records everything that is entered from the keyboard, to an encrypted file.for more information pls visit http://www.d...

color in cells 2
hi i am haveing trouble getting what i need I have a spreadsheet and in one cell say in the column i would have 3 choses "A" "B" "C" and when i apply the letter "A" i would like it to trun red but if i was to apply the letter "B" then i would like the letter to turn green. i can get the letter to trun red but how can i get other letters to turn different colors sorry i am slow but i hope you can help joe --- Message posted from http://www.ExcelForum.com/ Hi jladika! Keep to the same thread. After OK of your f...

Referencing another field if result is N/A
Hello, I have a situation where I am referencing data on another workbook. The reason behind this is the data in Column H can be entered incorrectly and often is. I am able to prevent this going forward but can not do so for the items already entered. I enter a formula via VBA, however, sometimes the result will be #N/A and if so I would prefer to leave the old value and just highlight it that it needs to be verified by hand. I am not sure if I can just "leave" the value as it has pretty much already been replaced. Due to this, I copy the column to a different colum...

Word Opening in new window everytime
I would like to open MS word within the same window everytime =96 however my version opens a new window everytime I click on the Word Icon. <codeweb@gmail.com> wrote in message news:fc6f47a6-f390-4aa1-a366-053167c1fde2@g19g2000yqe.googlegroups.com... > I would like to open MS word within the same window everytime � > however my version opens a new window everytime I click on the Word > Icon. > > So it should. What EXACTLY are you trying to do? If you are using Word 2007, clear the "Show all windows in the Taskbar" option in Office butt...

Can't Add/Delete (and More) in Report Design
Hi all, there is probably a really simple solution to this, but I cannot seem to figure it out. In the Design Mode of one of the reports I am working on, I cannot add/delete fields from a report. If I right- click on any field, other options are also greyed-out; such as copy/ paste/cut/build/hyperlink. I can move the fields around and edit their contents, etc. Anyone know how to fix this? Thanks in advance! If all else fails try copying/cutting and pasting the fields into a new report. It's possible the report somehow got corrupted and once that happens it's sometimes better jus...

Speak Cell command
Is it possible to get Excel 2007 speaking out a cell contend in a language other than English? Phonetically as usual: For example to “say” Good Morning in Japanese, try something like: O hey you gozaimasu -- Gary''s Student - gsnu200803 "renatoabc" wrote: > Is it possible to get Excel 2007 speaking out a cell contend in a language > other than English? ...

Assign a value to a cell
I'll apreciate your help with this in A1 i have a value in days that is used to make several calculations but I need it to change if a specific condition is given something lik this: A1=300 If b1<a1 then a1=b1 where b1 is a value calculated somewhere else. Thank you for your help. Rodolf -- Message posted from http://www.ExcelForum.com Hi try the following formula in A1: =MIN(B1,300) Frank > I'll apreciate your help with this > > in A1 i have a value in days that is used to make several > calculations, but I need it to change if a specific condition is > giv...

How do I change a cell range with a reference cell?
How do I change a cell range with a reference cell? i.e. cell H220 changes from =sum(G220:G229) to =sum(G220:G235) by changing cell A1 from 10 to16? =sum(offset(g220,0,0,a1,1)) "Danneskjold" wrote: > How do I change a cell range with a reference cell? i.e. cell H220 changes > from =sum(G220:G229) to =sum(G220:G235) by changing cell A1 from 10 to16? Thanks Duke. "Duke Carey" wrote: > =sum(offset(g220,0,0,a1,1)) > > "Danneskjold" wrote: > > > How do I change a cell range with a reference cell? i.e. cell H220 changes > > from ...

Worksheet doesn't permit F4 absolute referencing tool in formulas
I've never seen this before in a worksheet and can't find a Tool-Option that would change it. You can of course type the dollar signs in, but the F4 key won't toggle. Sure it's something simple, any help appreciated! -- ronthedog ------------------------------------------------------------------------ ronthedog's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26504 View this thread: http://www.excelforum.com/showthread.php?threadid=397725 ...

Finding data in a unknown cell
Hi, hope someone can help. I have a spreadsheet which has items and their respective quantitys in 2 simple columns. The item names are consistent and do not change, however the items and their position within the spreadsheet changes everyday as it is taken from an external source. I can find whether ITEMx exists easy enough with the COUNTIF function*, however since it's position will change everyday I can't put in a static formula of it's quantity since I won't know the cell it's in (I don't want to do it manually). Basically is there a function to display a cell to t...

This workbook is currently referenced by another workbook and cannot be closed.
The message is given when I try to close a workbook / kill Microsoft Excel. I'm using Microsoft Excel 2002 / XP. The spreadsheet includes a reference to another sheet that someone else referenced in another message here. I put the AddIn sheet in via: Tools, AddIns..., Analysis ToolPack - VBA. I've not referenced any function or procedure in the add-in. I get a Triangular Warning / Error ? It's got a graphic of a Yellow Triangle with an Exclamation Mark inside. The subject is the exact text of the warning. A Microsoft Search on the subject yielded nothing. Google yielded one ...