Who's good with Excel?

[Bryson]

So, I have a weird but simple-sounding Excel question:

 

Can I make two (or more) cells match each other exactly? ie: I want to say B4, C16 and F24 to all have the same value at all times, regardless of which one I edit. So the updates go in both directions, as it were. None of them would be a formula, just a value.

 

Basically we have some forms that have the event times on, but they're in different formats for FOH, Technical, Front Desk etc. Different members of staff input into different sheets, but everyone needs all of the info available on "their" sheet which is in a format they're used to.

 

Does that make sense?

[Brian]

Are these values on the same worksheet or different ones? If they are on different ones then you'll need to attach a bit of VBA to the sheet which is triggered when that sheet is updated. It can then intelligently copy the new value to the other sheets. If they are on the same sheet then a bit of VBA which uses an off-sheet cell as a static variable which is used to update the cells when a change is triggered.

[david.elsbury]

Bryson, does this link help at all?

 

http://www.pcreview.co.uk/forums/thread-2825587.php

[HobitLight]

You could set it up so that if you edit one of them, the other two would update themselves. But this isn't what you want. I suppose that you could use a spare cell to be used as a hidden variable to store your data and use some VBA code or something to update the others when one's changed. I'll have a think but I've got to do my physics coursework now! ;)

[adamcoppard]

A hacky way would to be have one input cell, and set all of the aformentiond cells to that cell. So, you could have a worksheet time, which this is entered in, then, all of the cells are =time!$A$1

[Bryson]

They're on different tabs of the same workbook, so that should work.

 

Care to share an example of the VBA that would do it?

 

 

A concurrent post has been automatically merged from this point on.

 

Davids thing looks like it'll work....

[JackPott]

May or may not be quite what you want but Excel has a sort of picture in picture 'camera' mode, so you would actually be editing the same cell but it would be visible in two places. You have to right click on the tool bar and add a button to use it, as far as I know it doesn't appear in any menu.

 

While you're there, two of Excels best kept secrets also live in the secret toolbar stash, 'Select Block' and 'Select Visible Cells'

[Bryson]

Looks good. Now...I've never done any Excel Scripting before. Could soemone decipher what I need to do to make this work?

 

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrH:
Application.EnableEvents = False
Select Case Target.Address
Case "$A$1"
Range("B1").Value = Target.Value
Case "$B$1"
Range("A1").Value = Target.Value
End Select
ErrH:
Application.EnableEvents = True
End Sub

 

Does it matter that the cells will be spread across different sheets?

[benash]

I think (and may well be proven wrong) that you will need to put the sheet reference in with the cell reference so it'd look something like "Sheet1$A$1" or something similar. If you insert a formula in one sheet and reference a cell in another sheet (by clicking on it) it will give you the exact syntax.

 

HTH

[David Buffham]

The function below should work in Sheet1's code module to copy the required cell into the specified cells of Sheet2 and Sheet3. To use it just right-click on the Sheet1 tab, select View Code, and paste the function:

 

Private Sub Worksheet_Change(ByVal ChangedCell As Range)

 

Select Case ChangedCell.Address

 

Case "$B$4"

Sheet2.Range("C16").Value = ChangedCell.Value

Sheet3.Range("F24").Value = ChangedCell.Value

 

End Select

 

End Sub

 

 

To get the other sheets to copy back into Sheet1, etc.., just copy the same code into the code module for each worksheet, and adjust Sheetn, source and destination cells accordingly.

 

Even if you've renamed the sheets, the objects are still referred to here as Sheet1, Sheet2, etc..

 

Hope this works for you - it seems to work OK for me on Excel 2003 & 97.

[andy_s]

Even if you've renamed the sheets, the objects are still referred to here as Sheet1, Sheet2, etc..

really? when I bring data from one worksheet to another in the same workbook it appears in the destination cell as (for example) " ='staff costs Day 1 '!J23 " (ie the name of the worksheet, not the number.....

 

this is excel 2003. don't know if it's different on other versions.

[boatman]

Even if you've renamed the sheets, the objects are still referred to here as Sheet1, Sheet2, etc..

really? when I bring data from one worksheet to another in the same workbook it appears in the destination cell as (for example) " ='staff costs Day 1 '!J23 " (ie the name of the worksheet, not the number.....

 

this is excel 2003. don't know if it's different on other versions.

 

Yes this code will work if the sheets are renamed as 'Sheet1.Range().Value' refers to a logical cell name within Visual Basic which doesn't have to be the same as the physical name.

 

If you need any help with Excel, then Chip Pearson's site has a lot of good stuff.

[Lion Tamer]

I have tested the code below and it does work although you will need to make sure that macros are enabled.

 

	Dim Temp As String

Private Sub Workbook_Open()
Temp = Sheet1.Range("A1").Value
Do
	If Sheet1.Range("A1").Value <> Temp Then
		Sheet2.Range("A1").Value = Sheet1.Range("A1").Value
		Sheet3.Range("A1").Value = Sheet1.Range("A1").Value
		Temp = Sheet1.Range("A1").Value
	ElseIf Sheet2.Range("A1").Value <> Temp Then
		Sheet1.Range("A1").Value = Sheet2.Range("A1").Value
		Sheet3.Range("A1").Value = Sheet2.Range("A1").Value
		Temp = Sheet2.Range("A1").Value
	ElseIf Sheet3.Range("A1").Value <> Temp Then
		Sheet1.Range("A1").Value = Sheet3.Range("A1").Value
		Sheet2.Range("A1").Value = Sheet3.Range("A1").Value
		Temp = Sheet3.Range("A1").Value
	End If
	DoEvents
Loop
End Sub

 

Open the Visual Basic Editor, Double click on the item called "ThisWorkbook" in the right hand menu and paste in the code above (substituting your cell reference to whatever you want).

 

Now it will start running as soon as you open the workbook and continue running in the background till you close it - there is probably a more efficient way to do it but until I have worked out which event is a single cell change this seemed like the quickest and easiest way to do it.

 

 

Even if you've renamed the sheets, the objects are still referred to here as Sheet1, Sheet2, etc..

This is correct - I have tested it in Office 2003.

 

Hope that Helps

 

Jem

 

EDIT: Scrub What I said - David Buffham's way should work more efficiently than mine - I just misread it the first time and picked the wrong event, which is caused in part by the fact that I tend to work through VB6 not VBA for most of what I do.

[Heapsy]

Seeing as we are having an excel training session I wonder if anyone might be able to help me with my little Excel issue. I'm sure this solution will be a lot simpler but Excel formulae are really not my strong point!

I want to have a template excel sheet that I can use when creating quotes which includes subbing in equipment from a variety of other suppliers. Ideally I would like to insert the list price in one column, have the discount as a percentage in the next (which would be different and edited for each quote) and then the new price in the following column. Would anyone be able to assist me in the 'code' I need to achieve this.

 

Thanks in advance people,

 

Mark

[Bryson]

There's probably a more elegant way but this works:

 

If the list price is in B4 and the discount is in C4

 

=SUM(B4-(B4*(C4/100)))