Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    New Lounger
    Join Date
    Oct 2008
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Transpose information to other worksheets (Excel)

    I am looking for inspiration to short cut a cumbersome process with the Key Register used in my new workplace. I have attached an extract from it.

    Simply what happens is that a list of keys is maintained in separate worksheets for each management area. From there the information is manually transposed to the corresponding Alphabetical Worksheet and then sorted into alphabetical order. I see that this process is both time consuming and leaves lots of room for error in transposition.

    Is there a way to simply maintain the ins and outs and maintenance of the alphabetical list by just using the master lists?

    Any ideas or improvements would be most welcome.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Transpose information to other worksheets (Excel)

    Welcome to Woody's Lounge!

    With the way the data are organized, it's very difficult. I'd create a relational database, for example in Microsoft Access.
    You can use queries to display the sorted and grouped in different ways.

  3. #3
    New Lounger
    Join Date
    Oct 2008
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transpose information to other worksheets (Excel)

    Hello there

    I would like to use Access, but my workplace doesnt have it on their computers.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Transpose information to other worksheets (Excel)

    I'd keep all information in a single sheet - see the attached version.
    To sort the info by key number or by property or by department, click in the column header, then click the Sort Ascending button on the toolbar.
    To view data for a single department or for a letter of the alphabet, use the AutoFilter buttons in the column headers. I added a column that uses formulas to display the first letter of the property.
    The colors in the Dept column are set by conditional formatting, using a list of departments in the List sheet.
    Attached Files Attached Files

  5. #5
    New Lounger
    Join Date
    Oct 2008
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transpose information to other worksheets (Excel)

    Thankyou so much. That is a fantastic idea. It will be very simple to maintain.

    Much appreciated.
    B

  6. #6
    New Lounger
    Join Date
    Oct 2008
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transpose information to other worksheets (Excel)

    Hello Hans

    I have been wondering how you managed 4 conditions in the Conditional Formatting?

    Regards
    B

  7. #7
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transpose information to other worksheets (Excel)

    From what I can see in Hans attachment, there are only 3 conditional format's (blue, green & yellow). PM1 is not conditional, this is just set as the standard format. Red will appear for every input (i.e PM1, PM3, PM4), only PM2, COMM & Sales are conditional.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Transpose information to other worksheets (Excel)

    Nathan (VegasNath) is correct - I set red as default background color in the Pattern tab of Format | Cells, and set 3 conditions in Format | Conditional Formatting.

  9. #9
    New Lounger
    Join Date
    Oct 2008
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transpose information to other worksheets (Excel)

    Ahhh - very clever. Thankyou.

    I love this solution - it is just fantastic and so simple. A database would have been an overkill I think.

    Great forum!

  10. #10
    New Lounger
    Join Date
    Oct 2008
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transpose information to other worksheets (Excel)

    I have a further enhancement I would like to make to this spreadsheet. Can you help me with the type of function I should use?

    If "no key" and Property is not blank turn the Property text red
    If "key present" and Property is blank shade the property cell red

    I assume this would be another conditional format?

    Regards
    B
    Attached Files Attached Files

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Transpose information to other worksheets (Excel)

    Select B2:B9 or howver far down you want to apply the conditional formatting.
    B2 should be the active cell within the selection (you can see this in the address box on the left hand side of the formula bar.)
    Select Format | Conditional Formatting...
    Select Formula Is from the first dropdown.
    Enter the following formula in the box next to it:

    =AND($A2="No Key",NOT(ISBLANK($B2)))

    Click Format... and specify Red as text color in the Font tab, then click OK.
    Click Add >> to add a second condition.
    Select Formula Is from the first dropdown.
    Enter the following formula in the box next to it:

    =AND($A2="Key Present",ISBLANK($B2))

    Click Format... and specify Red as fill color in the Pattern tab, then click OK.
    Click OK to close the Conditional Formatting dialog.

    See attached version.
    Attached Files Attached Files

  12. #12
    New Lounger
    Join Date
    Oct 2008
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transpose information to other worksheets (Excel)

    Thankyou for the advise and including the technique. I was way off as I thought it would be an IF function.

    I have a lot to learn. Including no replying to myself????

    Regards
    B

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Transpose information to other worksheets (Excel)

    Don't hesitate to ask - that's what we're here for!

  14. #14
    New Lounger
    Join Date
    Oct 2008
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transpose information to other worksheets (Excel)

    Back again!

    I would like to extend the functionality of this a step further. When I filter in ascending order the "Letter" column I would like for the blank cells to be at the bottom and when I print the report each letter to be on a new page with about 10 blank rows at the end of each letter.

    Is this too far fetched?

    Regards
    B

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Transpose information to other worksheets (Excel)

    You could sort on the Property column instead of on the Letter column. Really empty cells will always be sorted at the bottom (the blank cells in the Letter column aren't empty, they contain formulas).

    I don't see an easy way to realize your printing request.

Page 1 of 2 12 LastLast

Posting Permissions

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