Results 1 to 5 of 5

VLookup...is it possible to create dynamic table arrays?

This is a discussion on VLookup...is it possible to create dynamic table arrays? within the Excel Questions forums, part of the Question Forums category; I’m currently updating a spreadsheet template used in preparing monthly forecast information and I am trying to make the spreadsheet ...

  1. #1
    New Member
    Join Date
    Aug 2002
    Location
    Hong Kong
    Posts
    12

    Default

    I’m currently updating a spreadsheet template used in preparing monthly forecast information and I am trying to make the spreadsheet more “dynamic” to save time.

    The source data comes in the format of a three column spreadsheet with Month, JobNo and InvoiceAmt. This data is a summary therefore for each job there can only be one Invoice Amount in any particular month.

    I have prepared an output file template consisting of a full Job list and reference the Invoice Amounts using the following VLOOKUP formula picked up from an earlier topic thanks to Aladin Akyurek.

    =IF(ISNUMBER(MATCH(A1,INDEX(LTable,0,1),0)),VLOOKUP(A1,LTable,2,0),0)

    where A1 is a value to lookup in LTable.

    As I need to prepare a summary for each month, I have set up separate table arrays for each month but have to update these manually whenever the source data changes (i.e. new projects are added, etc.)

    Does anyone have any ideas on how I can automate this process to avoid all the manual updating?

    Any help would be appreciated

    Thanks in Advance

    CT


    [ This Message was edited by: CrouchingTiger on 2002-08-08 21:53 ]

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    230

    Default

    You might try using Mr. Excel's infamous CSE formulas also know as array formulas. For example if the data is a s follows

    Month Inv No. Amount
    Jan 1245 25.07
    Jan 1246 33.02
    Feb 1245 17.68
    Feb 1247 22.05
    Mar 1245 36.77


    Assuming Month in in Cell A1 the following formulas will return the total for Feb "=sum((a1:a6="Feb")*(c1:c6))". The formula "=sum((b1:b6="1245")*(c1:c6))" will return the total for invoice 1245. Please note to enter the formula instead of using (Enter) use (Ctrl)(Shift)(Enter). YOu can refer to Mr. Excel's article for a more complete discussion of these formulas. You will discover they are very powerful and be as dynamic as you need. One caveat, they are more computationally intensive than normal formulas so if you use many of them they can slow down your spreadsheet considerably. If you would like a more complex example, I could provide one for you. Just e-mail me at marlscpa@juno.com.

  3. #3
    New Member
    Join Date
    Aug 2002
    Location
    Hong Kong
    Posts
    12

    Default

    Thanks for the reply 2077delta...never new about these CSE formulas but I look forward to playing with them in the future and may take you up on the offer of a more complex example.

    What I really want to do with my spreadsheet is to create a dynamic table array that allows me to copy and paste the records for a particular month to another spreadsheet without having to manually copy them over which is what I do at the moment, however my macros / vba skills are appalling at best

  4. #4
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,253

    Default

    Tiger,

    I'm not sure if this is what you're looking for. It's how to create a dynamic range

    http://www.mrexcel.com/board/viewtop...c=7199&forum=2

  5. #5
    New Member
    Join Date
    Aug 2002
    Location
    Hong Kong
    Posts
    12

    Default

    Thanks Brian....that was sort of what I was thinking of.

    I have found the answer to my problem on the following link thanks again to Aladin Akyurek

    http://www.mrexcel.com/board/viewtop...6207&forum=2&7

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com