Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert Rows Based on values in another worksheet
Hi all
I am extracting a list of account numbers on one sheet (Extract), and need to copy the extracted values to another sheet (P&L). The sheet the values are being copied to, has formulas, for which I need to insert rows (The number of rows are determined by the number of accounts that have been extracted). So I will need to insert these rows and have the formula in the existing row (There will always be one existing row) Lets say the sheet starts like this: ACTUAL | BUDGET | DESCRIPTION ---------------------------------------------------------- (formula) (Formula) (Blank Value EXISTING ROW) -------------------------- $000.00 $000.00 It will need to end up like this: Please note the formula extracts from an external finance database and points to the "Description" column for the account number ACTUAL | BUDGET | DESCRIPTION ---------------------------------------------------------- (formula) (Formula) 34500 - Operating Supplies (formula) (Formula) 33300 - Advertising (formula) (Formula) 34455 - Electricity (formula) (Formula) 34451 - Gas (formula) (Formula) 32200 - Entertainment (formula) (Formula) 31500 - Travel Cost -------------------------- $000.00 $000.00 The number of rows on the (Extract) will increase each month Any ideas Cheers Jason |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert Rows Based on values in another worksheet
I think you just need to use th lookup function.
In row 3 column a on extract =lookup(c3,'P&L'!c1:c100,'P&L'!a1:a100) In row 3 column b on extract =lookup(c3,'P&L'!c1:c100,'P&L'!b1:b100) "Jason" wrote: Hi all I am extracting a list of account numbers on one sheet (Extract), and need to copy the extracted values to another sheet (P&L). The sheet the values are being copied to, has formulas, for which I need to insert rows (The number of rows are determined by the number of accounts that have been extracted). So I will need to insert these rows and have the formula in the existing row (There will always be one existing row) Lets say the sheet starts like this: ACTUAL | BUDGET | DESCRIPTION ---------------------------------------------------------- (formula) (Formula) (Blank Value EXISTING ROW) -------------------------- $000.00 $000.00 It will need to end up like this: Please note the formula extracts from an external finance database and points to the "Description" column for the account number ACTUAL | BUDGET | DESCRIPTION ---------------------------------------------------------- (formula) (Formula) 34500 - Operating Supplies (formula) (Formula) 33300 - Advertising (formula) (Formula) 34455 - Electricity (formula) (Formula) 34451 - Gas (formula) (Formula) 32200 - Entertainment (formula) (Formula) 31500 - Travel Cost -------------------------- $000.00 $000.00 The number of rows on the (Extract) will increase each month Any ideas Cheers Jason |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert Rows Based on values in another worksheet
Hi Joel
Thank you for replying. Unfortunately it is not as simple as that. You see column "C" needs to pull the values from the extract page, but first the number of rows on the P&L need to be inserted based on the number of accounts that have been extracted. For example, if 30 account codes have been extracted on the extract page, then 29 rows need to be inserted on the P&L page, then the account codes from the extract page need to be copied to the newly inserted rows on the P&L page. The P&L will always start with one row, and needs to grow. The formula in column A & B, is a formula that extracts values from an external database - the formula is not an excel formula. Jason "Joel" wrote: I think you just need to use th lookup function. In row 3 column a on extract =lookup(c3,'P&L'!c1:c100,'P&L'!a1:a100) In row 3 column b on extract =lookup(c3,'P&L'!c1:c100,'P&L'!b1:b100) "Jason" wrote: Hi all I am extracting a list of account numbers on one sheet (Extract), and need to copy the extracted values to another sheet (P&L). The sheet the values are being copied to, has formulas, for which I need to insert rows (The number of rows are determined by the number of accounts that have been extracted). So I will need to insert these rows and have the formula in the existing row (There will always be one existing row) Lets say the sheet starts like this: ACTUAL | BUDGET | DESCRIPTION ---------------------------------------------------------- (formula) (Formula) (Blank Value EXISTING ROW) -------------------------- $000.00 $000.00 It will need to end up like this: Please note the formula extracts from an external finance database and points to the "Description" column for the account number ACTUAL | BUDGET | DESCRIPTION ---------------------------------------------------------- (formula) (Formula) 34500 - Operating Supplies (formula) (Formula) 33300 - Advertising (formula) (Formula) 34455 - Electricity (formula) (Formula) 34451 - Gas (formula) (Formula) 32200 - Entertainment (formula) (Formula) 31500 - Travel Cost -------------------------- $000.00 $000.00 The number of rows on the (Extract) will increase each month Any ideas Cheers Jason |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert Rows Based on values in another worksheet
You are right. But you can aways make the number of rows 65536.
"Jason" wrote: Hi Joel Thank you for replying. Unfortunately it is not as simple as that. You see column "C" needs to pull the values from the extract page, but first the number of rows on the P&L need to be inserted based on the number of accounts that have been extracted. For example, if 30 account codes have been extracted on the extract page, then 29 rows need to be inserted on the P&L page, then the account codes from the extract page need to be copied to the newly inserted rows on the P&L page. The P&L will always start with one row, and needs to grow. The formula in column A & B, is a formula that extracts values from an external database - the formula is not an excel formula. Jason "Joel" wrote: I think you just need to use th lookup function. In row 3 column a on extract =lookup(c3,'P&L'!c1:c100,'P&L'!a1:a100) In row 3 column b on extract =lookup(c3,'P&L'!c1:c100,'P&L'!b1:b100) "Jason" wrote: Hi all I am extracting a list of account numbers on one sheet (Extract), and need to copy the extracted values to another sheet (P&L). The sheet the values are being copied to, has formulas, for which I need to insert rows (The number of rows are determined by the number of accounts that have been extracted). So I will need to insert these rows and have the formula in the existing row (There will always be one existing row) Lets say the sheet starts like this: ACTUAL | BUDGET | DESCRIPTION ---------------------------------------------------------- (formula) (Formula) (Blank Value EXISTING ROW) -------------------------- $000.00 $000.00 It will need to end up like this: Please note the formula extracts from an external finance database and points to the "Description" column for the account number ACTUAL | BUDGET | DESCRIPTION ---------------------------------------------------------- (formula) (Formula) 34500 - Operating Supplies (formula) (Formula) 33300 - Advertising (formula) (Formula) 34455 - Electricity (formula) (Formula) 34451 - Gas (formula) (Formula) 32200 - Entertainment (formula) (Formula) 31500 - Travel Cost -------------------------- $000.00 $000.00 The number of rows on the (Extract) will increase each month Any ideas Cheers Jason |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert rows based on specific value | Excel Worksheet Functions | |||
Insert new rows based on Data in other rows | Excel Worksheet Functions | |||
Hiding Specific Rows Based on Values in Other Rows | Excel Worksheet Functions | |||
How do I insert blank rows between rows in completed worksheet? | Excel Discussion (Misc queries) | |||
Insert New Rows based on COUNTA() | New Users to Excel |