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
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