+ Reply to Thread
Results 1 to 8 of 8

Making a formula to automatically fill a label

  1. #1
    Registered User
    Join Date
    06-08-2009
    Location
    Helsinki, Finland
    MS-Off Ver
    Excel 2007
    Posts
    7

    Making a formula to automatically fill a label

    Hello,
    As I am a fresh user of excel 2007 I do not know how hard it is to solve the problem I am facing but hopefully there is a solution that the readers here are willing to share with me.
    I have a list of cd-s that I have recorded as a hobby with my friends (karaoke, free concerts...) and I have them coded in a specific way, so that I can find them on a shelve easily. The list is built up in a form that is seen on the example file that I added.
    So far I have used my own handwriting and a sticker to label my cd boxes. Itīs not a very good looking combo to be honest. Since I already have all the data in my excel tabel it would be good to use excel to take all the things from there and just print it out.
    How to do that? The front cover is easy, I used VLOOKUP function. All I have to do is enter a number into the column C1 and the front label gets filled.
    The problem for me is the back cover that should take the right track nr, title and length from several rows (VLOOKUP uses only one row at a time)? How to make it work, that excel would know that on the first album there are 3 tracks and when chaging a number in C1 there would be 2 tracks on a second one.

    How to solve that small problem I have. Thanks for the help
    Attached Files Attached Files
    Last edited by NBVC; 06-09-2009 at 10:35 AM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Making a formula to automatically fill a label

    Put this formula in C3.

    Please Login or Register  to view this content.
    then hold the CTRL and SHIFT keys down and hit ENTER.. You should see { } brackets appear around the formula.. then copy the formula down and across the rear cover cells to get the rest of the information...

    This formula is based on the Album number in E1 rather than the CD Nbr in C1.....
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    06-08-2009
    Location
    Helsinki, Finland
    MS-Off Ver
    Excel 2007
    Posts
    7

    Post Re: Making a formula to automatically fill a label

    Thank you for youre quick reply NBVC
    After struggeling with this formula a bit I got it working but I also found out that it is not working as I wanted it to work. Most likely I did not explain very well what I wanted to achive, or I screwed the formula up. My mistake and I try to explain what I wanted to achieve.
    My goal was to have a box (in my example E1) into which I could insert Album Nr. from column F. And as a result of a formula it should fill up the label on a second sheet. Lets say I insert 1 into E1 - the rear label should have three tracks (from cd 1) with all the data. If I insert 2 into E1, the label should have two tracks from the Second cd that are marked with 2 in the F column including the data. If I insert 3 into E1 the label should have only one track that is marked with 3 in the tabel. I have about 250 cds in a tabel like that and some of them have up to 25 tracks on them which should be presented on the rear cover.

    With the formula you gave me I had to do some tuning to get it working. Right now, if I insert 1 into E1 I get only the first three tracks from the First cd. If I insert 3 I only get one track from the First cd.
    I think I explained myself badly (or i messed up the formula you gave me) and hopefully you can understand what is my goal here.

    Thank you
    Last edited by Cretage; 06-08-2009 at 04:07 PM. Reason: Found a mistake in my post

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Making a formula to automatically fill a label

    Somehow part of my formula got cut off in my previous post...

    should be:

    Please Login or Register  to view this content.

    Confirmed with CSE key combination and copied down and across

  5. #5
    Valued Forum Contributor
    Join Date
    11-11-2008
    Location
    Euro
    MS-Off Ver
    2007, 2010
    Posts
    470

    Re: Making a formula to automatically fill a label

    Quote Originally Posted by NBVC View Post
    Somehow part of my formula got cut off in my previous post...

    should be:

    Please Login or Register  to view this content.

    Confirmed with CSE key combination and copied down and across
    Nice fomula, thanks you. I only just add more

    For D3 is:

    PHP Code: 
    =IF(ROWS($A$1:$A1)>COUNTIF('CD Tabel'!$F:$F,'CD Tabel'!$E$1),"",INDEX('CD Tabel'!H:H,SMALL(IF('CD Tabel'!$F:$F='CD Tabel'!$E$1,ROW('CD Tabel'!$F:$F)),ROWS($A$1:$A1)))) 
    of course, also with Ctrl+Shift+Enter
    .
    Last edited by tigertiger; 06-08-2009 at 04:39 PM.

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Making a formula to automatically fill a label

    Copying my formula over to next column will automatically index H:H

  7. #7
    Registered User
    Join Date
    06-08-2009
    Location
    Helsinki, Finland
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Making a formula to automatically fill a label

    Thank you. The formula works very well. I managed to make things as I wanted with it. Thanks again NBVC

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Making a formula to automatically fill a label

    You are welcome.

    Can you kindly mark your thread as Solved?

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

+ 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