changing cell reference via another cell

I have a spreadsheet with a tab (called calc) containing columns of 
calculations.  The next tab (called D) contains a diagram which pulls numbers 
off of column D of the calc tab.  I copy tab D to a new tab (by right 
clicking on the tab and selecting copy) and I rename the new tab as E.  I 
want the cells in tab E to reference column E in the calc tab.  Currently, I 
do a Find/Replace, changing 'calc'!D to 'calc'!E.  Is there a way I can 
automate this so I don't have to do the Find/Replace.  I was hoping I could 
just type E in a cell on tab E and it would be set up to automatically change 
'calc'!D to 'calc'!E in all relevant locations on tab E.  Or better yet it 
would look at the name of the tab and automatically change to that when I 
change the tab name to E.  So can I set up the cell references to do this?  
Thanks.
0
Utf
5/6/2010 3:53:02 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
590 Views

Similar Articles

[PageSpeed] 50

You can use the indirect function. You can look it up in the functions help. 
Issues with doing that are taht your formulas may not be draggable requiring 
you to write every formula by hand. Also Indirect is vaoltile meaning that it 
has high calculation overhead and could slow your spreadsheet down.
-- 
HTH...

Jim Thomlinson


"chicolini" wrote:

> I have a spreadsheet with a tab (called calc) containing columns of 
> calculations.  The next tab (called D) contains a diagram which pulls numbers 
> off of column D of the calc tab.  I copy tab D to a new tab (by right 
> clicking on the tab and selecting copy) and I rename the new tab as E.  I 
> want the cells in tab E to reference column E in the calc tab.  Currently, I 
> do a Find/Replace, changing 'calc'!D to 'calc'!E.  Is there a way I can 
> automate this so I don't have to do the Find/Replace.  I was hoping I could 
> just type E in a cell on tab E and it would be set up to automatically change 
> 'calc'!D to 'calc'!E in all relevant locations on tab E.  Or better yet it 
> would look at the name of the tab and automatically change to that when I 
> change the tab name to E.  So can I set up the cell references to do this?  
> Thanks.
0
Utf
5/6/2010 4:43:01 PM
To get the current sheet name into a cell:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,999)

Assuming this is in cell A1, your other formulas would then be something 
like:
=INDIRECT("Calc!"&A1&":"&A1)
This would reference Calc!E:E

-- 
Best Regards,

Luke M
"chicolini" <chicolini@discussions.microsoft.com> wrote in message 
news:296BE8D7-A5CE-478F-9122-82653DED67AE@microsoft.com...
>I have a spreadsheet with a tab (called calc) containing columns of
> calculations.  The next tab (called D) contains a diagram which pulls 
> numbers
> off of column D of the calc tab.  I copy tab D to a new tab (by right
> clicking on the tab and selecting copy) and I rename the new tab as E.  I
> want the cells in tab E to reference column E in the calc tab.  Currently, 
> I
> do a Find/Replace, changing 'calc'!D to 'calc'!E.  Is there a way I can
> automate this so I don't have to do the Find/Replace.  I was hoping I 
> could
> just type E in a cell on tab E and it would be set up to automatically 
> change
> 'calc'!D to 'calc'!E in all relevant locations on tab E.  Or better yet it
> would look at the name of the tab and automatically change to that when I
> change the tab name to E.  So can I set up the cell references to do this?
> Thanks. 


0
Luke
5/6/2010 5:05:09 PM
Hi

Since your tab names are only a single character, then you could set up 
a named range called myRange, just utilising the rightmost character of 
the filename

Insert>Name>Define>
Name			myRange
Refers to	 
=EVALUATE(RIGHT(CELL("filename",!$A$1))&"1:"&RIGHT(CELL("filename",!$A$1))&"65536")

This would produce a range from D1:D65536 on Sheet D and E1:E65536 on 
Sheet E.
Make the ranges shorter if required.

Then
=INDEX(myRange,10)
would give the value from row 10 of the relevant column for the sheet.
--
Regards
Roger Govier

chicolini wrote:
> I have a spreadsheet with a tab (called calc) containing columns of 
> calculations.  The next tab (called D) contains a diagram which pulls numbers 
> off of column D of the calc tab.  I copy tab D to a new tab (by right 
> clicking on the tab and selecting copy) and I rename the new tab as E.  I 
> want the cells in tab E to reference column E in the calc tab.  Currently, I 
> do a Find/Replace, changing 'calc'!D to 'calc'!E.  Is there a way I can 
> automate this so I don't have to do the Find/Replace.  I was hoping I could 
> just type E in a cell on tab E and it would be set up to automatically change 
> 'calc'!D to 'calc'!E in all relevant locations on tab E.  Or better yet it 
> would look at the name of the tab and automatically change to that when I 
> change the tab name to E.  So can I set up the cell references to do this?  
> Thanks.
0
Roger
5/7/2010 9:21:16 AM
Reply:

Similar Artilces:

Changing Item Tracking To Enable Serial Number Tracking
Through GP you cannot change the tracking option if an item has any on hand or on order quantity amounts. Has anybody changed the tracking option outside of GP and can tell me the other steps that need to be taken to build the serial number inventory? Thanks -- Charles Allen, MVP Charles: First, you need to do a decrease adjustment to move the items out of stock. The, change the ITMTRKOP field in IV00101 to '2' for those items you want to track. Finally, perform an increase adjustment to bring the material back in and record the serial numbers. Then I'd run reconcile on ...

How do I copy data from one workbook to another whilst retaining formulas on original
I'm looking to run a macro from workbook SWT (sheet CLT) which will cu and paste data from a range of cells (A2,C2,E2,F2,G2,I2,K2.. A3,C3,E3,F3,G3,I3,K3... A4,C4,E4,F4,G4,I4,K4.... until end of data from workbook SWT (Sheet CS) to rows A to G in Workbook SWTS (Shee Stats). I have vlookup formulas (eg =IF(B2="","",VLOOKUP($B2,'Data Field'!$A$7:$B$12,2))) in place in columns A,C,E,F,G,I,K on sheet CS and I'm looking to keep the these formulas in place (unless someone ca think of better alternatives). When I paste the data on sheet Stats i must paste onto the...

formula change problem #2
The columns that the formula is referencing can change locations on th spreadsheet, if a user inserts a new column. To simplify the questio I put the actual column numbers in the formula but actually I will hav an integer defined that will hold the column number. That integer wil get populated by grabbing the column number from a named cell in tha particular column. I hope I explained that good enough. I was using the offset's in the formulas because that is what I am mos familiar with. Can offset's not be used in formulas? Is there a better way? Thanks -- cparson ---------...

excel spreadsheet 2003 wont show content when moved to another par
i have backed all documents which included excel spreadsheets from c drive to d drive (ANOTHER PARTITION). Now i cannot see the content of the spreadsheet when i access it from the D drive or C drive. what have i done to it? It also wont open when itry to access it from backed up USB stick. Thanks Chris I had a similar issue with some files. What happened was that somehow the windows for the files got minimized or dragged out of view somehow (not sure how this happened). Users would open a file and see nothing. This fixed it for me. Goto the Window item on toolbar, S...

[P2007]
Hi, I have a PM user who has just changed the login account password in AD and after that he is no longer able to login to PWA, an access denied message will show up instead. Even if I assign him as an administrator, he is still getting the same problem. He has no issue using Project Professional (with his account) to connect to the project server, it is just the PWA that he is unable to access. Did anyone encountered the same issue before? Regards Godrid Godrid: Most likely his desktop is sending the incorrect credentials. Verify that the user didn't save the passw...

company name change
Hello Our company has recently changes names. We already have two email domains currently in use, one set as primary for everything and another which was used in the past. We have one defined SMTP address, which is primary for all users. We also have another which was used int he past, but is not listed as a SMTP address in EXCH system mgr, recipients policies. Of course, I have a new domain to add as our primary smtp address and will need to make the current smtp address secondary for users. In EXCH system mgr, I do see CCMAIL and MS properties which appear to be our old email domain...

how do i change the color of a cell auotmatically?
I am desiging a data shee and i want to changes the color of a cell based on the valuse of anthor cell. ex. if "cell m34" is equal to 0 then "cell p21" is white, if cell m34 less than 1548, then "cell P21" is yellow Can this be done? If so, please help me out. Hi! Select cell P21 Goto Format>Conditional Formatting In the dropdown select Formula is Enter this formula in the box: =AND(M34<>"",M34=0) Click the Format button and select the style to apply then click OK. Click the Add button In Condition 2 Formula is: =AND(M34>=1,M34<=154...

Is it possible to have text and currancy in one cell and ...
Is it possible to have text and currancy in one cell and be able to use the numbers in that cell to make an equation with another? The short answer .. No -- Rgds Max xl 97 --- Please respond in thread xdemechanik <at>yahoo<dot>com ---- "thegen" <thegen@discussions.microsoft.com> wrote in message news:1552FF10-9CAA-42AD-B401-B0D229D6A343@microsoft.com... > Is it possible to have text and currancy in one cell and be able to use the > numbers in that cell to make an equation with another? well maybe depending on how the "text & currency" was ar...

How to skip cells with text strings.
What should I add to the code below to cause it to skip a cell tha contains a text string in the cell above it? I need it to only work o numbers in the cell above it. If ActiveCell.Offset(rowoffset:=-1) <> "" Then ActiveCell = ActiveCell.Offset(rowoffset:=-1) + 1 Application.SendKeys ("{Enter}") End If Thanks so much. mikebur -- mikebur ----------------------------------------------------------------------- mikeburg's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2458 View this thread: http://www.excelforum.com/showthread.php?thread...

Diffrent cell on diffrent date?
I�m going to TRY and explain this the best I can. For work when we do our cash out at the end of the night we use a exce sheet. This sheet is to add up our change, checks, credit cards etc etc. etc. When we're done we print it out and than type the figure into another excel sheet. I pretty much know how to combine the sheets so instead of typing the numbers into SS1 and than entering i into SS2 I can have the numbers going from SS1 to SS2 automatically But my question is that how do I set it up so when I type the info i SS1 that it will go to in to SS2 but to have the cells change by dat...

How to Route Outbound E-mail from the Exchange Server 5.5 to another server.
Hi. I'm implementing an e-mail filter software and I need to configure my Exchange Server to route outbound e-mail to the e-mail filter server. I went to the connection tap and changed the message delivery to : Forward all message to host : "My e-mail filter server ". I need to know if what I've done is correct ? Thank you for your help. Yes, that's where you make the change. Don't forget to restart the IMS in order to affect the change. -- hth, SusanV "Javier" <jbisono@tricom.com.do> wrote in message news:uPVHjhl9DHA.1472@TK2MSFTNGP11.phx.gbl...

Inserting a value based on a calculation from another sheet
Hi all I have a sheet that performs a series of calculations based on a single value and culminating in another single value. Also the series of calculations is too complicated and long and numerous to do in a single calculation. What I have is, in another sheet, I have a range of that single initial value, from -90% to +90% of the initial value, and I'd like to somehow calculate that value resulting from all the various initial values and insert them in the first sheet...how can i do that? for example: sheet A contains the range of initial values and the result of the calculations per...

Excel Formula Guidance. Formula need to determine if cell is popul
Hi, Hopefully someone will be able to provide some guidance. I have been tasked with setting up some spreadsheets at work to application coming into my department and the number of days it takes for us to deal with them. To work this out is easy enough using formula =NETWORKDAYS(B4,E4,$A$119:$A$158) with the numbers involved with the $ referencing bank holidays listed in the spreadsheet and B4 received date and E4 being the completed date. I now need to include some extra lines as I need to be able to monitor written enquiries as well so the headings would read Application ...

changing calendar appearance
I can not get the calendar day of the week to start on Sunday. Version of Outlook? Are you using Compressed weekends? You need to disable compressed weekends if you want Sunday at the beginning of the week. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: mailto:EMO-NEWSLETTER-SUBSCRIBE-REQUEST@PEACH.EASE.LSOFT.COM New Poll: What type of email acc...

Why does Outlook display text in yellow? How can i change it?
When pasting text into an e-mail, it appears in pale yellow. Sometimes, parts of meesages from others are also yellow. How can I prevent this? Are you using stationery by any chance? -- Kathleen Orland "Wise Dog" <Wise Dog@discussions.microsoft.com> wrote in message news:2D2FBAF7-2B2B-4C66-AE33-B2CA5A51B821@microsoft.com... > When pasting text into an e-mail, it appears in pale yellow. Sometimes, > parts of meesages from others are also yellow. How can I prevent this? ...

Synchronising Two Instances of Outlook Via PocketPC (ActiveSync)
Win2kPro SP4, Outlook 2002 SP3, ActiveSync 3.7, PocketPC CE V3.0 I synchronise calendar, contacts and notes between two PCs via my PocketPC. The work computer uses Exchange and therefore meetings have invitations to other people in the organisation. The home computer is stand-alone. On syncronising, all meetings with invitations become unresolved in the sync process with the following message: "Microsoft Outlook does not recognize "[my Exchange name]". ...If you used a comma to separate several addresses: click Cancel, change the commas to semicolons, and try to send again.&qu...

Coping numbers to next blank cell on another worksheet
I am just learning Excel (need SIMPLE help) but any help would be greatly appreciated. My OS is XL Professional and I have MS Excel 2002 SP2. I need to copy calculated numbers (I got this much done on my own) in specific cells on worksheet # 1 to the next blank cells in existing columns of numbers used in calculations on other worksheets in the same workbook. I other words, I have a formula that calculates numbers and places the results in the same column and row each time it is used. Those calculated numbers need to be copied and used on different worksheets in the same workbook. While...

Changing the data label position below the chart axis.
I'm charting some percentages. Many of them are negatives, but not all. The Y axis in my chart is more in the middle, instead of on the bottom, as with normal charts that have all positive numbers. The data labels are overlapping some of bars in the chart that are negatives. I can't get the data labels to move any further south. I've already changed the 'data label distance from axis' to the maximum of 1000. The data labels are still overlapping. Is there anything else I can try? The chart looks terrible with the labels partially on the negative bars, please help! H...

can't change the data type or field size
I am creating/editing updating fields in table design. I am trying to change the field size of the first field. But getting message.. 'You can't change the data type or field size of this field, it is part of on or more relationships. I deleted the relationship and still get the message. Can I screw anything up if I delete any other relationships. Or does access re-connect all relationships on demand. Thanks for any replies. On Sat, 9 Jan 2010 17:12:01 -0800, Steve Stad <SteveStad@discussions.microsoft.com> wrote: >I am creating/editing updating field...

How to save custom cell formatting?
When I goto Format - Cells - Custom and type my own format. For e.g. #,###,##0.00000 The format mentioned above is available for current file only. How do I make it available to other files (and new ones) as well? ...

Change amount from number to figure form
I am making appointment letter for employees using mail merge option. In appointment letter I have to put salary in number form i.e. 121000 and also in figures i.e. One lakh twenty one thousand. I have a coloumn in excel where I have all the salary package in number form. Is it possible, for excel to automatically convert this number form salary into figure ? Any assistance would be greatly obliged. Jai Numbers to words Bob Phillips' site for help on this. http://www.xldynamic.com/source/xld.xlFAQ0004.html or Bernd Plumhoff's site http://www.sulprobil.com/html/spellnumber.htm...

Need to access selective cells from Excel file stored at Sharepoin
I need to link my PPT 2007 slide with an Excel 2003/2007 sheet data stored at Sharepoint 2007. Excel sheet is having lots of cells/rows - hundreds, but i want only selective ranges to be visible in PPT slide. Moreover, if can apply some calculations prior to rendering the date, is also required. Any suggestions please? ...

how do i copy rows to a new worksheet by a cell value
Hi, I have a worksheet with many rows of values. Where a cell in a particular column is "Yes" I wish to copy the values of that row to a different worksheet. Is this something suited to a macro? Many thanks for any assistance. Nibbs without a macro, use data>filter>autofilter>filter on yes>copy -- Don Guillett SalesAid Software dguillett1@austin.rr.com "Nibbs" <Nibbs@discussions.microsoft.com> wrote in message news:144D7242-BAFC-43B4-98EA-EC95657832B2@microsoft.com... > Hi, > > I have a worksheet with many rows of values. Where a cell ...

How to change english numeric to arabic numerics?
I changed the language already, I can type arabic in MS word 2007, but arabic numerics is not coming, If MS word 2003 it was very easy to settle. ...

loop thru cells to find matching record
I'm having difficulty with the following code. I think thre are several issues, perhaps the easier one first. When I set the following range, I noticed it didn't start at row 14 but at row 12, not sure why? Set rng = wsSheet.Range("A14:A" & Cells(65536, "A").End(xlUp).Row). I noticed that the message box shows cells I'm not interested in. The more difficult problem is per the following The active workbook, "sheet 1" has a lising of serial numbers, the serial numbers start at row 14, the exact number varies, hence the set rng as per abo...