Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto Update a list
Hi,
I have read a post on here with the same subject from December '06 but what I'm after is a bit different and I don't want to populate all cells in the new row. I have an ODBC connection set up which pulls information from an application to Access. I then have a worksheet ('AccessData') that gets information from the database with the following column headers (if it's relevant to you) Task_No, Type_Desc, Name, Title, Opened, Resp_Eng, Quoted_Price, State, Completed. This information is then pulled across to another worksheet ('Formulas') using this formula: =INDEX(AccessData!$A$1:$I$406, MATCH(IncomeData!$A2,AccessData!$A$1:$A$406,), MATCH(AccessData!$B$1,AccessData!$A$1:$I$1,)) Obviously, the $A2 and $B$1 change dependant on the number and data I want to match in each cell. This information is then taken across to a worksheet (IncomeData) using this kind of formula: =IF(ISNA(Formulas!C2),"",Formulas!C2) The information on this worksheet is a list and presently eight cells of the thirteen cells along each row populate using this information. Essentially, when I enter a task number in column A of 'IncomeData', the rest of the row fills itself in with the data applicable to the task number in 'AccesssData'. The reason I have done it this way with the ISNA statements is because this in turn becomes a pivot chart and they don't like #N/A! Hopefully, you've not lost the will to live at this point and you've understood everything I've said! So, to my request for help. When I right click a populated cell in 'AccessData' I can refresh the data which gets new information, 'Formulas' updates as the formulas are copied way down the sheet. To get 'IncomeData' to update, I have to type in the new task number, now, I know it sounds lazy but..... I want 'IncomeData' to add a new row(s) when 'AccessData' refreshes. Even lazier, I want the 'AccessData' worksheet to refresh when the workbook is opened and vaguely recall some code about 'action on open'. Thanks for taking the time to read this behemoth of a post and all help gratefully received. Oh, BTW I'm running Excel 2003 and XP. -- Cheers, Spidersinthekitchen |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto update a list and don't show blanks | Excel Worksheet Functions | |||
ATTN : Ron (Multiple Dependent DV List Auto Update) | Excel Worksheet Functions | |||
Auto Update validation list? | Excel Worksheet Functions | |||
Auto Update validated list wording? | Excel Worksheet Functions | |||
Auto Update A Validation List | Excel Worksheet Functions |