Reference Another Cell In Full

[js0873]

I would like an ability to reference all the contents from one cell in another cell. That is, I have a cell that is formatted a certain way, with comments in the cell, hyperlinks in it, and a formula or constant. I’d like to reference the entire contents in another location in my spreadsheet. However, if I do a simple reference, like =A1 for example, that copies the data/formula, but not the formatting, comments, or hyperlinks. I’d like an ability to copy everything about cell A1. That way, if I alter A1’s comment or hyperlink, it would change that in the 2nd location as well. Actually, I have rows of data I want to do that way, so I had planned to do a =A1 and autofill across, but I can’t find any way to get a complete reference to another cell. I can understand that not being the Excel default, but I would hope there was some way to do this. Any ideas?

[6StringJazzer]

It's not just the default, it's a fundamental design decision. In Excel, cells can reference the values/formulas in other cells but information about the formatting of other cells is not readily available, and dynamic formatting of a cell ("conditional formatting") can depend only on values, not on formats.

It is possible to accomplish what you want using VBA code. Off the top of my head it would detect any change in the worksheet, determine if any cells reference the cell that changed, then apply any formatting from the changed cell to the referencing cell.

If you want to pursue it I can probably help. If so an example workbook would be helpful.

[js0873]

It's not just the default, it's a fundamental design decision. In Excel, cells can reference the values/formulas in other cells but information about the formatting of other cells is not readily available, and dynamic formatting of a cell ("conditional formatting") can depend only on values, not on formats.

It is possible to accomplish what you want using VBA code. Off the top of my head it would detect any change in the worksheet, determine if any cells reference the cell that changed, then apply any formatting from the changed cell to the referencing cell.

If you want to pursue it I can probably help. If so an example workbook would be helpful.

Thanks. Here's an example workbook. Row 2 has the complete data, and row 10 was built by referencing row 2 all the way from column A thru AD. I'd like any change in row 2 to be automatically reflected in row 10, and I'll have multiple places in my spreadsheet where one row references another. Hopefully you'll find a way to do this.

[6StringJazzer]

Attached is your file with code to do what you want. You should probably test it to make sure.

You must, of course, enable macros to run.

Any time you change the value of a cell, the code will automatically check to see if any other cells reference that cell. If they do, then the format of the changed cell will be copied to the referencing cell. Note that the code cannot detect if the formatting of a cell changes, only if the value/formula changes.

If you want to synchronize the formatting of all such cells without waiting for values to change, you can press the button in the upper left corner and it will cycle through all formulas that are simple cell references and copy formats for those.

This code is specific to the worksheet you are using, but is otherwise generic and can be used for any data. It can also be copied to any worksheet, or be modified slightly to work through an entire workbook.

[js0873]

Attached is your file with code to do what you want. You should probably test it to make sure.

You must, of course, enable macros to run.

Any time you change the value of a cell, the code will automatically check to see if any other cells reference that cell. If they do, then the format of the changed cell will be copied to the referencing cell. Note that the code cannot detect if the formatting of a cell changes, only if the value/formula changes.

If you want to synchronize the formatting of all such cells without waiting for values to change, you can press the button in the upper left corner and it will cycle through all formulas that are simple cell references and copy formats for those.

This code is specific to the worksheet you are using, but is otherwise generic and can be used for any data. It can also be copied to any worksheet, or be modified slightly to work through an entire workbook.

This is excellent!! Thanks. A few things, though ...

The part that's missing, unless I'm doing it wrong, is the copying of hyperlinks and comments from one cell to the other. Can that be done at all?

Also, is there any way to specify that you don't want certain referencing cells to participate in the copy? That is, can certain cells referencing another cell be excluded, if I want, from the copy? Thanks again though.

[6StringJazzer]

The part that's missing, unless I'm doing it wrong, is the copying of hyperlinks and comments from one cell to the other. Can that be done at all?

Hmmm, I coded in the part for hyperlinks but perhaps did not test it thoroughly. I will take another look. I missed the part about comments but that should be possible to.

Also, is there any way to specify that you don't want certain referencing cells to participate in the copy? That is, can certain cells referencing another cell be excluded, if I want, from the copy? Thanks again though.

Yes, it's certainly possible. You can either hard-code in which cells to ignore, or have some criteria baked into the page somehow. What are your criteria for opting out?

[6StringJazzer]

I guess I coded the hyperlinks part in my dreams.

OK, repairs in place. Please note that changes to hyperlinks or comments will not trigger a refresh, only changes to actual cell contents.

[js0873]

What are your criteria for opting out?

Great question, since I hadn't really thought through how certain cells might opt out. In thinking about it more, since that's a tough one, let's ditch that idea until I see if it really presents a problem.

[js0873]

I guess I coded the hyperlinks part in my dreams.

OK, repairs in place. Please note that changes to hyperlinks or comments will not trigger a refresh, only changes to actual cell contents.

Well, I can't use the "Sync All Formats" button. It's giving me a run-time error 1004. Can that be fixed? Thanks again.

[6StringJazzer]

Error in determining if a cell already has a comment.

Error in attempting to add a subaddress if it's null.

I think it's fixed now.

[js0873]

This looks really good. I'll have to play with it some more before I can give you extended feedback. But I noticed a couple of things, neither of which may be able to be resolved, but here goes:

1) I lose the ability to UNDO any action once with the macro in place. For a spreadsheet like the one I needed this for that may be a showstopper, since I often make sample changes just to see how things might be and then UNDO those changes. Is that capable of being corrected, not having undo capability?

2) Much less important, but when I make a comment the default size of the comment box is small and makes most of the text not visible, so I generally expand the box size. If I do a normal copy of the cell, the comment text box remains the same size as what I re-sized it to. But whenever I make a change with the macro in place, the comment on the copied to cell gets re sized to the small default, so I have to re-size it. Can the sizing be maintained?

[shg]

When VBA modifies a sheet, the UNDO buffer is flushed.

[js0873]

Actually then, if there's no way around it, it might be better not to automatically change cells that reference others on every change, but just allow the button to make those changes when I'm ready. That way I'll realize and think about be losing the undo before I press the button and it'll be no big deal.

[6StringJazzer]

I believe that VBA has the ability to provide an Undo action using the OnUndo method (I've never done it) but it has to be coded from scratch and that means that the code that modifies the formats has to keep a record of what it did in case the undo is requested. That is a bunch of work so if the button works for you, that's the way to go. Meantime you will have to disable the Worksheet_Change sub. You can comment it out, or insert "Exit Sub" as the first line, if you want to preserve the code for later.

[js0873]

I believe that VBA has the ability to provide an Undo action using the OnUndo method (I've never done it) but it has to be coded from scratch and that means that the code that modifies the formats has to keep a record of what it did in case the undo is requested. That is a bunch of work so if the button works for you, that's the way to go. Meantime you will have to disable the Worksheet_Change sub. You can comment it out, or insert "Exit Sub" as the first line, if you want to preserve the code for later.

OK, I took it on myself to try to learn a bit of VBA from your code, but I'm having some problems that I'm hoping you can help me out with ...

(1) Even if I insert an "Exit Sub" or "End Sub" right after
Private Sub Worksheet_Change(ByVal Target As Range)

it still makes the instant change in my workbook. I know that should work, but for some reason I can't figure out why it isn't.

(2) Even though Application.ScreenUpdating = False looks to be specified where in the subs to where I shouldn't see the changes being made when the button is pressed, it still shows all the changes. Is it getting reset in one of the public subs?

(3) What does the StylePercent Sub do?

Thanks in advance.

[6StringJazzer]

I'll look at this in more detail, but here's a couple of quick things.

(2) Even though Application.ScreenUpdating = False looks to be specified where in the subs to where I shouldn't see the changes being made when the button is pressed, it still shows all the changes. Is it getting reset in one of the public subs?

I noticed this too. I suspect that the Paste operation is forcing a cell selection on the screen that is unaffected by ScreenUpdating. I do not know if there is a way to avoid this. I chose doing a copy/paste format because the alternative would be to manually code every possible formatting element that could be there, which gets pretty big.

(3) What does the StylePercent Sub do?

I started that and abandoned it but didn't delete it. It's unused.

I'll post an update that disables the event handler in a little while.

[6StringJazzer]

Revision attached that disables the event handling but leaves the code in place. I don't know why you didn't have any luck with that.

I also removed the extraneous Sub.

[js0873]

Hmm. I still get the automatic updating going on. For example, if I type something in H2 it automatically goes into H10. Does it not do that for you?

[6StringJazzer]

Look at the formula in H10 and you will see why this happens.

=H2

That was how it was set up when we started this whole thing. What does not happen is the automatic copying of formats.

[js0873]

Wow. Now I feel really stupid. That's why I felt that exiting the sub wasn't working either. Dumb, dumb, dumb. Thanks.

[js0873]

I have a few more tweaks to request. If the formatting of a cell with a hyperlink or comment changed, it wasn't passing the format change along with it. I found that by moving the CopyFormat statement from the beginning to the end of the CloneCell sub, after the copying of hyperlinks and comments, corrected that. I guess I'm learning a little.

But I'd also like the macro to be available to all sheets in the workbook. I don't need a button for that, so just executing the macro with Tools/Macro would be fine, and I can set up a hotkey if needed. But I'm not sure how to make the macro work on all worksheets. I copied the code to the ThisWorkbook module it still only works on Sheet1 - yet I can't see in the code where it is sheet specific (I'm a newbie for sure). Can you make it a macro for the entire workbook? That is, it would only execute on the sheet I'm on when I issue it the macro, but I'd like it available for all sheets.

[6StringJazzer]

...I'd also like the macro to be available to all sheets in the workbook. I don't need a button for that, so just executing the macro with Tools/Macro would be fine, and I can set up a hotkey if needed. But I'm not sure how to make the macro work on all worksheets. I copied the code to the ThisWorkbook module it still only works on Sheet1 - yet I can't see in the code where it is sheet specific (I'm a newbie for sure). Can you make it a macro for the entire workbook? That is, it would only execute on the sheet I'm on when I issue it the macro, but I'd like it available for all sheets.

There is a sub call buttonSync_Click which is executed by Excel when the user presses the button. All it does is call the SyncAllFormats sub. All of this code is located in the module for this particular worksheet. In VBA, the execution assumes certain things about what the code means based on where it is located. For example, if you have something like

Please Login or Register  to view this content.

it refers to the cell A1 within the sheet where the code is located. There is no explicit reference to the sheet, it is just assumed to be the sheet that owns the code.

Further in SyncAllFormats, you will notice the line

Please Login or Register  to view this content.

In VBA, the object "Me" refers to whatever module owns the code, so in this case it is the worksheet. It is a way of explicitly showing what is being referred to, although it is optional (UsedRange would imply that it means this sheet, the same as the Range example above).

If we want to generalize the code we should move this code to a Module instead of a sheet. It can then be modified to refer to whatever sheet is currently active. We simply change Me to ActiveSheet. I also added an argument to CloneCell to provide the worksheet containing the cells; I could have just made that ActiveSheet too but it is more general to do it this way. See the attached.

[js0873]

I'm about 99% there now. BTW, one of the recent changes (about a couple back) corrected the "screen updating" part.

The remaining piece of my puzzle isn't something really unique to the macro,but unique to referencing a cell in general. Whenever I use a symbol, like combining an Ariel font character with a Wingdings one in a cell (see N2 in the example, where I make a "5 star"), referencing it doesn't display the characters correctly. The only way I've been able to have it show the same in a referencing cell is to copy the contents to it vs referencing it in the other cell, which is not what I want. Is there a way for a referencing to display this mixed font correctly? Can a macro do anything?

[6StringJazzer]

I haven't written any code for it but you would have to address the formatting of the text at the character level. That is, loop through every character in the cell to query its format and then apply it to the corresponding character in the other cell. Here is code I generated with the macro recorder to change three characters to bold:

Please Login or Register  to view this content.

The rest is left as an exercise for the student

[js0873]

OK, the student is stumped. If I mix fonts within a cell, it still seems to use the first font specified for the entire cell. For example, if I code:

ActiveCell.Characters(Start:=1, Length:=1).Font.Name = "Ariel"
ActiveCell.Characters(Start:=2, Length:=1).Font.Name = "Wingdings 2"

so that the first character is a "5" and the second is a wingdings star, I get the same 5ê that I always had.

If I reverse it and put Wingdings first, the entire cell shows as Wingdings (box and star). I can't seem to mix font types within a cell. Surely you can do that, right?

[6StringJazzer]

I did a little experimenting with it. My conclusion is that formatting at the character level only applies to cells with data and not to cells with formulas, which means that there is no way to address formatting at the character level for the application you have in mind. The best you could do is have your macro actually copy the text, and the formatting would be carried along with it.

[js0873]

Well, at least the student wasn't as lost as he thought he was then. Just to close the point, if I copy the data I'll forever lose the referencing, right? That is, if I change the original data that the cell was pointing to it'll never pick that up.

[shg]

js, please stop quoting whole posts. It's just clutter.

Thanks.

[6StringJazzer]

Correct. So if you really need to have format cloning at the character level, the only thing I can think of is to have one sheet that serves as a map for which cells refer to which ones, then another sheet that has the actual formats and data. Give some thought to what you really need and whether it's worth it.

Actually, I am still trying to figure out why you need the data twice on the same sheet in the same formats....

[js0873]

I'm not saying it's the best way of doing things, but ....

I'm building potential investment portfolios. I have a spreadsheet full of mutual fund choices. chock full of information across a row of important data about each particular fund. So at the top I may have 100+ fund choices, and at the bottom of the sheet I start building the selected set of funds I might opt to invest in. At the bottom I want to see the exact same information on the funds I tentatively select, without having to hide all the rows above of funds I didn't select. So when I select say 8 funds below, I want to simply refer the the same data above it, that has comments, hyperlinks, and so on. So in summary, the data at the top is the comprehensive list of all funds, and the one below is the ones I might choose. Does that help? Is there a much better way of doing this in your mind?

[rduran7915]

Hello all
kinda rusty at the excel.
This particular Fundraiser sale are dinner tickets of 500
5 columns starting row 4;
this is what i am trying to do
A =ticket#, B=amount($6.00), C is fx if [column D=PAID then column B=$6.00 if not $0.00], D=PAID or blank, E=name
B2 = sum column B
D2 = B2/6 "which will give ticket # sold
but my if formula does not work and does not give me an error..

=IF(D9paid,"B9+$6.00","B9+$0.00")

is this correct any help very well received

The next part I do not have a clue as to how to do...
after this I would like to make a report from the if true/false
those that are true print all rows in two columns Paid report 1
that what is false unsold tickets report 2
hiding the formulas of course

[arthurbr]

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.