Edit A Source Value From A Destination Cell
Jul 29, 2008
I'm positive that this is a dumb question that's been answered elsewhere, so pardon my n00bness -- this is a one-time project for a non-programmer.
My manager needs to edit values in a spreadsheet. This person is non-technical and put off by spreadsheets, so I want to create a custom view of the data to show only the editable data (with pretty colors and fonts, etc.)
I have a source worksheet and a display worksheet. I need the display worksheet to show the values in the source worksheet, and I need changes in the display sheet to change the referenced data in the source sheet. For example, if cell Source:A1 = "thingamabob", then cell Display:A1 = "thingamabob". If user changes cell Display:A1 to "hoodgie", then cell Source:A1 changes to "hoodgie".
Essentially, the user has to be able to edit the source cell via the display cell.
Is that possible?
Again, please excuse my ignorance -- for all I know, this may be an automated function in Excel... but I've been trying to figure it out for two hours and so I thought I'd ask you nice people.
View 9 Replies
ADVERTISEMENT
Feb 23, 2014
I have tried numerous versions of macros I found. Most get the same error of; Range object error or script error.
Here is the formula: VLOOKUP(C$13,VESSELS_DATA,ROW(30:30)-12)
What I would like to do:
1) double click to on the cell with the formula
2) goto the worksheet "DATA_TABLES" that contains the Named Range "VESSELS_DATA"
3) go down the rows until the c13 is matched
4) offset column the same as in the above formula "Row(30:30)-12"
View 9 Replies
View Related
Dec 10, 2009
I am sure this is simple but cant get me head around it (tried paste & paste link).. I am wanting to copy formulas in file (A) to file (B) with data source linked in the file (A).
i want is when i copy the formulas to file (B) sheet 2 and it should retain the original path say H:excelfile A[sheet1]$G$1+....xls and source from which the data is being used.
View 7 Replies
View Related
Jan 17, 2014
I have extracted data from multiple sheets and now want to mark this data as "Paid" and for the macro to go back and change the value in the original sheet on a row in column "AR" to "PAID.
All the columns have the same data type in.
The extracted data contains the original sheet name and each row has a unique ID No to it.
I know I probably need to use INDEX and MATCH and then Offset to post value.
Attached worksheet shows the data and columns.
View 14 Replies
View Related
May 12, 2014
My first shot at a function! hows it look?
Code:
Function wbIsopen(wbname As String) As Boolean
On Error Resume Next
WbIsOpen - (workbooks(wbname).Name = wbname)
End Function
[Code]...
This is also my first shot at using a DIM and Set. I am attempting to move the data from 1 workbook sheet to another after it has been sorted. But if i only have the source open i want it to open the destination workbook and sheet then paste and close the source sheet. If the source is open I want it to go to the next command.
View 3 Replies
View Related
Oct 27, 2011
I have a source data sheet that has around 50 columns. I want to copy and past selected columns to a destination sheet.
View 1 Replies
View Related
May 22, 2014
I noticed that when I click on a cell in my Pivot Table a new worksheet pops up with the requested information (essentially a filtered version of my data source depending on what cell I clicked) is there a way for that information to appear on the same worksheet as my Pivot Table? And if so can I also edit what parts of the data source appear?
View 1 Replies
View Related
Jul 17, 2014
I am working with multiple workbooks with several tabs in each one. I need the forumula to update the "sheet name" from the source workbook even if the destination workbook is closed.
this is a portion of the forumula I am working with:
=SUM(IF(AND([L2_EI_Phase_II_monitoring_workbook.xlsx]Sheet25!$I$4>=182,[L2_EI_Phase_II_monitoring_workbook.xlsx]Sheet25!$I$4=182,
[L2_EI_Phase_II_monitoring_workbook.xlsx]Sheet26!$I$4=182,
[L2_EI_Phase_II_monitoring_workbook.xlsx]Sheet27!$I$4
View 9 Replies
View Related
Jun 6, 2014
I want see if it's possible to take the name of the source workbook and put it in a cell in my destination workbook and then use that as the reference in formulas. The source document title includes a date that changes yearly, or in some instances changes entirely, and I want to keep the formulas in the destination workbook intact. I don't want to have to go to every instance where I have used the source workbook and change the title. Instead I would like to copy the source workbook title, paste it into the destination in the same cell and have that propogate the needed changes into the destination workbook formulas.
View 2 Replies
View Related
Apr 14, 2013
I want to fill all a column bottom-up but without specify cells in format "A1500:A2" like
VB:
Range("H1578").Select
Selection.AutoFill Destination:=Range("H2:H1578")
{H1578 have a formula so...}
I have data in the first 7 columns, with a variable(Range) for the first 5 columns
VB:
Dim oRange As Range
Set oRange = ActiveSheet.Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(89, 4))
{The first cell in it is A1}
My question is : How to fill Range("H2:H1578") using my ''oRange'' variable?
I try with OffSet but my code didn't work. I need to do the same with the 6 columns to the right and then plot in a chart.
View 4 Replies
View Related
Jun 22, 2006
How do I change the destination Cell in a workbook from data entered in a macro?
As an example If I press the command button the first time the Textbox1.value is sent to Cells(11, 5).
The next time I press the Same command button the data Textbox1.value is sent to Cells (12, 5)
Mainly is the rows I want to change not the coloums!
Struggled with this for weeks now which I am sure is a simple issue to code.
I am struggling to make people understand the problem though so I hope this works!
View 9 Replies
View Related
Jan 9, 2014
Im trying to adapt a formula destination to cell.
I have a formula on cell B2 that is directed do other excel file, It appears like [1.xlsx] 1 being the name of the other file.
What I am trying to is make something like:
cell A2 = 1
and ["A2".xlsx]
so whenever I change the A2 cell, the destination also changes.
View 4 Replies
View Related
Mar 28, 2009
There is a big range of cells with normal numbers (ex. 100, 150 .. etc), but I need to convert them in the following formulas that give the same numbers as a result: for example if the cell value is 100, I need to convert it in =if(iserror(100);0;100) and so on with all other values. Is it possible this to be done automatically for all cells?
View 5 Replies
View Related
Feb 15, 2010
I would like a button in (Sheet2) to do this operation when clicked: jump to (Sheet1) and enter Sheet2's name into cell B4, where this button is used in many sheets similar to Sheet2(aka2,3,4,5,6...) that all do the same to jump to sheet 1.
View 2 Replies
View Related
Oct 18, 2013
My goal would be to click on a cell within a range of cells (in a column) and have the value copied to a specified destination cell. There would be a few different columns with source cells and two destination cells. Each column would to copy to a specific cell. My ojective is basically to deal with different processing times (days, weeks, months).
Here's a little visual, clicking on C4 for example would copy the value "5" to cell B2. Clicking on D4 would copy "12" to A2.
row/col
A
B
C
D
E
1
Days
Weeks
Processing time X (weeks)
Processing time Y (in days)
Processing time Z (in weeks)
2
3
10
110
3
4
11
111
4
5
12
112
5
6
13
113
View 1 Replies
View Related
Oct 30, 2013
This is the current layout I have:
Sheet 1: A1:B20, is linked to Sheet 2: A1:B20.
I need to copy and paste data from the internet into Sheet 2, but it's not in the correct order, so I must swap column A and column B in Sheet 2 only. The problem with this is that when I swap them, the linked cells swap as well, which destroys my data on Sheet 1 columns C-Z.
Is there a way that I can keep the cells on Sheet 1 completely static, so that it only reads the data I put into the corresponding cell on Sheet 2?
Or maybe, is there a way I can just swap the cell information in Sheet 2 without messing around with the formatting? Just a complete swap of A1>B1 and B1>A1?
View 4 Replies
View Related
Jul 31, 2008
I have a sheet where it has got hyperlinks to many cells. When I click on the Hyperlink, the destination cell selected should start blinking.
View 9 Replies
View Related
Feb 22, 2012
I am trying to create a destination cell by concatenating several cells and text together. A1 = Folder Name, B1 = File Name, C1 = Tab and Cell. In D1, I am creating a formula that concatenates all the cells to create a destination. This obviously doesn't work so I run a macro to copy, paste special values which displays this in E1, ='FolderName[File Name]Tab'!Cell. The pasted formula works but only after you click on the cell and hit the enter key. Is there a better way to do this?
View 2 Replies
View Related
Mar 31, 2014
I have browsed for two days looking for material on effecting this. The technique quoted most is watch for undolist for paste & auto fill then undo the change and paste the value again with specific formatting needs. This technique can be implemented in a worksheet_change event handler. The problem that I am facing is that the user can copy and past while my VBA is running. Once it is caught inside my own application class object event handler all that undo stack is already cleared. Therefore I have nothing that tells me if the user has entered the value via a paste action. By the time the value is pasted, all that formatting would have been entered. For example, a value that is interpreted by another workbook as DATE will have my destination formatting changed to DATE as well even though it is designed to be TEXT at design time.
So far I have not been able to think of a scheme to deal with this.
My basic intention is to always ensure the destination cell formatting remains as TEXT. If something is already interpreted as DATE after the paste even if I can convert to TEXT the string will appear totally differently. I have to find a way to paste the whole thing as TEXT in order to keep the string the same. The problem is that there is no intrinsic PASTE event in VBA. Without a method to undo that paste I cannot catch this at all.
View 1 Replies
View Related
Sep 27, 2013
I have this code that copies existing folders to a new destination path. How do I change the code so that the From path and To path is based on cell value rather than writing it into the code?
Code:
Sub Copy_Folders()Set Fobj = CreateObject("Scripting.FileSystemObject")
Fobj.CopyFolder "C:UsersHengDocuments1243-01234photos", "C:UsersHengDocumentsCasework1243-01234photos"
End Sub
View 9 Replies
View Related
Jan 30, 2007
i am writing a custom copy/paste routine which will paste data from a selection if the cell being pasted to is not locked.
The user will select the text for coping and then launch the routine.
The routine will query for destination cell and then process the paste.
This is my routine so far:
Sub testCopy()
Dim c As Range
Dim MyRange As Range
Dim Dest As Range
Set MyRange = Selection
Set Dest = Application.InputBox(prompt:="Select a cell", _
Title:="Paste Destination", Type:=8)
For Each c In MyRange
If Dest.Locked = False Then
Dest.Value = c.Value
End If
'dest = ?
Next c
End Sub
What I need to know is how to increment the dest reference so it is in the same 'relative' position from the initial dest that c (from the selection) is in or is there a better way to do this?
View 3 Replies
View Related
Oct 24, 2013
I'm creating a Macro to paste a series of formulas from a template in hidden rows above to cells further down in the same worksheet. My code works fine if I set an absolute range as the Destination - here's a portion of it:
Sub TimelineRow150()
Dim TimelineMatch As Integer, ProjectPlan As Object
TimelineMatch = ActiveCell.Offset(0, -1).Value
If TimelineMatch = 26 Then
Range("E26:AQ28").Select
[Code]....
I'd like to be able to copy and paste this code to run it multiple times in the same worksheet, (the TimelineMatch variable actually has 26 different options). Is there a way to replace the [Range("E150:AQ152").Select] portion of the code with a range that is relative to the Active Cell? (I'd instruct users that cell E be the Active cell before running the Macro, and E to AQ is still the range I need the copied data to appear.) I've tried using ActiveCell.Offset, but I keep getting an error of Method 'Range of Object' _Global' failed.
View 3 Replies
View Related
May 27, 2009
I have two workbooks. I am copying cells data from one workbook A to workbook B with macro - pretty easy. Now, after selecting and copying cell data in workbook A and switching to workbook B I want to be able to pause the macro and wait for the user to chose a correct cell where he/she wants to paste the data. After the selection the macro would automatically paste the data in selected cell. Since I am completely newbie any help would be appreciated. Here is the macro I have so far :
Sub CopyVIN()
'
' CopyVIN Macro
' Macro recorded 5/26/2009 by Pacific Coachworks
'
Sheets("Sheet1").Select
Range("G9:R9").Select
Selection.Copy
Sheets("For copying VIN").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Cami's Production Schedule.xls").Activate
' Here I'd like to have a code for waiting for a user selection and pasting the data automaticlly.
End Sub
View 9 Replies
View Related
Apr 1, 2009
Not sure if this can be done, still a rookie at this stuff. Everything works but can something be wrote into code too change list source?
View 5 Replies
View Related
May 24, 2006
I suddenly can not edit in a cell by double-clicking on it. I can only edit in the fx bar on top. Other sheets in the workbook still work normally. What causes this, how can I fix it and how can I make sure it doesn't
View 5 Replies
View Related
Feb 22, 2008
i have an excel sheet that has a cell with a formula in it. This cell has been locked by the previous user.
I am unable to click on it and it because it is showing a #NAME? error, i need to edit it somehow. Is there some way i can edit it?
View 9 Replies
View Related
May 31, 2009
I've got this small (?) problem here. In a range of about 150 rows and 30 colums, I have to edit the cell formula, i.e. add something to the existing formula. For instance, in all the cells in column C I need to add " + D9" to the existing formula, in all the cells of column D I have to add " + E9". Is there a way to do this in VBA ? I would already happy to do it column by column, but I'm really reluctant to edit 4500 cells manually.
View 2 Replies
View Related
Jun 27, 2007
I want to edit the cell or Specific Range when it is in autofilter mode. Here is the example i want to filter as long as the first column criteria is 1
Before filter
1 string 1
2 string 2
1 string 1-1
3 string 3
after filtered with criteria = 1
1 string 1
1 string 1-1
now i want to edit both "string 1" and "string 1-1" to "abcd". How am i going to get the the autofilter to return me a range for me to change the cells value.
View 9 Replies
View Related
May 23, 2007
I have a worksheet with about a dozen cell comments in one column. I am able to add and edit cell comments to other cells throughout the worksheete, but I can not edit any of the existing comments. I've tried all of the usual ways to edit the comment (Shift/F2, Insert-Edit Comment, etc.), but nothing opens the comment in edit mode.
View 3 Replies
View Related
Dec 23, 2008
Trying to copy a range from Sender.xls (sheet) Lists backstage
onto Userform.xls (sheet) Behind the Scenes
When trying to copy the values within a multicell range, the destination cell range (same size) becomes blank.
View 2 Replies
View Related
