Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 23rd 07, 05:00 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 367
Default 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   Report Post  
Old June 23rd 07, 06:13 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 9,101
Default 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   Report Post  
Old June 24th 07, 02:04 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 367
Default 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   Report Post  
Old June 24th 07, 01:13 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 9,101
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert rows based on specific value bob Excel Worksheet Functions 6 February 29th 08 07:11 PM
Insert new rows based on Data in other rows mg_sv_r Excel Worksheet Functions 5 November 21st 07 01:51 PM
Hiding Specific Rows Based on Values in Other Rows Chris Excel Worksheet Functions 1 November 2nd 06 08:21 PM
How do I insert blank rows between rows in completed worksheet? bblue1978 Excel Discussion (Misc queries) 1 October 26th 06 07:02 PM
Insert New Rows based on COUNTA() TheDPQ New Users to Excel 2 January 28th 06 03:07 AM


All times are GMT +1. The time now is 01:01 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017