Insert Rows Based on values in another worksheet

[Jason]

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

 

[Joel]

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]

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]

You are right. But you can aways make the number of rows 65536.