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

CrouchingTiger

New Member
Joined
Aug 7, 2002
Messages
12
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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,148
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top