Duplicate Cell properties automatically in Quattro Pro - Documentation - WordPerfect Office - OfficeCommunity.com
  • Not Answered

Duplicate Cell properties automatically in Quattro Pro


Normally I work through challenges myself and learn as I go, but at the moment, time is in a crunch so I am in need to do a bit of brain picking to see my current project through in the fast lane.

I am fairly new to Quattro Pro and just bumping around using what I know from Open Office and Excel 97.

My current task at hand is:
I have multiple sheets, For sake of example, SheetA will be a master all other sheets contain parts (various cell content) from the master. This is not an problem, but what I am looking to do is not only duplicate cell content, but also cell properties. In other words, if I change cell "master:A2" to red with white text I would want the targeted cell "page2:B4" to also change to red with white text.

Seems it should be simple enough...

Thank you


19 Replies

  • Since I don't use Quattro Pro for more than simple single-sheet projects, perhaps Charles Cork can help. See his free PDF book, "Quattro Pro Formulas, Functions, and Macros" at http://corklaw.com/Tech/QP/
  • I don't know a way to do this that is both automatic (i.e., requires no user intervention, even to start a macro) and applicable to all color properties. Two lesser options come to mind:

    1. A macro can do this close to automatically, but it would take writing the macro and then clicking a button to run it each time you want to update colors. If that would work for you, the QP book that Barry mentions may help. And if you want to discuss the macro-coding options, we can do that here.

    2. Or, if there is a rule for changing colors, say red for the text of negative numbers or blue for numbers within a particular range, that can be set to change automatically on a per-sheet basis. This wouldn't change the background colors of the cell, though, so it doesn't appear to be what you want.

  • In reply to Charles Cork:

    Thank you for your input. Clicking a button to update would work since the sheet would only be used by me or a couple select people.
    Let me explain in more detail what I am looking to do so hopefully I can be pointed to someplace to learn by example since this is something I am looking to accomplish without having to invest hours (which I don't have) in learning everything in order to learn what I need at this moment.

    I just took on a position managing the kitchen of a nursing home. As you might imagine, there are many diet restrictions, modifications etc. Only certain groups of staff need to have reference sheets for certain meals/tasks. For communication with staff to ensure proper restrictions are met, I use various color code and color combinations.

    I set up a workbook (I think its called notebook in QP) First sheet is the master, which is where I enter everything for each resident, using color codes to signify like Low Salt or diabetic etc.

    The additional sheets, I would say have one set to give the person taking care of beverages which would contain only the contents from the master that would pertain to their given task of beverages. Another sheet for the cook so they would know any special diets.

    Since text is too easily missed, I want to add appropriate colors, like green to indicate diabetic to cells. Occasionally we may someone with 2 restrictions which would require 2 color codes and this were the colored text combined with colored cell comes to play.

    Any assistance to point me directly to where I need to go or even a quick sample of this sort of function would be greatly appreciated.

    Thank you again...
  • In reply to Nelson:

    I think that the {BlockValues} macro command is easiest to use here. {BlockValues} copies cell content and cell properties from one block of cells to a target. (See Chapter 16 of the book for more discussion.)

    Here's how I set up a quick version that you can modify to suit your needs.

    The first sheet is named Master.
    Residents are listed in column A. I used cells 1-10.
    Food requirements are listed in column B, parallel with the Residents.
    Drink requirements are listed in column C, again parallel with the Residents.
    I applied various color attributes to six randomly selected cells in B1..C10, and put words in the cells that were colored.

    The second sheet is namedFood. It is currently blank, but column A will list the Residents, and column B their food requirements.

    The third sheet is named Drink. It is currently blank, but column A will list the Residents, and column B their drink requirements.

    The fourth sheet is called Macro, where the macro commands will be stored. There, in cells A1..A4, I put these commands:
    {Blockvalues Master:A1..A10,Food:A1}
    {Blockvalues Master:A1..A10,Drink:A1}
    {Blockvalues Master:B1..B10,Food:B1}
    {Blockvalues Master:C1..C10,Drink:B1}

    I then ran the macro by pressing Alt+F2 and selecting cell Macro A1. The Food and Drink sheets were then filled in exactly as intended.

    I changed the colors and contents of cells in Master:B1..C10. Then I ran the macro again, using the same steps. The Food and Drink sheets were modified to match the changes made on the master sheet.

    To make this easier, I then added a command button on the Master sheet to run the macro with a single click. Right-clicking that button and selecting "Properties," I gave it the name "Generate" on the "Label Text" tab, and on the "Macro" tab, I entered {Macro:A1}. Clicking this button runs the macro that starts at Macro:A1.

    I then changed the color and content of cells in Master:B1..C10 again, and then clicked the new Generate button on the Master sheet. It made the changes on the Food and Drink sheets, as expected.

    That should get you started, but you'll probably want to modify this in various ways.

  • In reply to Charles Cork:

    Thank you so much for this and your time. Can't express how appreciative I am! It is my goal to have the first version of this workbook up and functioning next week. Then I can refine from there as needed. I will let you know how it goes once I get the reread your posting and review Chapter16 as you suggest.

  • In reply to Charles Cork:

    Hi Charlie

    Once again thank you. Just set up your text example and it will be perfect:-) Now thinking ahead to a possible conclusion to this project which would make it a complete system, is it possible to retrieve a cell value and use it in for the sheet name part in the Macro? That sounds confusing...

    This is a small nursing home, less then 40 residents and due to nature of the business, there should also be some error checking or cross checks. This error checkpoint would be in the form of a single card for each resident. I would add/remove sheets as residents come and go and these sheet names would be the resident's name as it would appear in A column of the master. So when I add a new resident it would be a 2 step, first step on the master with all their information and second step I would create a new sheet for that resident. Using the examples you have shown I would fill out each card using the Macro.

    I realize this would be an extensive Macro and may take a few moments to run.
    {Blockvalues Master:A1,VALUE_FROM_A1:A1}
    {Blockvalues Master:A2,VALUE_FROM_A2:A1}
    and so on....

    This would add upward to 40 more calls in the macro. Hope I am not asking too much...

  • In reply to Nelson:

    I think that what you're contemplating can be done, at least if the entries in column A could be valid sheetnames, but there may be better ways to do it. As residents come and go, the QP notebook could get difficult to manage if there is a sheet in the same QP notebook for every resident who has every been there. Do you want or need to keep each card on each resident in that single QP file?

    One option is to generate a separate QP notebook to serve as the card for the resident. This would be a good option if you want to keep a record of the QP data for that resident, but don't need to keep it for prior residents in your general/master QP notebook. And if you need to keep each card as revisions are made, you could have more than one QP notebook for each resident.

    And if you don't need all that, just a printable card, the program could be set up to send the data on a resident to one sheet of the QP notebook, and then that sheet could be printed. And that program could be extended to generate and print cards on each resident, which would come in handy if you need to print the cards for all residents on a regular basis.

    If any of these options suits your needs, we can go to the next step.

  • In reply to Charles Cork:

    Yes, column A could easily be a valid sheet name. The initial part of this which you have discussed with me would contain at most 6 additional sheets. What I was thinking is, as residents leave, I would simply delete their info and their separate sheet, as new residents come in, I would add their name/info and add a sheet with their name (these name sheets being the printed cards) so the notebook would never exceed say 50 sheets.

    The initial sheets would be printed anytime there was an update to any resident. Card printed would be more permanent, in fact I would laminate the cards. Some residents could be with us for years without diet change. I would just need to make note of any changes when I do them and then go to that resident's card to print a new one and laminate to replace the current.

    I do think it would be a good idea to keep the digital version of each card, so should the director of nursing want to see a quick reference to any given resident it might be easier to bring that card up on screen rather then looking through the physical laminated cards for the one resident. The card would be a consolidated summery which would be easier to look at then to go through the Master sheet.

    One little catch I didn't mention, and don't know if easily worked in, the only properties I would want to carry to the cards would cell & border colors and font colors since the cards would need various bolder and larger text then the master.

    As I am writing this, would it be possible to put a "generate card" button in each row and then follow your third method of sending data for a single resident to a card for review by Nurse and/or print? This could be a lighter weight option, since I don't actually need to keep the completed cards as long as I can generate a digital version and time it is needed.

    Your feedback and ideas have been very valuable and much appreciated.
  • In reply to Nelson:

    I'm on a trip now and didn't see the request until now. A few follow-up clarifications, or requests for confirmation:

    1. The Master QP should have the current cards on each current resident, with color-coded properties.

    2. A separate digital card would be created for permanent storage. Simplest would be to have it as a separate QP file, but having it as a PDF may be more forward-thinking. Which format would you prefer? Also, note that you would either need to manually name the file, or if you automate the file-naming process using QP's macros, ensure that each resident's permanent file would have a unique name, so that it doesn't overwrite an earlier file.

    3. The last request about sending a card for review sounds like the same thing as re-generating the same card as in point #2. Is that right? If so, it would be simplest to set this up so that it simply overwrites the first separate QP file. If you don't need to preserve an electronic record of each card generated on the same resident, but need only maintain the current one, this would be easy to do.

    4. Instead of a button on each row, the macro can be set up to recognize what row the cursor is on. So, you would click cell A10, for example, and the macro could recognize that you want to print the current card on that resident. Or update the information on that master sheet. Or navigate to the master sheet on that resident. Key to this will be knowing what sort of data you have in column A (which I assume is your "index" column).

  • In reply to Charles Cork:

    Hi Charlie

    You have gone above and beyond expectations, so no worries at all about your delay. Enjoy your vacation and we can get back to this when you return. So please do not feel obligated, although I am ever so grateful for your help.

    #1: The master sheet QP sheet contains all the needed information for all of the "cheat sheets" for the staff, (one for drinks, one for say cereal, one for details the cook needs among others and the resident cards to be laminated). None of the cheat sheets or cards will contain everything in the Master Sheet. In other words the cards would not contain details such as drink preferences or other little details like this. The card would contain the details such as allergies, or low salt or food textures etc. Your initial approach with the Blockvalues is perfect for all the "cheat sheets".

    #2: I do not need any sort of permanent records, only current active records.

    #3: Yes, regenerating the current card for a quick reference or reprinting a particular card due to diet change or other reason is correct. My concern here is, can we selectively choose what cell properties to bring into the card and have other properties pre-defined in the card template/sheet. Example might be, in the card, I would want the name font pt size to be maybe a 24point bold, but I would not want the cheat sheets names in 24 point size. So the cards should only carry over the text, text color and cell colors and boarders, but not font or font size or even text orientation because these properties would only be for the cards.

    #4: Since I do keep first and last names in separate columns, it might be to my advantage to create a third name column to join the first and last to keep unique for purpose of a key index. Since the probability of having 2 "johns" or 2 "smiths" would be much higher then the chances of having 2 "john smiths". But, with only 35 residents, running into this type of scenario could be easily dealt with on a case by case basis.

  • In reply to Nelson:

    I've set up a system that does this, at least if I understand it correctly. Unfortunately, I don't see how to attach anything that is not an image or a video here; otherwise, the QPW file would be attached. Instead, I've uploaded it to the QP page on my web site:

    The master sheet's first row is reserved for 3 command buttons, and the second row is for headers. Column A is an index column filled in A3..A37 with "Res01", "Res02", ... "Res35". Column B is "Name," C is "Food", D is "Drink", and E to I is "Info1" to "Info5". I filled in "Names" with a random name generator, manually made a few entries in Food and Drink, and then filled Info1 to Info5 with calendar months. You can change those names and, as the macros are written, the order in which they appear.

    After the Master sheet is a sheet to store the Macros.

    Then follow the "Food" and "Drink" sheets that were filled with the BlockValues macro. That macro is currently at Macro:A2. More "Cheat Sheets" can be inserted around here, as long as they have unique names.

    Turning to the Resident Cards, I then created a sheet for each Resident called "Res01," "Res02" etc. A macro at Macro:H2 automated that task.

    I then entered the headers from the master sheet into cells Res01:A1..A8. I then copied Res01:A1..A8, selected Res01:A1..Res35:A1, and pasted, which pasted the same headers into all the other Resident cards.

    I then created a macro at Macro:C2 that allows the user to select a resident number on the master sheet, and the macro takes the user directly to the correct Resident Card, which can be printed manually. A button was placed over column A to run that macro. It uses a subroutine to make sure that the user has selected a Resident in column A, and if not, gives the user a chance to do so. That Verification subroutine is at Macro:H10.

    Finally, I created a macro as Macro:C10 that allows the user to select a resident number (as with the navigation macro, and with the same verification subroutine), and the macro then writes or updates the Resident Card with the data currently on the master sheet, as well as the cell color, the cell font color, and the borders and border colors. It leaves other properties on the Resident's card unchanged. A button on the master sheet starts this process. This macro deserves some explanation.
    * It loops down the list of header categories in cells A1..A8, until it reaches a blank, returns to the top, and stops (Macro:C12 and C21).
    * It locates the address of the cell on the master sheet that relates to the current resident and the current header category (Macro:C13..C16).
    * It then copies the "Value", "Text_Color", "Fill/Pattern.Fill_Color", and "Border" properties from that cell to the cell 1 column to the right of the header categories (i.e., B1..B8) on the Resident's card (Macro:C17..C20).

    That should get you started. There is an index in my QP book that can lead you to discussions of most of these commands and functions.

  • In reply to Charles Cork:

    This is absolutely perfect!!! You got all of the needed mechanics covered. I will be sending you a personal thank you note to your e-mail on your site with a token of gratitude.

  • In reply to Charles Cork:

    I have been playing around with this and perhaps I am rushing some, but there is one piece of the puzzle that is not jumping out at me on how to modify. I can see and can follow the offset part and move the group any place on the resident card I choose, but I need to move the individual values, not as a group. Picture a info card, values wold not be listed in a single column. Like, name perhaps at the top and maybe repeated at the bottom and another piece of info to the right of the name and more of the stuff arranged in the center. I am trying to see how to break this down so I can arrange the resident data on the card?

    One option I am thinking although not very clean would be to keep the record info in the left column and then just set the various other cells that would make up the card to just duplicate. If this is a route then I will need to read into how to pull the color properties from the left column to the target.

  • In reply to Nelson:

    I agree that the latter option would not be clean, in the sense that it duplicates the problem of transferring cell properties.

    The macro was initially set up to handle random information from the master sheet, but transferring from specific columns on the master to specific cells on the resident card would require restructuring the macro at C12..C21. It seems to me that this is the simplest way to do it:

    After the error checking and navigation in C10..C11, the first step would be to calculate the row on the master sheet with the command currently at C15. Then for each item to be put on the resident card, you'll do three things:

    1. Calculate the source cell on the master sheet for the desired item of data. You'll already know the column offset on the master for each item of data, so you don't need to calculate it with C13..C14. You got the row in the first step, so a variation of the command in C16 calculates the source cell.

    2. Navigate to the cell on the resident card where the item of data is to be placed. A variation of the command at C11 should work.

    3. Run a variation on the four commands currently at C17..C20. The command would be {setproperty}, not {setobjectproperty}, and it would delete "[]c(1)r(0)." from the first argument of each command. The effect would be to operate on the current cell, rather than the cell calculated by []c(1)r(0).

    That makes six commands for each item of data, so if you have 10 items of data on each card, you would have 60 lines of code. There are ways to make this more elegant (e.g., make a table pairing the offset on the master sheet with a cell on the resident sheet, and loop through the table), but this method should get the job done. Let me know if it is unclear, or if you experience difficulties.

  • In reply to Charles Cork:

    Thank you and i certainly don't want to wear out my welcome. i am sure with some focused time this will come together. If I wouldn't be asking too much, I think a single working example would be worth a 1000 words. I have only looked over this and tried to make sense over my morning coffee and it is possible my mind is creating mountains out of mole hills:-)

    Thank you