Dear all,

I have a sheet with some formulas, i need the same formulas in the new
sheet.

Eg: -
A1 A2
A3 A4 A5 A6
Code Customer Name Oct 11 Sep 11 Aug 11 Total
CAA001 ABUDHABI HOSPITALITY CO. 11,456 8,422 28,063 =sum(A3:A5)

can i able to copy only formula (A6) with reference to the colomn A1
using Vlookup or any other formula to the new sheet

Ansar

On Dec 11, 7:05*am, ansar ak wrote:
Dear all,

I have a sheet with some formulas, i need the same formulas in the new
sheet.

Eg: -
A1 * * * * * * * * * A2
A3 * * * * * * A4 * * * * * * * A5 * * * *A6
Code * *Customer Name * * * * * * * * Oct 11 * *Sep 11 *Aug 11 *Total
CAA001 *ABUDHABI HOSPITALITY CO. 11,456 * * * * *8,422 * 28,063 * * * * =sum(A3:A5)

can i able to copy only formula (A6) with reference to the colomn A1
using Vlookup or any other formula to the new sheet

Ansar

VLOOKUP() returns a value. What you need is a combination of MATCH()
and OFFSET() to get the address of the cell. Then use a tiny bit of
VBA to perform the copy/paste.

Are you O.K. with the VBA approach??

"ansar ak" wrote:
Subject: Copy a formula using Vlookup
I have a sheet with some formulas, i need the same
formulas in the new sheet.
[....]
can i able to copy only formula (A6) with reference
to the colomn A1 using Vlookup or any other formula
to the new sheet

Your question is unclear. We do not use VLOOKUP to copy formulas; instead,
we use it to look up values.

If you truly wanted to copy formulas from one sheet to another, you would
use either copy-and-paste or the Copy Sheet feature (right-click on the
sheet tab).

But I suspect you want to look up codes in the 1st column (A) and return the
corresponding sum from the 6th column (F). Thus, if your table of data is
in Sheet2, you might write:

=VLOOKUP(C2,Sheet2!$A:$F,6,FALSE)

to look up a code in column C in Sheet1 and return the corresponding sum.

It would be better if the table in Sheet2 were sorted in ascending order
according to the codes in column 1. In that case, you might write:

=VLOOKUP(C2,Sheet2!$A$2:$F$1000,6)

assuming that the Sheet2 table is in rows 2 through 1000.

Note.... In your example, you labeled the columns of data A1, A2, A3, etc.
Since you presented them as columns of data, I assume you meant to label
them as A2, B2, C2 etc, allowing for headings in row 1. But if your table
is truly in rows 1 through 6 of columns A, B, C etc, you would use HLOOKUP
instead of VLOOKUP. For example:

=HLOOKUP(C2,Sheet2!$1:$6,6,FALSE)

or

=HLOOKUP(C2,Sheet2!$A$1:$Z$6,6)

Again, the second form assumes that the data is sorted in ascending order
according to the codes in row 1.

If I misinterpreted your requirements, forgive me for any misdirection here.
In that case, I suggest that you provide a concrete example showing what
results you want and where by cell name.