Copy data into a table (but not the formula)

Hi
First of all I apologise if this has been asked before.

What I want to know is it possible, and if so how does one do it, to d
the following ?

Copy data from a cell into another cell in a table but without having 
formula in the destination cell , e.g 

Say $b1 contains the name Fred and I want to place Fred into the $c
cell but I don't want there to be a formula in $c5 asking for it tha
will update the contents of $c5, if I change the value held in $b5, fo
instance, I put Fred in $b1 and Fred appears in $c5, but then I pu
another name, say Bert into $b1 and it goes to $c6, but the contents o
$c5 remains Fred, etc, etc

Any help greatly appreciate

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

0
1/17/2004 5:30:52 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
477 Views

Similar Articles

[PageSpeed] 30

Hi Apollo

If I understand you correct?

With a header in C4 you can use this macro to copy the Value of B1
each time in the C column(starting in C5)
Every time you run the macro it will copy the value of B1 below the last cell
with data in column C


Sub copytest()
    Dim sourceRange As Range
    Dim destRange As Range
    Set sourceRange = Range("b1")
    Set destRange = Range("C" & Rows.Count).End(xlUp).Offset( _
        1, 0).Resize(sourceRange.Rows.Count, sourceRange.Columns.Count)
    destRange.Value = sourceRange.Value
End Sub


-- 
Regards Ron de Bruin
(Win XP Pro SP-1  XL2000-2003)
www.rondebruin.nl



"Apollo >" <<Apollo.106sde@excelforum-nospam.com> wrote in message news:Apollo.106sde@excelforum-nospam.com...
> Hi
> First of all I apologise if this has been asked before.
>
> What I want to know is it possible, and if so how does one do it, to do
> the following ?
>
> Copy data from a cell into another cell in a table but without having a
> formula in the destination cell , e.g
>
> Say $b1 contains the name Fred and I want to place Fred into the $c5
> cell but I don't want there to be a formula in $c5 asking for it that
> will update the contents of $c5, if I change the value held in $b5, for
> instance, I put Fred in $b1 and Fred appears in $c5, but then I put
> another name, say Bert into $b1 and it goes to $c6, but the contents of
> $c5 remains Fred, etc, etc
>
> Any help greatly appreciated
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
rondebruin (3790)
1/17/2004 6:36:59 PM
Ron
Thanks, this works a treat

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

0
1/21/2004 11:17:02 PM
I understood the question and was excited for the answer.  Your answer however was greek to me...macro

    
     ----- Ron de Bruin wrote: ----
    
     Hi Apoll
    
     If I understand you correct
    
     With a header in C4 you can use this macro to copy the Value of B
     each time in the C column(starting in C5
     Every time you run the macro it will copy the value of B1 below the last cel
     with data in column 
    
    
     Sub copytest(
         Dim sourceRange As Rang
         Dim destRange As Rang
         Set sourceRange = Range("b1"
         Set destRange = Range("C" & Rows.Count).End(xlUp).Offset( 
             1, 0).Resize(sourceRange.Rows.Count, sourceRange.Columns.Count
         destRange.Value = sourceRange.Valu
     End Su
    
    
     --
     Regards Ron de Brui
     (Win XP Pro SP-1  XL2000-2003
     www.rondebruin.n
    
    
    
     "Apollo >" <<Apollo.106sde@excelforum-nospam.com> wrote in message news:Apollo.106sde@excelforum-nospam.com..
     > H
     > First of all I apologise if this has been asked before
     >> What I want to know is it possible, and if so how does one do it, to d
     > the following 
     >> Copy data from a cell into another cell in a table but without having 
     > formula in the destination cell , e.
     >> Say $b1 contains the name Fred and I want to place Fred into the $c
     > cell but I don't want there to be a formula in $c5 asking for it tha
     > will update the contents of $c5, if I change the value held in $b5, fo
     > instance, I put Fred in $b1 and Fred appears in $c5, but then I pu
     > another name, say Bert into $b1 and it goes to $c6, but the contents o
     > $c5 remains Fred, etc, et
     >> Any help greatly appreciate
     >>> --
     > Message posted from http://www.ExcelForum.com
     >
0
anonymous (74722)
4/5/2004 9:01:12 PM
Start here

http://www.mvps.org/dmcritchie/excel/getstarted.htm
See David McRitchie's site if you just started with VBA


-- 
Regards Ron de Bruin
http://www.rondebruin.nl


"DanS" <anonymous@discussions.microsoft.com> wrote in message news:1DA176F9-4F78-49CD-AC31-7539632EB9A6@microsoft.com...
> I understood the question and was excited for the answer.  Your answer however was greek to me...macro?
>
>
>      ----- Ron de Bruin wrote: -----
>
>      Hi Apollo
>
>      If I understand you correct?
>
>      With a header in C4 you can use this macro to copy the Value of B1
>      each time in the C column(starting in C5)
>      Every time you run the macro it will copy the value of B1 below the last cell
>      with data in column C
>
>
>      Sub copytest()
>          Dim sourceRange As Range
>          Dim destRange As Range
>          Set sourceRange = Range("b1")
>          Set destRange = Range("C" & Rows.Count).End(xlUp).Offset( _
>              1, 0).Resize(sourceRange.Rows.Count, sourceRange.Columns.Count)
>          destRange.Value = sourceRange.Value
>      End Sub
>
>
>      -- 
>      Regards Ron de Bruin
>      (Win XP Pro SP-1  XL2000-2003)
>      www.rondebruin.nl
>
>
>
>      "Apollo >" <<Apollo.106sde@excelforum-nospam.com> wrote in message news:Apollo.106sde@excelforum-nospam.com...
>      > Hi
>      > First of all I apologise if this has been asked before.
>      >> What I want to know is it possible, and if so how does one do it, to do
>      > the following ?
>      >> Copy data from a cell into another cell in a table but without having a
>      > formula in the destination cell , e.g
>      >> Say $b1 contains the name Fred and I want to place Fred into the $c5
>      > cell but I don't want there to be a formula in $c5 asking for it that
>      > will update the contents of $c5, if I change the value held in $b5, for
>      > instance, I put Fred in $b1 and Fred appears in $c5, but then I put
>      > another name, say Bert into $b1 and it goes to $c6, but the contents of
>      > $c5 remains Fred, etc, etc
>      >> Any help greatly appreciated
>      >>> ---
>      > Message posted from http://www.ExcelForum.com/
>      >


0
rondebruin (3790)
4/5/2004 9:21:08 PM
Reply:

Similar Artilces:

Remote data access
As a new .net developer, I would like to know how a VB.net Windows application can access a SQL Server database residing on a web server. In other words, using the Visual Studio IDE, is there a way to develop a Visual Basic, Windows application that can access a SQL Server database over the internet. Thanks for suggestions, John C. John C. wrote: > As a new .net developer, I would like to know how a > VB.net Windows application can access a SQL Server > database residing on a web server. > > In other words, using the Visual Studio IDE, is there a > way to develop...

Formula not being stored any more
Recently, Excel has stopped storing certain formulae in the formula bar. For example, if I type in say "=3*1/10" Excel will store "=0.3" in the formula bar. This is most inconvenient as I want to be able to tell what the constituent parts of the calculation are. It never used to do this so have I accidentally set an option on somewhere? How do I turn it off again? I couldn't duplicate this. If I typed: =3*1/10 and hit F9 (calculate)--not enter I got: 0.3 You're not getting close to the F9 key (with only portions of your formula selected? (Yeah, I did...

Data Validation #12
Hi. I have data validations in sheet1 as named range lists from sheet2. When I have deleted sheet2 and copy sheet2 from identical other workbook - my validations does is not work... I see Name ranges in copied worksheet, but when I use they in validation - I have error: "The Source currently evaluates an error. Do you wish continue?" Only after deleting of Named ranges in Sheet2 and recreating its, my validations works. It is possible after replacing of worksheet with named ranges for validations (named ranges names are identical) use copied worksheet without recreating ...

Data Entry Form 03-20-08
Hi, I created a form to use for viewing currently records and entering new data. This time, the form doesn't allow me to enter new data. In fact, all text boxes didn't let me to enter new information. Please help Thanks Chi Several things to check: In form properties: Allow Additions = Yes Allow Edits = Yes If the form's record source is a query, be sure the query is updatable. -- Dave Hargis, Microsoft Access MVP "Chi" wrote: > Hi, > > I created a form to use for viewing currently records and entering new data. > > This time, the ...

Linking from a pivot table
I have a problem with Excel XP. Under Excel 2000 I would be able to link a Pivot Tables contents to another worksheet using the "=" and clicking in the cell within the pivot table, then using F4 to get rid of the "$" then enter. I would then use the autofill button to drag over the number of coulums I needed to fill, then autofill down to the bottom of the sheet for the number of rows in the pivot table that I needed. It would automatically change each cell designation to the corresponding cell of the pivot table. Now with XP, it doesn't work. When you use...

Copy Constants Only
Hi, Need to copy all "values" or "constants" (not Formulas) from one sheet to another sheet in a different workbook. I did the following: Edit -->Go To then Special (from dialogue box) then "constants" I see all the cells that I want are selected. Then I tried to use the copy command but I got the error message: "that command cannot be used on a multiple selection". Any ideas is appretiated! Khalil Here is a method that works, Copy everything to the second sheet Not use Edit | Go To | Special | Formulas and tap the Delete key best wishes -- Ber...

Days Old formula?
Hi im having a problem trying to figure out the forumla for days old. M teacher wants us to come up with a formula for the age of 2 dates. Does anyone know any formulas that will work? -- Message posted from http://www.ExcelForum.com If you need the days only, subtract =A1-A2 -- Regards, Peo Sjoblom "frackskat004 >" <<frackskat004.11hxa0@excelforum-nospam.com> wrote in message news:frackskat004.11hxa0@excelforum-nospam.com... > Hi im having a problem trying to figure out the forumla for days old. My > teacher wants us to come up with a formula for the age o...

Sorting lots of data
We are trying to sort through a csv file that we converted to an excel spreadsheet. the spreadsheet has over 40,000 entries which include mailing addresses that are in some cases duplicated. is there anyway to sort/filter the info to locate the duplicates and then remove them?? any help would be greatly appreciated. thank you for your responses. Hi use 'Data - Advanced Filters' and check 'unique entries' or use the following addin for this: http://www.rondebruin.nl/easyfilter.htm -- Regards Frank Kabel Frankfurt, Germany miss lynar wrote: > We are trying to sort thr...

Stacking Data??
if I have data like this A B C D... 1 Type\Date 01/10/04 02/10/04 03/10/04... 2 T-X 100 50 100 3 T-Y 50 70 0 4 T-Z 400 0 400 ... .. ...

Merging tables
Hello, I do not have deep knowledge of Excel. That is why my proble might appear a bit strange: I have two tables, list1 and list2. In both tables there is a colum with unique ID numbers. The rest of the columns contains differen information in both tables. Example: list1 ----- ID name email 101 bob bob@none.com 102 rita rita@none.com ... 999 jim jim@none.com list2 ----- ID salary 102 2000 103 2500 ... 204 2100 What I would like to get as a result is a combined table, with selecte columns from both tables in it. I.e.: list3 ----- ID name salary 101 bob - 102 rita 2500 ... Anyone who can ...

the criteria pane and table panes have disappered.
In my excel file I can't see the criteria and table panes and the functions will not reappear. ...

Viewing Formulas instead of formula results
I want to view all of the formulas in my worksheets without going through each cell and typing " " around each formula. Is there any way to do this so I can check all of my formulas at once? Meghan, here is one way, use Ctrl and ~, this will toggle between formulas and results -- Paul B Always backup your data before trying something new Using Excel 97 & 2000 Please post any response to the newsgroups so others can benefit from it ** remove news from my email address to reply by email ** "Meghan" <mmckee15@yahoo.com> wrote in message news:00db01c351f6$4eb0...

Label lines disappear when I copy to Powerpoint
Just created a pie chart in Excel 2007 with Labels which have a line joining the label to the pie segment, however when I copy and paste into Powerpoint the lines joining the labels to the segments disappear. Anyone know how to fix this? Need presentation for tomorrow! __________ Information from ESET Smart Security, version of virus signature database 4031 (20090423) __________ The message was checked by ESET Smart Security. http://www.eset.com ...

Copy chart with reference table
I have a pie chart neatly tied to a data table alongside. Now I want to copy both the chart and table to a different area of the sheet so I can modify the new data table and have 2 different charts. My issue is that the chart will only allow absolute references, so when I copy both the chart and table together the new chart still is tied to the old table - so I have to go and change the source data ranges one by one. Any way that I can do this easily? Copy the whole sheet. The copied chart links to the data on the copied sheet. Now cut the range that includes the copied char...

Conditional Formula?
Hi- I need help creating a formula that sums values in a list based on the value in an adjacent cell. Please see attached screen shot. Hopefully it explains what I'm trying to do. Thanks. +-------------------------------------------------------------------+ |Filename: excel help 3-10-06.gif | |Download: http://www.excelforum.com/attachment.php?postid=4442 | +-------------------------------------------------------------------+ -- rhovey ------------------------------------------------------------------------ rhovey's Profile: http://www.excelf...

order data by a rank
i need to create a form order by the militaty rank.This form is based in a query "qrystaff" with three fields: MemberID FullName and Rank.I´d like that the data were ordered firstly by MilitaryRank and secondly by FullName in alphabetical order.There is an easy way to do this? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access/201003/1 When you create queries, you're given the option to specify the sort order under each field. If the fields aren't in the order from left-to-right in the query grid, you can add ...

Turn data #2
Is there a way to turn the data in an Excel spreadsheet so that what is on the top is now on the side and what is on the side is now on top? I need to export my spreadsheet into Access but, I can't get it to format correctly in its current orientation. ~Make a backup copy of your data to work from (just in case, so you don't lose data) ~Highlight the entire range of data; click >Edit >Copy ~Choose a new location, on a different sheet perhaps; click >Edit >Paste Special and select the Transpose box; click OK Does that do it for you? thank you so much. You are a genius...

Pivot Table Refresh Problems
Hi All: Hoping that someone can help. I am trying to refresh a pivot table using the following code which was inserted on the Daily Production Output Sheet(both sheets in the same workbook). I am using Excel 2003 Private Sub Worksheet_Calculate() 'If data on this worksheet changes, refresh the pivot table Sheets("Daily Production Output").PivotTables("PivotTable3").RefreshTable End Sub The Calculation has been set to Automatic. However when I try to run the above code I am getting the following error" Run Time error 1044, Application defined or Object defin...

Data in columns not rows
I have file that is names, addresses and phone numbers. When I copy it into excel it comes out like this. (COLUMNA) NAME ADDRESS ADDRESS2 PHONE I need it to be like this COLUMNA COLUMNB COLUMNC COLUMND NAME ADDRESS ADDRESS2 PHONE Is there a way to change this without copying and pasting? Jenn Is the data consistently 4 rows? Or do you have varying sets? How far down Column A do the sets extend? If 4 rows per set try this..... In B1 enter =INDEX($A$1:$A$3000,(ROW()-1)*4+COLUMN()-1,1) Drag/copy across to E1 then select B1:E1 and drag/copy down u...

Pivot Table fields #2
Is there a way to format multiple pivot table fields at one time and have them refresh that way, rather than having to select each column and choose the format options? Thanks CLou: I think the solution for you is to open the Pivot Table Toolbar and select the Field Settings icon. This sets the format for all results for that particular field. One limitation that I haven't found a way around is getting a preferred column size to "stick" and not reset after a table refresh. Bruce >-----Original Message----- >Is there a way to format multiple pivot table fields at ...

pivot tables #19
I want to create a pivot table where I just have to enter the data in once where it will post to a general ledger, income/expense statement, profit & loss report and individual vendor accounts. Can this be done? -- tiki Hi tikitai, Look at my Excel Database Tutorial at http://edferrero.m6.net/DataTutor1.html You should be able to work it out form there. Ed Ferrero > I want to create a pivot table where I just have to enter the data in once where it will post to a general ledger, income/expense statement, profit & loss report and individual vendor accounts. Can this be done? &...

Using a Text / Data output as a cell reference
I am trying to use the end of a column as a divisor and need to convert what i guess is text into an actual cell reference if possible. In column A, say there are 13 data points ending at cell A13. I then try to turn that into a cell reference with the formula ="A"&TEXT(COUNTA(A1:A13,),"0") I put this formula in B1 the output of this is then A13 what i need this to do, is be able to divide any cell by A13. The reason I am going through all this trouble is that there are many columns and each has a different # of data points. I hope this is somewhat clear. Thanks ...

Modify Access 97 tables in Access 2003
How do I modify an Access 97 table using Access 2003 without converting the database? Is there any tool available? Rick This is only one person's experience... There is only one tool I'm familiar with that would let you do that, and it's called ... Access '97<g>! You've described HOW you want to do something. Now, if you'll describe a bit more about WHY you need this done, the folks here in the newsgroup may be able to offer more specific suggestions. Regards Jeff Boyce Microsoft Office/Access MVP "Rick" <Rick@discussions.microsoft.com...

Empty copy of database
G'Day All,I have a back-end database that contains both client data tables andsupporting tables with lists (e.g. Languages, Income Ranges etc) forcombos on the Forms. All relationships are defined, including twowith cascading deletes.I would like to take a "clean" copy of the database with client dataremoved and all autonumber key fields reset (to 1 for the first record).How may I achieve this?-- Regards,Pat GarardMelbourne, Australia_______________________ Create a new (blank) database.Import all tables: File | Get ExternalIn the Import dialog, click the More/Options button (...

Copy and paste from Excel to GridView
Hello, is it possible to copy and paste values taken from an Excel spreedsheet into the grid of a GridView in a ASP.NET page? Luigi On May 21, 4:29=A0pm, Luigi <Lu...@discussions.microsoft.com> wrote: > Hello, > is it possible to copy and paste values taken from an Excel spreedsheet i= nto > the grid of a GridView in a ASP.NET page? > > Luigi You mean from a single cell or entire table? The standard gridview control is a data-bound control for rendering data. It has no paste function. To copy and paste values from Excel you can use a separated textbox...