Copy a formula using Vlookup

  • Thread starter Thread starter ansar ak
  • Start date Start date
A

ansar ak

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
 
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
Click to expand...

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 said:
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
Click to expand...

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.
 
Back
Top