+ Reply to Thread
Results 1 to 4 of 4

Reference a cell and keep formating?

  1. #1
    Registered User
    Join Date
    05-11-2007
    Posts
    2

    Reference a cell and keep formating?

    I am having a heck of a time finding a way to make this work...

    I have a workbook with many sheets in it. One of these sheets is a "Base" sheet where the user fills in a list of names (along with other base information). Now these names may be Bold, Underlined, or in Regular text depending on who it is. The problem sheet is called "Summary" which provides formulas to make all kinds of calculations based off information in these other sheets. For the name column it is just doing a simple "=Base!A10", "=Base!A11", "=Base!A12", and so on. This copies the names automatically as they change in the "Base", which is what I want... but I want the formatting to automatically copy over too. I don't want them to have to run a macro either, I want it automatic.

    Also, there is one other column that is just referencing another sheets column (no calculations, just like the names). This column contains either a number or a date (depending on the corresponding person). When a date is inserted into the "Base" it changes the format of the cell to the date type. But when this cell is then referenced in "Summary" it is a general type and so the date is converted to its funky number representation. I would also like it to copy/reference the date type over in this case.

    I was thinking I could use conditional formatting which would always be true, but you have to use statically set formats, I couldn't reference a cells format. At least not that I could see.

    Any help or ideas on solving this would be great. Thank you very much.

  2. #2
    Forum Contributor
    Join Date
    03-14-2006
    Location
    Pakistan
    Posts
    1,791

    Lightbulb

    Quote Originally Posted by cood
    I am having a heck of a time finding a way to make this work...

    I have a workbook with many sheets in it. One of these sheets is a "Base" sheet where the user fills in a list of names (along with other base information). Now these names may be Bold, Underlined, or in Regular text depending on who it is. The problem sheet is called "Summary" which provides formulas to make all kinds of calculations based off information in these other sheets. For the name column it is just doing a simple "=Base!A10", "=Base!A11", "=Base!A12", and so on. This copies the names automatically as they change in the "Base", which is what I want... but I want the formatting to automatically copy over too. I don't want them to have to run a macro either, I want it automatic.

    Also, there is one other column that is just referencing another sheets column (no calculations, just like the names). This column contains either a number or a date (depending on the corresponding person). When a date is inserted into the "Base" it changes the format of the cell to the date type. But when this cell is then referenced in "Summary" it is a general type and so the date is converted to its funky number representation. I would also like it to copy/reference the date type over in this case.

    I was thinking I could use conditional formatting which would always be true, but you have to use statically set formats, I couldn't reference a cells format. At least not that I could see.

    Any help or ideas on solving this would be great. Thank you very much.
    Hi
    Formats cannot be linked through formulas. (and you don'd want to use macros).
    So one thing that you can do is to do a paste special as formats whenever you want to see other sheet as the Base sheet. This is not automated of course you have to do it manually when you change formats in Base sheet or when you want to see other sheet as the Base sheet.

  3. #3
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967
    Try the following event driven macro pasted into the module for the sheet in which the formula are going to be placed.


    Please Login or Register  to view this content.

    This should give the appearance of being automatic even though a macro is involved.
    Martin

  4. #4
    Registered User
    Join Date
    05-11-2007
    Posts
    2
    Awesome mrice, I didn't think to use an event handler. I changed the code around to fit my needs and it works almost perfect. The only issue (I could live with it, but a fix would be great too) is that the event procedure "Worksheet_Change" is only called when a VALUE is changed in the sheet, not when just a FORMATTING change is made. So if a user goes and makes a name bold in the "Base", it will not change in the "Summary" until a value is entered in a cell as well. Right now I just have the event handler copying the whole name range; this made the problem more bearable in that it would at least change sometime in the future. But if I could get it to change on format changes as well, then I could just copy the changed cells.

    Thanks a lot.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1