$ large amount of Data.

Hi,
I've been working on quite a large worksheet, and after about 5000 rows, I 
realized I forget to hit F4 and $$ the referenced cells. Is there a way to 
highlight a large area, and have it do the whole thing at once?

Thanks,
Confused Man 


0
bfbj (38)
2/7/2005 4:59:57 AM
excel 39879 articles. 2 followers. Follow

5 Replies
644 Views

Similar Articles

[PageSpeed] 27

Hi!

Take a look at this:

http://tinyurl.com/5pp4q

Biff

>-----Original Message-----
>Hi,
>I've been working on quite a large worksheet, and after 
about 5000 rows, I 
>realized I forget to hit F4 and $$ the referenced cells. 
Is there a way to 
>highlight a large area, and have it do the whole thing at 
once?
>
>Thanks,
>Confused Man 
>
>
>.
>
0
biffinpitt (3172)
2/7/2005 6:59:05 AM
Hi,
this example is good but I seriously recommend NOT using cell as a variable
name...it is a reserved word in Excel.

My 2cents..

O

-- 
Message posted via http://www.officekb.com
0
forum (466)
2/7/2005 7:20:45 AM
confused

Only with VBA.

Sub Absolute()
Dim rcell As Range
    For Each rcell In Selection
        If rcell.HasFormula Then
            rcell.Formula = Application.ConvertFormula(rcell.Formula, _
            xlA1, xlA1, xlAbsolute)
        End If
    Next
End Sub

Sub AbsoluteRow()
Dim rcell As Range
    For Each rcell In Selection
        If rcell.HasFormula Then
            rcell.Formula = Application.ConvertFormula(rcell.Formula, _
            xlA1, xlA1, xlAbsRowRelColumn)
        Next
    End Sub

Sub AbsoluteCol()
Dim rcell As Range
    For Each rcell In Selection
        If rcell.HasFormula Then
            rcell.Formula = Application.ConvertFormula(rcell.Formula, _
            xlA1, xlA1, xlRelRowAbsColumn)
        Next
    End Sub

Sub Relative()
Dim rcell As Range
    For Each rcell In Selection
        If rcell.HasFormula Then
            rcell.Formula = Application.ConvertFormula(rcell.Formula, _
            xlA1, xlA1, xlRelative)
        Next
    End Sub


Gord Dibben Excel MVP

On Sun, 6 Feb 2005 23:59:57 -0500, "confused man" <bfbj@iup.edu> wrote:

>Hi,
>I've been working on quite a large worksheet, and after about 5000 rows, I 
>realized I forget to hit F4 and $$ the referenced cells. Is there a way to 
>highlight a large area, and have it do the whole thing at once?
>
>Thanks,
>Confused Man 
>

0
Gord
2/7/2005 4:27:18 PM
O,

Cells is, but cell is not.

HTH,
Bernie
MS Excel MVP

> I seriously recommend NOT using cell as a variable
> name...it is a reserved word in Excel.


0
Bernie
2/7/2005 5:44:57 PM
Thank you all,

These explanation were a little more technical than I was hoping for. I'm 
not too good with VBA, but I bumbled my way through it using your collective 
advice.

Thanks so much
Confused Man

"confused man" <bfbj@iup.edu> wrote in message 
news:avydncPN-rRMaJvfRVn-ig@adelphia.com...
> Hi,
> I've been working on quite a large worksheet, and after about 5000 rows, I 
> realized I forget to hit F4 and $$ the referenced cells. Is there a way to 
> highlight a large area, and have it do the whole thing at once?
>
> Thanks,
> Confused Man
> 


0
bfbj (38)
2/9/2005 12:34:34 AM
Reply:

Similar Artilces:

MICROSOFT MONEY 2005
I use MSN Bill Pay with Microsoft Money. I own a copy of Microsoft Money 2004. 1. Things were fine until I downloaded a "trial version of Money 2005" 2. In loading Money 2005, I made the mistake of doing the "recommended" load over Money 2004, which wipes out Money 2004 (incidentally, that was a download too - but more to come about that). 3. Microsoft Money 2005 completely wiped out my MSN bill pay file. YEARS OF DATA with no way of retrieving the data, according to MSN Bill Pay support people. 4. After three hours with 6 different support people from MSN Bill P...

Email large messages
Hi, I have to email large DWG & zip files attached to email messages and have been having trouble with breaking apart large mess. in outlook express. I set maximum file size to 1500KB but they never seem to get reconstituted on the receiving end. If I send a message with a 6MB attachment the receiving end gets 4 file: 1 of 4, 2 of 4.... 4 of 4 and so on and they can not do anything with it. Thanks for any help. Joe This newsgroup is for support of Outlook 97, 98, 2000, 2002 & 2003 from the Office family for Intel PCs. For Outlook Express (OE) support try posting in one of thes...

Converting numeric data in Spreadsheet to zero
Hi I am working on a excel spreadsheet. It contains text dat and numeric data. what I am looking for is that I need to convert that numeric data into 0. I mean there are cells which contains values like 234, 22, 12 etc and they blank cells in between them. So is there a way by writing a command or Macro to convert that numeric data to 0, ie each field must contain a value 0 .. Thank you very much in advance Deepak -- deepakgoyal ------------------------------------------------------------------------ deepakgoyal's Profile: http://www.excelforum.com/member.php?action=getinfo&useri...

Migrate Data from Great Plains 7.5 to Sage Line 50 v12?
I have Great Plains 7.5. I want to move to Sage Line 50. How do I get the data out? What DBMS are you on? MS-SQL, Btrieve...? -- Richard L. Whaley Author / Consultant / MVP http://www.AccoladePublications.com Documentation for Software Users "Steve" wrote: > I have Great Plains 7.5. I want to move to Sage Line 50. How do I get > the data out? > > ...

What settings needs changed in order to merge from an access data. #2
I had created a certificate in publisher and was merging the names from a query in access. The techical support center upgraded our machines and now the merge will not work. What settings should I check to make this work again? ...

How large is a chart?
I am just asking out of curiosity. How large, in kilobytes, is a chart; say, a 1000-point x-y scatter? I had a large file whose size was halved when the charts were deleted (actually, they were lost, along with formats and scenarios, when the file got corrupted; see "story" below). In my non-expert view, it should not be so large, because the only information unique to the chart is its position, size, format, and similar details. The actual data itself is contained in the data-range cells, so should not have to be repeated in the chart itself. Not only the formulas but th...

Data Form/Dialog Box
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I would like to create a dialog box for my expense report. <br><br>My company has a spreadsheet they like me to use...but I loath moving cell to cell to enter my data. <br><br>I would like to create a dialog box or data form (not sure of correct terminology) that I can enter the data in (date, $$, choose category...etc) and have it land in the expense report. Even adding additional rows if necessary. <br><br>I tried the Data>Forms function...but wanted something a bit more...

Year to Date Amount on Payroll Check
We just printed out our first payroll check for 2007. The amount for the check printed fine but the Year To Date amount is double the amount. In some cases, YTD amount is triple of less than double. How do we fix this problem and where is this data stored? ...

PLEASE HELP: Send email from Access with data in email body
Hi All, How can I send email from access with data displayed in the email body? I have a button "Email users" in access custom form that sends email to all the users, But I dont know how to send them their particular schedule only. For eg: Here is what My Table looks like: Users Day Time User1 M 13:00 User2 M 14:00 User3 T 15:00 User1 T 16:00 User3 W 17:00 User4 W 18:00 User4 Th 13:00 So basically, When I click "Email Users" Button, I want all the users to receive their respective schedules onl...

Large fonts
How can I get fonts larger than 72? Hi swirem70, In the point-size box, you can type any point size, from 1 to 1638, in 0.5pt increments. -- Cheers macropod [Microsoft MVP - Word] "swirem70" <swirem70@discussions.microsoft.com> wrote in message news:9BEC0E88-CFF7-454F-B714-15D6538BBF02@microsoft.com... > How can I get fonts larger than 72? ....and press Enter. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "macropod" <macropod@invalid.invalid> wrote in message news:%236N...

external data problem
Hi I have a problem with external data. In the Data Range Properties, under Data Layout. There are 3 options after 'If the number of rows in the data range changes upon refresh:' 1 - Insert cells for new data, delete unused cells 2 - Insert entire rows for new data, clear unused cells 3 - Overwrite existing cells with new data, clear unused cells. I want option 3 to be the default oprion but it always defaults back to option 2. Is there any way I can change this.?? Cheers Baz ...

Splitting Data in a Cell into Groups
I have a issue where I have a list of 3 letter codes seperated by a ; that I need to grouped by the first letter of the code (ABC, ACB, AAA and BAA, BAB, BAC and so on) but I'm unable to figure out how to do this in either Excel or Access. There are about 60,000 rows and 1 code to 500 codes in each cell. Please help Here is an example of the data ABC;ACD;ADD;AEF;BOA;BPA;BZZ;CED;CEZ;TTY;ZZZ We want a way to seperate the data in the columns into 26 Columns (Each Column for each letter in the alphabet) Column A: ABC;ACD;ADD;AEF Column B: BOA;BPA;BZZ Column C: CED;CEZ Column T:...

How do I add a variable with unevenly distributed data to a data s
I have a large (12000 rows, 75 columns) panel data set in excel with 188 countries and 55 years for each country (1950-2004). Now I want to add a new variable to this data set. I presently have another excel sheet with data on this variable along with the corresponding country and year variables. But in this second data set, there are only 1600 observations which are unevenly distributed across the countries and years; for example, for some countries there are no observations, for some there is one and for others there are several. Is there a way in excel to insert this variable into th...

XML error "Some Data Was Imported As Text"
Daily, I recieve a 3rd party XML file that I parse in Excel (using an excel macro) and then import into an Access database. Infrequently, the data will import into Excel, then parse and then the routine stops with the error message above. I look through the raw XML file and everything appears to be within the brackets, etc. Can someone give me some clarification to the meaning of the error message? Thanks in advance and Mery Christmas and Happy Holidays! The error message might be generated within your macro - as it is parsing the data it might detect that some field which should be ...

large email stuck in Outbox
I have an email with a large attachmment stuck in my Outbox. After trying to send it I get the error message gtom my email server that the email is too large. The problem is that outlook won't let me delete the email. I get either no message when trying to delete or Outlook tells me that it is already being send and I cannot delte while sending. Is there any way to get rid of this email? Hi, Log off from outlook. Kill Mapisp32.exe Then remove the network cable. Connect to outlook Work Offline Now u can delete the file from Outbox. Rgds, Kishore >-----Original Message----- &g...

Update CRM Data using vb.net
Hi, I am new to CRM. I am trying to retrive and update information into CRM using my VB.Net application. I am able to retrieve information from CRM. But i am not able to update. Can any one tell me how to update contact information using VB.Net. If any sample program it will help me to create some applications. Thanks in advance. Phani Check out the Microsoft CRM examples on msdn.microsoft.com - a number of these show updfating data "Phani" <phanimadhavsv@hotmail.com> wrote in message news:Ow5xjhnWDHA.2064@TK2MSFTNGP11.phx.gbl... > Hi, > > I am new to CRM. I am...

Speed issues with large volume of serial numbers
Has anyone had issues auto assigning/posting a receipt with 100,000+ serial numbers in Dynamics GP? We were testing with the GP10 4/08 VPC (which I know isn't perfect), but it seems to take 15 or 20 minutes to assign. And 3rd party add-ons? hi Msauce What kind of Hardware are running this on? It sounds like the SQL server is having an issue and not he client. -- Fliehigh "MSauce" wrote: > Has anyone had issues auto assigning/posting a receipt with 100,000+ serial > numbers in Dynamics GP? We were testing with the GP10 4/08 VPC (which I know > isn't p...

How to create a dynamic chart using data from every 7th data row
I have a spreadsheet of with multiple rows of data based on dates. I need to build a dynamic chart that only uses data from every 7th row. To date all I have been able to find is going through the whole spreadsheet and marking data in every seventh row. Any suggestions Here's one way: http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=209#jon024 - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "Ralph" <Ralph@discussions.microsoft.com> wrote in message news:A82A2E2...

Macro to Synchronize data frm svrl workbooks & columns to 1 workbo
I am trying to record a macro that will pull from several different workbooks with active data. For Example: I want to take the data in workbook1 column "A" and copy and paste it to column "A" in workbook3, then look at workbook2 and copy and paste the data in column "A" to the first open cell in workbook3 column "A". It then needs to do the same thing for column "B" & "C". The data in workbooks 1 & 2 columns "A", "B" & "C" will be changing daily. The problem I run into when I try to...

Data Validation
I want to use the symbols for trademarks in a validation list. I listed the characters in cells using the "Symbol" font. However, when the list pulls up, it's in the original font (Arial) so the characters appear differently. I tried changing the whole spreadsheet to "Symbol" to see if this would change anything, but the characters are still in the Arial format. Is there any way to make the list pull in the font for the symbols? The short answer is no. Data validation list are Arial font. -- HTH... Jim Thomlinson "richchipper" wrote...

Deleting data entries but not formats
Hi newsgroup, I have some trouble with excel... and with a document which I use for serial mails via Mail Merge. Now I need to clean all old data entries and prepare it for the next use. But if I simply delete the entries or do it via Clear-Entries then I have problems because conditional formatting will also be deleted and furthermore the mail merge cant work anymore because some fields are not found anymore. Does someone know how I can only delete the data entries, nothing else? The Mail merge runs via SQL and I'm not quite good in it... Thanks a lot in advance and a nice weeken...

NDRs not being generated on Large SIZE emails
This is a follow up question to my SIZE MATTERS! post. When a large email gets sent out (in my case over 3MB), I do not receive an NDR. So the user is left thinking that it went out because he/she never gets notified that it was not delivered. The Application Log has EVENT ID 3004 with a Source being - MSEXCHANGEIMC which states: An NDR could not be sent. This is most likely because the original message had a blank originating address. In most cases this is normal behavior, although it can sometimes indicate a local or remote server configuration problem. If archiving was enabled at...

Data Migration Manager Cnofiguration error
I can't get Data Migration Manager configured. I first intalled DMM (CRM 4.0) on the dev PC running XP SP3 then on SQL Server hosting CRM databases and also on the CRM server (both application and platform installed on the same server running Windows server 2003 Entreprise SP2, it's the DEV environment!) and I got the exact same result from any of these intallation, by the installation of DMM and CRM server done with the same user with all required rights. When I first run DMM, the config wizard appears and after I type in the intranet url and click NEXT the following error occ...

No data appearing on SOP invoices
Hi there We have a customer that has been using SOP for about a year and printing SOP documents without any problem. About 2 months ago they implemented Collections Management. Last week was the first time they tried to email statements and invoices to their customers. The statements did not even send, and the invoices went to all the customers, only to find out that all the fields were not appearing on the invoice. The design is printed: logo, lines, labels etc, but no data. If we print it again through SOP it works. What could be causing the problem. Regards Robin Hi Robin-- ...

Send Raw Data to a Printer
To print directly to a printer I have tried the solution: "HOWTO: Send Raw Data to a Printer by Using the Win32 API" http://support.microsoft.com/?id=138594 But nothing comes out on the printer! The "status box" (double-click on printer tray icon) shows the document for a second or so when RawPrint is executed, usually with status Spooling and occasionally with status Printing, but as mentioned nothing gets printed. The printer works OK when printing from notepad, word etc. The printer is a Canon S750 (I have named it CanonS750 in Windows), and it's connected with par...