relative references when copying Sheets containing form controls

I've been given a spreadsheet that I need to take copy various sheets
from, the sheets contain form controls, when I copy the sheet to a new
workbook, named ranges and so on come across fine, but the references
to the input ranges of for example the drop down box controls become
hard coded to the original sheet, I don't want this..

Is there anyway to alter the way excel copies so that the references
remain relative rather than absoloute????
0
2/20/2004 4:40:18 PM
excel 39879 articles. 2 followers. Follow

2 Replies
400 Views

Similar Articles

[PageSpeed] 3

What did you include in the addresses for those references?

I put a listbox from the forms toolbar on a worksheet.

If I used a range like:  $b$1:$b$10 as the Input Range and $A$1 as the Cell
link, then I could Edit|Move or Copy Sheet|Copy the worksheet and the input
range and cell link traveled with the new sheet.

But if I used a range like sheet1!$b$1:$b$10 and sheet1!$a$1, then the listbox
input range and cell link pointed back at that original worksheet.

(I used xl2002 in my test.)



tom wrote:
> 
> I've been given a spreadsheet that I need to take copy various sheets
> from, the sheets contain form controls, when I copy the sheet to a new
> workbook, named ranges and so on come across fine, but the references
> to the input ranges of for example the drop down box controls become
> hard coded to the original sheet, I don't want this..
> 
> Is there anyway to alter the way excel copies so that the references
> remain relative rather than absoloute????

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
2/21/2004 12:14:33 AM
Thats the problem, in the original workbook, to keep things tidy the
references are stored in cell ranges on hidden sheets. Even though I
copy over both sheets, i.e. the one with the controls on and the one
with the references, the links still become absoloute.

Thanks for trying though.

I'll chat to the person who gave me the sheet and see if I can't just
copy the entire book and then delete things rather than copying things
into a blank workbook.
0
2/23/2004 9:19:50 AM
Reply:

Similar Artilces:

Compare records in multiple sheet -> report
Hi! I have a workbook consisting of ten sheets. To simplify my question let�s say that the three first columns of every sheet denotes the spatial coordinates x,y,z and the fourth column is a scalar value. Some x,y,z-triplets exist in all 10 sheets, some exists in only a few sheets, if the triplet exists, then also the scalar value of the 4th column exists. What I would like to do is to find all unique x,y,z-triplets and show them in the first column of a new sheet. In columns 2-11, I would like to show the scalar value(from the corresponding x,y,z-triplet of course) in column 4 in s...

Control size of chart
Hi again, I'm trying to use VBA to: - copy a chart in a chart sheet - paste it as an embedded chart in a new sheet - resize (and format) the chart - copy the embedded chart to the clipboard - delete the new sheet This is the code I've come up with so far: Sub CopyChart() ActiveChart.ChartArea.Select ActiveChart.ChartArea.Copy ActiveWorkbook.Sheets.Add ActiveSheet.Paste ActiveSheet.ChartObjects("Chart 1").Activate With ActiveChart.Parent .Height = 252.75 .Width = 342.75 ActiveChart.PlotArea.Height = 205 ActiveChart.PlotArea.W...

Details from 52 worksheets into one master sheet Help please
Hi Gord Dibben has given me this formula to take the details from 52 weekly sheets and give me a total sheet for the year and it works very well (Thanks Gord) If sheets are not named Week1 through Week2, you can insert a dummy sheet to the right of Totals sheet. Name it Start. Add another dummy sheet after last sheet. Name it End. Formula in Totals =Sum(Start:End!G25) Second method allows you to insert new sheets between Start and End sheets as your weeks progress. Gord Dibben Excel MVP What I would now like to do is make one sheet and have all the weekly totals listed so I can...

Postback & Xml Control ViewState
Hi I have an Xml control that reflects a tree structure from a serialized object that is generated in the Page_Load event: If Not IsPostBack Then Dim ms As New IO.MemoryStream Dim ser As New System.Xml.Serialization.XmlSerializer(Content.Site.GetType) ser.Serialize(ms, Content.Site) Xml1.DocumentContent = System.Text.UTF8Encoding.UTF8.GetString(ms.ToArray) End If The transform source is static and therefore set at design time. The problem i have is that when the postback occurs the Xml control does not maintain it's transformed document. I cannot perfor...

HELP: how to evaluate a filename when looking up another sheet?
I hope someone can help here. Suppose I have a document called 'sheet1.xls'. Now I have another document master.xls, from which I want to reference certain cells in sheet1.xls - but next week it will be sheet2.xls, then sheet3.xls and so on. I would like to set aside one cell in master.xls to point to sheet(n).xls - whichever is appropriate, so I can then apply a formula to the cells in master.xls, e.g. ='[sheet1.xls]TABLE1'!$A$3 ='[sheet1.xls]TABLE1'!$A$4 etc. Except that I don't want to point to sheet1.xls, I want to evaluate the string to point to a file b...

Absolute->relative link
Hello all, here's my problem: I created a vba code that copy from a template 2 sheets and paste them into a new workbook. But in the sheet #2, there's some formulas like this: ='Valeurs NCAP'! F3-$C3 where "Valeurs NCAP" is sheet #1. Once the sheet copied in the new workbook, the link became: ='[Original_workbook.xls]Valeurs NCAP'!F3-$C3 How can I break this absolute link and transform it in relative link to only keep ='Valeurs NCAP'!F3-$C3 ? thanks in advance, alex One way is to save that receiving workbook and then do edit|links and change sour...

Help about the default Check Box state of Date Time Picker Control!!
Hi, In my Dialog-Based MFC App, I used a Date Time Picker Control with Show None and Allow Edit properties. You know that default state of Check Box of this DTP control is 'Checked'. I need that default state of DTP control is Un-Checked when the main dialog shows. But it seems a pity that there is not any proper member function of CDateTimeCtrl for me to set up this Un-Checked state in the OnInitDialog() function. Any good ideas? Thank you in advance. Dave >In my Dialog-Based MFC App, I used a Date Time Picker Control with Show None >and Allow Edit properties. >You k...

Insert row into group of sheets
Hi, Can anyone spot the error in the following code snippet? What I want to do is insert a row in a particular place in a group of sheets - this works from the keyboard but not from this code: <<<<<->>>>> Dim sWard, sSheetName, sAddress, sFormula As String Dim iAnswer, iWardTotalRow As Integer ' Where are we? sSheetName = ActiveSheet.Name sAddress = ActiveCell.Address ' Get the name of the new ward Do Beep sWard = InputBox("Enter name of new Ward or 'Q' to quit", "New Ward") ...

Button on Datasheet form?
Hi, I am trying successfully to add an Edit button on everyrow of the record for user to edit current record on a Tabular form. This feature is turned off when I switch to Datasheet form. Is there an alternative way to put a button on Datasheet form to obtain view below? ID Name Position Action 1 AAAA Director Edit 2 BBBB Driver Edit ...... SF ...

How do i re-download my volume control?
I accidently deleted my volume control on my tool bar and i need it back...is there some way that i could re-download it or get it back? L, This is an Excel newsgroup, not a Windows group however... you could try: Start (button) | Control Panel | Sounds and Audio Devices | Volume (tab) | and checkmark "Place volume icon in the taskbar" Jim Cone San Francisco, USA "Luver_not_a _fighter" <Luver_not_a _fighter@discussions.microsoft.com> wrote in message news:4E699181-6E31-44E4-A435-985895D896F1@microsoft.com I accidently deleted my volume control on my tool bar ...

Building Form's Filter
Dear all, I have tables with this fields: expenses_control: expenses_code, profit_center, date, value. date: date profit_center: profit_center_code, profit_center_description expenses: expenses_code, expenses_description The relationship between the tables are: expenses_control.expense_code =3D expenses.expense_code expenses_control.profit_center =3D profit_center.profit_center_code expenses_control.date =3D date.date Now I'm trying to do a form to imput, to include and to exclude data/ values (not for consults!), like that: ------------------------------------------------------...

Macro to copy and paste
Hi all Excel Gurus, I need help. I have an excel sheet call template and i want to create a button to copy a selection of data which is B9:B34 and i need it to paste the information to another excel sheet called data which will be A:AA and add each row when pressed. When the data has been copied over i need it to close the data sheet. Can someone help me please? First we need to understand your terms. An excel sheet is a worksheet within a workbook(file). Now detail exactly what you need. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.co...

Copy Sheets to One Sheet
What is the easiest way to copy 30 Excel files into one Excel file? The column data in each Excel file is the same, so data should not get missed up. Thank You! Steve >-----Original Message----- >What is the easiest way to copy 30 Excel files into one Excel file? > >The column data in each Excel file is the same, so data should not get >missed up. > >Thank You! > >Steve > >I'm looking for the same info - let me know if you hear anything. From what I understand, older versions of Excel had a "Binder" option to bind several excel files ...

move/copy folders from one mailbox to another
As an administrator i often have to move information from one person's mailbox to another. I normally attach these user's mailboxes to my profile and then copy or move the folders (ususally subfolders under the inbox). Recently we moved to outlook 2002 and now i cannot do that any more and i can't find out why. The message is "can't copy/move folder. Right click on the folder and look at the permissions ....." Since i can do it using outlook 2000 and not outlook 2002 i cannot understand why i should have to change the permissions (even changing them does not he...

Dynamically changing sub-forms in code.
Hi all, I hope someone can hep me here - I've been looking at this for days now, and I'm out of ideas. I'll firstly explain how my db is structured... Tbl: stockStation (a computer worstation) PK: ss_StationId Tbl: stockAsset (assets on a workstation) PK: sa_AssetId FK: sa_fk_ss_StationId Tbl: stockFaults (faults for an asset) sf_fk_sa_AssetId (links to sa_AssetId) Tbl: stockFixtures (fixtures for an asset ie. patch info.) fx_fk_sa_AssetId (links to sa_AssetId) Tbl: stockNotes (notes related to an asset) nt_fk_sa_AssetId (links to sa_AssetId) Al...

Control arrays in VC++
Hi, I've been trying to find this for a little while, but how do you create a control array in the IDE for VC++. I need just a small fixed array, but I think it would be easier than having to write ten event handlers and five functions. >I've been trying to find this for a little while, but how do you create >a control array in the IDE for VC++. John, If you create each control with contiguous ID values you can make use of the ON_CONTROL_RANGE macro to have a single handler for the event. The IDE unfortunately won't help you to do this - you have to be careful when cr...

Excel relative cell referenc lost while sorting
in a spreadsheet, in cell "A10", I'm making a relative reference to "A1 which shows "Oct 21, 2004" formatted as date number. When I sort th columns into another order, the actual relative reference doesn' follow the "Oct 21, 2004" and the relative cell reference gets anothe cell which for example might have "October 15, 2004" How do I ensure that even through a sort, my original reference cel isn't lost -- jracin ----------------------------------------------------------------------- jracine's Profile: http://www.excelforum.com/me...

how do i plot data consisting of 1000 lines in excel sheet
hi all, i need to plot a data which has about 1000 lines in excel xy scatter chart. please help me Hi, A chart can only have 255 series, is that what you mean by lines? It can however have 32K data points. What sort of help do you need exactly? Cheers Andy novice wrote: > hi all, > i need to plot a data which has about 1000 lines in excel xy scatter chart. > please help me -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info Andy wrote on Tue, 29 Nov 2005 08:35:34 +0000: AP> A chart can only have 255 series, is that what you mean by AP> lines? It can howev...

Rectangles on Form
Option boxes display on a form with rounded corners. Is it possible to draw a rectangle with rounded (as opposed to square) corners so that it looks the same as an option box? Yes. But not as a rectangle. Make an option box and put meaningless controls options in it and make them invisible. Delete the option box label. Note, however, that the lines will revert to a rectangle, even in an option box, if you use a color other than the default and set the line size other than hairline. Damon "JimP" <jpockmire@houston.rr.com> wrote in message news:460fb8cf$0$24757$4c...

Vertical tabs in Property Sheet
Hi, I'm trying to create a property sheet wherein the tabs would be displayed vertically(on the left), rather than on top. The code is as shown below: BOOL CMyPropSheet::OnInitDialog() { BOOL bResult = CPropertySheet::OnInitDialog(); CTabCtrl *pTab = GetTabControl(); pTab->ModifyStyle(0, TCS_VERTICAL ); return bResult; } The problem with this is that the tabs are not displayed clearly. The edges are only vaguely visible till I click on them. Apprecaite if anyone could help me fix this. Also, can icons be displayed on the tabs rather than plain text...

How to delete specific row from sheet?
Hi, How this differs from all other posts like this is that I want a function that deletes certain row, like "1" or "100", by given row number. Not with specific criteria. Something like this. =IF(something;deleteRow(3);0) So what I want, is that deleteRow(3) =) Thanks for help, -Sirritys No worksheet function can delete a row. You will have to use a VBA Sub fort that. HTH -- AP "Sirritys" <aki.koikkalainen@hotmail.com> a �crit dans le message de news: 1152513207.898695.38230@s13g2000cwa.googlegroups.com... > Hi, > > How this differs from ...

Word Form Send To function dropping activeX controls
Hey all...new to the forum but i have a question i hope can be answere here. I work in a large Telecom/IP/Voice network managment center and we hav recently implemented a "network report" that needs to be completed b each shift telling the next shift what the overall health of th network, and alerting the next crew to any potential issues. Problem is this. I have 3 checkboxes at the top of the form t indicate what shift the form is for. We edit the form in Word and the file>send to mail recipient, and the text shows up but the check boxe are gone. If i send as attachment the...

copy range of cells from one workbook to another
I have 2 workbooks. The 1st one is a list of individual names and numbers. One row per individual. The 2nd is a "request for manual check" form. Instead of retyping all that data is it possible to copy one line of data into the second workbook to complete the check request form? There are only 3-4 fields that need to be completed on the form. I have about 100 employees on the list and one form. Each employee needs his own request form. Hi try using vLOOKUP. See: http://www.contextures.com/xlFunctions02.html -- Regards Frank Kabel Frankfurt, Germany "mitmeez" <...

problem while opening the excel sheet #2
hi, priter setup is being displyed first while i opening excel worksheet can you help me with this issue?? ...

Filter and copy question
I have the following code to filter a data sheet. How can include a code to copy the filtered data and place it in a sheet called "Z"? Sub ApplyFilter() Dim wsDL As Worksheet Dim wsO As Worksheet Dim rngAD As Range Set wsDL = Sheets("DateList") Set wsO = Sheets("Orders") Set rngAD = wsO.Range("AllDates") 'update the list of dates wsDL.Range("A1").CurrentRegion.ClearContents 'rngAD.Offset(-1, 0).Resize(rngAD.Rows.Count + 1).Select rngAD.AdvancedFilter _ Action:=xlFilterCopy, CriteriaRange:="", _ C...