S
spidersinthekitchen
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.
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.