Home 
Search 
Today's Posts 
#1




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




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




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




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 