Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Populate a range from a single cell (Excel 2002)

    Hi

    Would like to populate a range of cells based on a selection of 1 number as attached workbook.

    Preferably with a formula rather than VBA, because this is only a sample of the data I need to produce.

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Populate a range from a single cell (Excel 2002)

    I do not think it is possible with builtin functions. Check out Re: Lookup more than one row (2000) for several UDFs I created. Copy the one named VLINDEX into a module

    Enter in C9:
    <pre>=vlindex($B$14,$A$1:$I$4,COLUMN()-2,ROW()-8)</pre>


    Copy C9 to C9:J12. Adjust ranges as needed. The "2" is because the formula is in the 3rd column and the lookup is offset only 1. The 9 is because you want the first item and the row is 9.

    Steve

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Populate a range from a single cell (Excel 2002)

    Hi Steve

    I now believe in magic. Sincere Thanks.

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Populate a range from a single cell (Excel 2002)

    After further thought on this on my way to work, I want to clarify my answer.

    I assumed that the items you are looking up in Col A will not be grouped together in the range of all the data: ie they could be anywhere. If that is not true and they will be grouped together, it could be done with match/Index combinations. Match could determine the top cell in the range, then you could increment the others to get their "Row index". Index could be used with this to get the items.

    The exact formula will depend on how it is setup, where intermediate calcs could go and if the number of items to extract will be constant or variable, and also the max number of items you will "extract".

    Hope this helps,
    Steve

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Populate a range from a single cell (Excel 2002)

    Hi Steve

    I have attached the actual sheet to explain what I am trying to do.

    However I have stuck a problem trying to extract other blocks of data, I would appreciate if you could take a look for me.

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: Populate a range from a single cell (Excel 2002)

    The row and column indexes are relative to the specified range. For example, the formula in C22 must refer to the first row of A12:I20, so you must use ROW()-21:

    =vlindex($B$22,$A$12:$I$20,COLUMN()-2,ROW()-21)

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Populate a range from a single cell (Excel 2002)

    Hi Hans

    Thanks for your prompt reply.

    Braddy
    If you are a fool at forty, you will always be a fool

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Populate a range from a single cell (Excel 2002)

    <P ID="edit" class=small>(Edited by sdckapr on 26-Aug-05 09:31. With attachment)</P>I am afraid I don't understand what you are doing. If your spreadsheet is setup like this I don't understand why you are duplicating one section immediately under itself.

    Is this your setup?

    I assumed you had a large table, and then you were trying to extract items from it. The way you have it listed does not require complicated formulas

    I pictured you wanted to do something like extract to a different sheet or different region. Also if you items are grouped. You could do it like the attached.and extract in a different sheet. (Note row 1 and column B may be hidden if desired). This does not need the function at all...

    Steve

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Populate a range from a single cell (Excel 2002)

    Hi Steve

    I will try to explain.

    Code 6620465 is premix chemical code. we are compelled by law to say what that chemical consists of. In this instance it is made up of,

    6220418
    6620114
    6620462
    6621400 the other columns contain the name of the chemical it's percentage of 6620465 etc

    This data is then transferred to another worksheet, so when a user enters the code 6620465 then its has to look the data from the small matrix that your code produces.
    Which for me will present another problem when I try to look it up from another worksheet.

    I hope this expains what I am trying to do, even if I am not going the correct about.

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Populate a range from a single cell (Excel 2002)

    I apologize, but based on what you wrote, I am still picturing it like my last attachment so I am unclear how to modify it.

    The extract sheet in the attachment (and the calcs) can be in another workbook, BUT:

    the validation list would have to be moved to the other workbook, (I would put in extract sheet)
    Both workbooks would have to be open (Countif does not work with closed workbooks) though this could be modified to not use COUNTIF

    Could you attach an example of the source you want to read from and the output where you want it to go.

    It sounds like you want some kind of cascading formulation database and I am not sure how the setup is exactly.

    [Personally I think this type of thing can be handled better in access since it needs more a "relational database" type of structure which is more "native" to access tables than it is to excel. But then I admit, that I don't fully understand what you are after or your "setup"...]

    Steve

  11. #11
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Populate a range from a single cell (Excel 2002)

    Hi steve

    You do not need to apologise, sometime I realise I am not clear in what I am asking.

    I think in this case I have complicated things more than I need to,for instance.

    If i transpose the data from columns to rows it should only be a norman vlookup.

    ie 6620456, Col#, Col# say upto Col5, 6620418, Col#,Col#,etc,6620432,Col#.Col# and so on.

    Then I should be able to do a standard vlookup from 6620456.

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Populate a range from a single cell (Excel 2002)

    I still don't understand (but if your question is "answered" that is great)

    If transposing can use a standard vlookup, then you should be able to not transpose and use an HLOOKUP (though I don't see how transposing or an HLOOKUP will help - tough I may not understand <img src=/S/smile.gif border=0 alt=smile width=15 height=15>).

    Perhaps, I am just being obtuse...

    As I mentioned, if this question is answered, great, if not you will have to provide more details. I admit I don't understand it, and since no one else is "chiming in" with other answers, it may be a fair statement that others do not understand.

    Steve

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

    Re: Populate a range from a single cell (Excel 2002)

    > it may be a fair statement that others do not understand

    <img src=/S/yep.gif border=0 alt=yep width=15 height=15>

  14. #14
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Populate a range from a single cell (Excel 2002)

    Hi Steve

    It's not that you don't understand, It's because I don't express myself properly, This is Workbook is inherited from someone who is no longer with us. and I am finding it difficult to undersand.

    There is an abundance of stuff in there that I do not understand, so I will probably asking for assistance understanding some of the formulas.

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Populate a range from a single cell (Excel 2002)

    <hr>It's not that you don't understand, It's because I don't express myself properly<hr>

    It still comes down to the phrase from Cool Hand Luke: "What we have is a failure to communicate"

    I am pointing no blame at all to your explanations or my "obtuseness" (a little on either side, will lead to communication problems).

    If you are working with another's workbook, you always have the problem of understanding why they did what they did and how to modify it. Depending on how old it is and well it works (or doesn't work), it may be a good time to start with a cleaner slate and redesign to see what have and what you want. There are multiple ways to do things (in excel or other programs) and sometimes redesigning the process is warranted.

    The key (for me at least) is understanding what data I am going to have to "extract info" from and what info I am going to need. Knowing what info is required/wanted is essential to the design of the data storage.

    With this question, we seem to have a "disconnect" about what you have as well as what you want. I have an idea (which seems "wrong") of what you have and want. Anyone trying to answer must understand both items (what you have and what you want). I can not speak for anyone else, but I am not clear on either point. The people who post answers always make some assumptions on the things not explicitly stated (and for the most part, I think, we are pretty good at "guesses"). In this case it seems my assumptions are completely wrong so I am just <img src=/S/confused.gif border=0 alt=confused width=15 height=20> as to what you have and need.

    As always, if you continue to try to explain, I will make an effort to try and understand...

    Steve

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
  •