Eliminate References and Keep Data


I have 55 workbooks that are filled with formulas that take data from other
workbooks.  I would like to get rid of the formulas, but keep the data.  I
would like all compiled workbooks to have any references eliminated.  How
can this be done?



1/20/2005 5:20:48 PM
excel 39879 articles. 2 followers. Follow

1 Replies

Similar Articles

[PageSpeed] 23


Select the cells, Copy.  Now paste-special right over the original cells 
with Edit (or right-click) - Paste special - Values.

Earl Kiosterud
mvpearl omitthisword at verizon period net

"Reed" <oscar49337@yahoo.com> wrote in message 
> Hi,
> I have 55 workbooks that are filled with formulas that take data from 
> other
> workbooks.  I would like to get rid of the formulas, but keep the data.  I
> would like all compiled workbooks to have any references eliminated.  How
> can this be done?
> Thanks,
> Reed

nothanks4548 (968)
1/20/2005 6:07:57 PM

Similar Artilces:

Eliminate unused columns
I have a table of columns of data per week, which I convert to a table of columns of data per month. This leaves 3 or 4 blank columns between the months. Is there a simple way to consolidate the month columns into a contiguous region, eliminating the unused columns between the month columns? Or... is there a smarter way to do the conversion than sum across an fill down? One approach is to use a macro: if your empty columns are completely empty you could use this macro. Hit Alt-F11, Click Insert/Module (check the project window to make sure your workbook is the active project before...

neep help,CSV,import data
My problem is the following: - I have this contacts in my outlook personal folders - I've installed MS CRM - Now I want to add these contacts to the CRM server - I exported them from outlook2003 as CSV file - Then I imported them to MS CRM using the import wizard - While importing the Arabic names appears as unreadable characters - Any ideas……..plz..... ...

how to keep cell rounding to 2 places even when someone else pastes number with more.
At work, i receive spreadsheets of bonuses that can be derived from formula. however, when i copy and paste the info into our file fo payroll, it can only be 2 places, otherwise it kicks out an error. now, i've been here almost a year now and it's been near impossible t get the people who submit the file to make sure they ROUND the numbe to 2 places. i'm wondering if there is something i can do in the cell that when the paste their number in it, it automatically rounds. or if that's not possible, if there's a way for me to highlight column then have it round all t...

Date Elimination
I have a worksheet with mainly dates in column A in the format of '25 Aug 2008'. Is it possible with a macro or similar to delete lines beyond a certain date (2 years hence)? Basically, I'm not interested in data more than 2 years old. This would eliminate a lot of data and make for a more viewable worksheet. try this Sub del_date() ActiveCell.Range("A1").Select Do Until ActiveCell.Value =3D "" dt =3D Date - 730 If ActiveCell.Value <=3D dt Then ActiveCell.ClearContents ActiveCell.Offset(1, 0).Select Else ActiveCell.Offset(1, 0).Select End If Loop End ...

Cut and paste specific data
`Hi Have been working on a problem which I think I can now simplify Cell A1 contains the following: 15 Smith Street Blacktown How can I cut the last item (Suburb) from the cell and paste it into B1? Many thanks in advance Stephen Try this in B1: =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Father John&quo...

eliminating duplicates

Data Type Mismatch in criteria
I have the below code. It stops on the If DCount line and says Data Type Mismatch in criteria expression. Run-Time Error '3464': I can't figure out what is wrong.. Matt >>>CODE>>>> Private Sub LookUpOrder_AfterUpdate() ' Find the record that matches the control. Dim SID As String Dim stLinkCriteria As String Dim rsc As DAO.Recordset Set rsc = Me.RecordsetClone stLinkCriteria = "[LeadsID]='" & Me![LookUpOrder] & "'" 'Check table for for item number. If DCount("LeadsID&quo...

Money Keeps Downloading Full Account History
I have archived all my Money Data prior to 1/1/2008 into an old file. However, Money keeps populating my Account Registers (only for certain accounts and only once in a while) with the full history from the account. For example, right now, I have 527 transactions to review with 500 or so being from before 1/1/2008. Is there a setting I have set incorrectly? Is there something wrong? Any way I can fix this? Brad The basic problem is that you have archived! If not too far down the track I suggest that you revert to using the archived file, which is just a copy of your .mny at t...

eliminate duplicates
I have a table with 7 different columns of team members on different teams. I need a count of all team members. How do I query seven different columns and eliminate dups? You've discovered yet another problem with denormalized tables. If you have 7 different columns, each containing the same basic information, you have a repeating group. If you can't go back and redesign your tables, the easiest approach would be to create a UNION query that normalizes the data so that it's in only 1 column: SELECT Member1 AS Member FROM MyTable UNION SELECT Member2 AS Member FROM MyTabl...

eliminate message from append query to eliminate duplicate record.
Create a new query in design view. Add the two tables for which you wish to compare. When you see the two tables in the design view of the query click on one of your 2 join fields and drag to the corresponding join field in the other table (a line should appear) then double click on fields to add them to the query. For your comparison field Your going to have to explain how you want to compare the two fields. What type of field is in table B, and what kind of comparison do you want to do? You could start with something simple like entering "ComparisonField:Iif(tableA.field1=tableB.field2,...

How to delete some sample CRM Data
I created some test data as Quota, Order, Product. And now CRM doesn't allow me to delete some of them because of they became read only data. Is there any way to delete active or closed quota, Order etc, and products that in used? If I delete the user account who is own the records that I want to delete, what happened? Any help appreciated. If you are working into a development or test environment then use SQL to clear the database. Is the faster way... If anything stop to work after this, reinstall the CRM. :D []'s Vin�cius Pitta Lima de Ara�jo "gulsah.safsoy@teknoko...

row reference
I want to take the average of every other row in my data set and I will be adding weeks to the data as time moves forward. Is there a relative or absolute formula that does this? Thanks for any help. Similar to columns, but pre-2007 can handle entire column callouts. Odd rows: =AVERAGE(IF(MOD(ROW(A1:A100),2)*ISNUMBER(A1:A100),A1:A100)) Even rows: =AVERAGE(IF((MOD(ROW(A1:A100),2)=0)*ISNUMBER(A1:A100),A1:A100)) Both are array formulas, must be confirmed using Ctrl+Shift+Enter, not just Enter. -- Best Regards, Luke M *Remember to click "yes" if this post ...

Outlook keeps hanging on mobile broadband connection
Hi there, are there any settings that I should check to stop Outlook from keep hanging when I'm on a mobile broaband connection? All other internet program work just fine, it is only Outlook that has an issue with it. I shoiuld mention that it is Outlook 2007 on XP Pro SP 3. The task bar icon is permanently set to "Microsoft Outlook is requesting dats from the server". Messages stay in the Outbox until you kill the Outlook process and restart it. what type of email account? do you have outlook set to use any internet connection? -- ...

Keep GL and IV in sync between 2 Companies
I have 2 Company databases and I want to keep the GL and IV (Inventory) tables in sync. Any tools that will do this. What do you mean "in sync"? Do you need the two companies to match?? Or are you trying to keep IV in sync with the GL?? Richard L. Whaley Senior Business Consultant 321-277-1341 btw.....there is some good information that might be helpful to you in some books on Dynamics GP/Great Plains available at www.AccoladePublications.com "Charlie Zarzour" wrote: > I have 2 Company databases and I want to keep the GL and IV (Inventory) > tables ...

How can you import data from another database using query ?
I am using SQL Server 2005. I would like to import data from dbA to dbB for tblMin, but only for some of the data in tblMin. Both dbA and dbB have tblMin. So, in the import wizard, i selected "Write a query to specify the data to transfer", then I write a query like this: SELECT * FROM [dbA].[dbo].[tblMin] where symbol = 'ABC' If I do this, the import wizard creates a new table called Query, and import the data to Query, instead of importing it to tblMin on dbB. How can I import the data to tblMin on dbB ? Thank you fniles (fniles@pfmail.com) writes:...

Expanding conditional formating with reference cells changing
I need to extend the conditional formating from a range of three cells to the whole document. The issue is that for each group of three cells, I need the reference cell to change. Essentially, I have data in three cells changing to blue or pink, depending on whether the value in one of the three cells is an F or and M. I need the reference for each set of three cells to adjust automatically, instead of manually entering the changes. I have a large table to cover. Can you just select the cells, then copy them. Then select your other cells and edit|paste special|Formats? CCoop wrote: &...

Fixed cell references
I want to create a form for purchase requisitions that is a spreadsheet (using Excel 97) with 4 work sheets. The first sheet is where the data will be entered, one row per item to be purchased. The other sheets will be fully formatted for printing/faxing and will include company logo, delivery information, order numbers etc. What I want to do is for the formatted sheets to reference various ranges of the data entered on the first sheet. i.e. the first formatted sheet references the data in worksheet1 cells A1:C20. The second formatted sheet references the data in worksheet 1 cells A21:C4...

Remove All Bill Data
When I first upgraded to Money Plus, I was having performance issues. Nuking the bills to eliminate the multiple schemas that had built up since Money 1.0 seemed reasonable, so I performed the operation and rebuilt them. However, here I am, 4-5 months later, unable to balance my primary checking account without having Money hang and need to be killed twice during the process. I can't enter transactions that match scheduled bills without having Money hang and need to be killed. So, I nuked the bills again and started over with rebuilding them. Problem solved. Rather than cleaning up th...

Getting data from a formula
Is it possible to extract a value from within a formula? For example: Let's assume I have a formula in B3 which is "=a3*2". Is it possible to get the value "3" off the formula in B3 and placed in C3? TIA. A B C 5 =a3*2 It would depend on how complex the formula is and how much effort you wanted to put into the code that would parse that formula. m1918@shaw.ca wrote: > > Is it possible to extract a value from within a formula? > > For example: > > Let's assume I have a formula in B3 which is "=a3*2". Is it possible to > get...

cross reference two
I'm need to write a formula based on information in a table - as an example: High Performing Location Market Area Non-Market Area New Location 1 $1,000,000 $750,000 $500,000 $200,000 2 800,000 600,000 300,000 100,000 3 550,000 400,000 200,000 50,000 I want to lookup the information in the top row and on the left column, then multiply by a factor referenced in ...

Outlook stealing mailbox data
I'm running outlook on a couple of machines, for some reason one of the outlooks is pulling all the data from the exchange mailbox and the other has no data. Where in outllok/mail settings do I adjust it so that all the mailbox data is held at the server and not deleted from the server? Also once I've done this I need to recover the mailbox, how would I go about doing this? There is no backup solution for exchange on our systems so its a case of recovering from the "fat" outlook. "Glass" <goldmercury0@hotmail.com> wrote in message news:1123603497.812423....

F4 no longer automatically creates absolute reference
I select the text of a cell name (A1, for example), hit F4, an nothing happens. I have to go in and type my $. How do I reset F4 to work? Did you go into edit mode first (either hit F2 or click on the formula bar with A1 selected)? molla wrote: > > I select the text of a cell name (A1, for example), hit F4, an nothing > happens. I have to go in and type my $. How do I reset F4 to work? -- Dave Peterson Do you have an F-Lock keyboard? Change state and see if F4 is enabled. Gord Dibben MS Excel MVP On Fri, 4 Jun 2010 12:46:18 -0700, molla = <molla@di...

How to Get Reference of CommandBar in Excel?
hi! i am using VC7 and Office 2003. I want to add a toolbar in EXCEL with some buttons. i have done that for word already. First i get the reference of the CommandBar using the following code. HRESULT hr; CComPtr<Office2003::_CommandBars> spCmdBars; //QI() for application. CComQIPtr<MSWord::_Application> spWordApp(m_pApplication); ATLASSERT(spWordApp); m_pWordApp = spWordApp; hr = m_pWordApp->get_CommandBars(&spCmdBars); so now when i try to do the same with Excel i receive an error message that get_CommandBars is not member of m_pExcelApp. i don't know how to...

Exporting the data from the form currently in the foreground
Hello, I'm trying to export the data displayed in a form to an rtf file, by clicking on a submit button on the form with the data I want exported. The code for the button calls another module with the following code: DoCmd.OutputTo acOutputForm, , acFormatRTF, FileName, True The same happens with: DoCmd.OutputTo acOutputForm, Form_Name, acFormatRTF, FileName, True Is there an easy way of just exporting the data from a form that is currently open? Cheers, Mike Or perhaps instead of exporting the form to a rtf file, is it posible to export the form's recordset? I know that fo...

Matching data.
I have two sets of data (2 worksheets) one set contains a column with 14000 different numbers and a second column with corresponding values to these numbers. the second worksheet contains a column of numbers which are some (7000) of the numbers in the first worksheet. I need to put the corresponding values from the first worksheet with the column of numbers in the second worksheet. The only way I know how is to copy one at a time which is taking a long time. Does anyone know a quicker way? Regards Barry Hi Barry On sheet2 in cell B1 enter =VLOOKUP(A1,Sheet1!A:B,2,0) Copy down as f...