A macro for pasting this formula

G

Gunjani

I have this formula in Cell C1 "='SHEET1'!C8*'SHEET2'!S5"
In C2 I want the formula "='SHEET1'!C9*'SHEET2'!S5"
In C3 I want "='SHEET1'!C10*'SHEET2'!S5"
In C4 I want "='SHEET1'!C11*'SHEET2'!S5"...... to cell C20

However in D1 I want "='SHEET1'!D8*'SHEET2'!T5"
In D2 I want "='SHEET1'!D9*'SHEET2'!T5"
In D3 I want "='SHEET1'!D10*'SHEET2'!T5"
In D4 I want "='SHEET1'!D11*'SHEET2'!T5"..... to cell D20

And in E1 I want "='SHEET1'!E8*'SHEET2'!U5"
In E2 I want"='SHEET1'!E9*'SHEET2'!U5"
In E3 I want "='SHEET1'!E10*'SHEET2'!U5"..... etc

All the way thru to column BB.
Is there an easier way to copy/paste this in ONE HIT currently I am
using in C1 "='SHEET1'!C8*'SHEET2'!$S$5" then pasting down in column C,
then in D1 "='SHEET1'!D8*'SHEET2'!$S$6" then pasting down again etc etc
all the way thru to column BB

--
Many Thanks

Gunjani
Insanity is hereditary - you get it from your kids.
-- Bumper Sticker
 
K

kkknie

No need for a macro. Just paste this:

=Sheet1!C8*Sheet2!S$5

Into C1 and copy it to the rest of your range. The S$5 keeps the ro
number constant through the pastes, while the lack of a $ in the othe
references lets them change based on where they are pasted.
 
B

Bernie Deitrick

Gunjani,

Use in C1

='SHEET1'!C8*'SHEET2'!S$5

(note the use of S instead of $S) then copy and paste to your entire range.

HTH,
Bernie
MS Excel MVP
 
G

Gunjani

kkknie said:
No need for a macro. Just paste this:

=Sheet1!C8*Sheet2!S$5

Into C1 and copy it to the rest of your range. The S$5 keeps the row
number constant through the pastes, while the lack of a $ in the other
references lets them change based on where they are pasted.

K
Click to expand...
Thanks but SORRY I made a major error in post it should read as follows
(note the last integar changing in each column):
I have this formula in Cell C1 "='SHEET1'!C8*'SHEET2'!S5"
In C2 I want the formula "='SHEET1'!C9*'SHEET2'!S5"
In C3 I want "='SHEET1'!C10*'SHEET2'!S5"
In C4 I want "='SHEET1'!C11*'SHEET2'!S5"...... to cell C20

However in D1 I want "='SHEET1'!D8*'SHEET2'!S6"
In D2 I want "='SHEET1'!D9*'SHEET2'!S6"
In D3 I want "='SHEET1'!D10*'SHEET2'!S6"
In D4 I want "='SHEET1'!D11*'SHEET2'!S6"..... to cell D20

And in E1 I want "='SHEET1'!E8*'SHEET2'!S7"
In E2 I want"='SHEET1'!E9*'SHEET2'!S7"
In E3 I want "='SHEET1'!E10*'SHEET2'!S7"..... etc

All the way thru to column BB.
Is there an easier way to copy/paste this in ONE HIT currently I am
using in C1 "='SHEET1'!C8*'SHEET2'!$S$5" then pasting down in column C,
then in D1 "='SHEET1'!D8*'SHEET2'!$S$6" then pasting down again etc etc
all the way thru to column BB
--
Many Thanks

Gunjani
ENERGY SAVING
- Achieved when the power switch is off. =3F=3F.An
Engineers' Terminology
 
G

Gunjani

"Bernie Deitrick" said:
Gunjani,

Use in C1

='SHEET1'!C8*'SHEET2'!S$5

(note the use of S instead of $S) then copy and paste to your entire range.

HTH,
Bernie
MS Excel MVP
Click to expand...
Thanks but SORRY I made a major error in post it should read as follows
(note the last integar changing in each column):
I have this formula in Cell C1 "='SHEET1'!C8*'SHEET2'!S5"
In C2 I want the formula "='SHEET1'!C9*'SHEET2'!S5"
In C3 I want "='SHEET1'!C10*'SHEET2'!S5"
In C4 I want "='SHEET1'!C11*'SHEET2'!S5"...... to cell C20

However in D1 I want "='SHEET1'!D8*'SHEET2'!S6"
In D2 I want "='SHEET1'!D9*'SHEET2'!S6"
In D3 I want "='SHEET1'!D10*'SHEET2'!S6"
In D4 I want "='SHEET1'!D11*'SHEET2'!S6"..... to cell D20

And in E1 I want "='SHEET1'!E8*'SHEET2'!S7"
In E2 I want"='SHEET1'!E9*'SHEET2'!S7"
In E3 I want "='SHEET1'!E10*'SHEET2'!S7"..... etc

All the way thru to column BB.
Is there an easier way to copy/paste this in ONE HIT currently I am
using in C1 "='SHEET1'!C8*'SHEET2'!$S$5" then pasting down in column C,
then in D1 "='SHEET1'!D8*'SHEET2'!$S$6" then pasting down again etc etc
all the way thru to column BB

--
Many Thanks

Gunjani
Life being what it is, one dreams of revenge.
-- Paul Gauguin
 
B

Bernie Deitrick

Gunjani,

=Sheet1!C8*INDIRECT("'SHEET1'!S"&COLUMN()+2)

HTH,
Bernie
MS Excel MVP
 
B

Bernie Deitrick

I'm sorry, that second Sheet1 should of course be Sheet2 - I changed it for
testing....

=Sheet1!C8*INDIRECT("'SHEET2'!S"&COLUMN()+2)

HTH,
Bernie
MS Excel MVP
 
G

Gunjani

"Bernie Deitrick" said:
I'm sorry, that second Sheet1 should of course be Sheet2 - I changed it for
testing....

=Sheet1!C8*INDIRECT("'SHEET2'!S"&COLUMN()+2)

HTH,
Bernie
MS Excel MVP
Click to expand...

It works EXCEPT only for the first set of rows i.e C1:BB20 (Sheet2!S55)
but when I restart from C21:BB41 (...Sheet2!S56 to ...Sheet2!S95) with
the same criteria it fails.

I'm not familiar with the INDIRECT or COLUMN formula but I guess a
slight tweak with the ..."&COLUMN()+2) part of the formula will do it.

--
Many Thanks

Gunjani
* He Broke your Heart, Shattered it like glass, So when
he comes crawling back to you say hunnie kiss my ass*
 
B

Bernie Deitrick

Gunjani,

Simply change the +2 to a +53 and it will work correctly.

HTH,
Bernie
MS Excel MVP

Gunjani said:
"Bernie Deitrick" said:
I'm sorry, that second Sheet1 should of course be Sheet2 - I changed it for
testing....

=Sheet1!C8*INDIRECT("'SHEET2'!S"&COLUMN()+2)

HTH,
Bernie
MS Excel MVP

Bernie Deitrick said:
Gunjani,

=Sheet1!C8*INDIRECT("'SHEET1'!S"&COLUMN()+2)

HTH,
Bernie
MS Excel MVP
Click to expand...
Click to expand...
Click to expand...
 
G

Gunjani

It works EXCEPT only for the first set of rows i.e C1:BB20 (Sheet2!S55)
but when I restart from C21:BB41 (...Sheet2!S56 to ...Sheet2!S95) with
the same criteria it fails....

OK FOUND THE ANSWER =Sheet1!C8*INDIRECT("'SHEET2'!S"&COLUMN()+54)

But can u explain how u attained reference '&COLUMN()+2', please
--
Many Thanks

Gunjani
"When you control the mail, you control information!" --
Newman, "Seinfeld"
 
B

Bernie Deitrick

Gunjani,

COLUMN() returns the Number of column of the cell that is passed as the
argument, or the number of column of the current cell if no argument is
passed.

Since your formula is entered in column C, COLUMN() returns 3. Since you
wanted S5 in the formula, the part

INDIRECT("'SHEET2'!S"&COLUMN()+2)
evaluates as
INDIRECT("'SHEET2'!S"&3+2)
INDIRECT("'SHEET2'!S"&5)
INDIRECT("'SHEET2'!S5")
which then returns the value from S5. Since you wanted S5 for each formula
in the column, each INDIRECT formula will evaluate the same, returning the
value from S5.

HTH,
Bernie
MS Excel MVP
 
G

Gunjani

"Bernie Deitrick" said:
Gunjani,

COLUMN() returns the Number of column of the cell that is passed as the
argument, or the number of column of the current cell if no argument is
passed.

Since your formula is entered in column C, COLUMN() returns 3. Since you
wanted S5 in the formula, the part

INDIRECT("'SHEET2'!S"&COLUMN()+2)
evaluates as
INDIRECT("'SHEET2'!S"&3+2)
INDIRECT("'SHEET2'!S"&5)
INDIRECT("'SHEET2'!S5")
which then returns the value from S5. Since you wanted S5 for each formula
in the column, each INDIRECT formula will evaluate the same, returning the
value from S5.

HTH,
Bernie
MS Excel MVP
Click to expand...
Thank u very much... is there a good site/book where I could learn all
this i.e Excel Functions for dummies/beginners. But thanks all the same
--
Many Thanks

Gunjani
"If Everton were playing at the bottom of the garden, I'd
pull the curtains."
- Bill Shankly (Legendary Liverpool Manager)
 
B

Bernie Deitrick

Gunjani,

All of the MVP's sites (look at the signature lines) are great on-line
resources. As for books, John Walkenbach has written a series of books,
including one on functions and formulas - I have never seen it, but can only
presume is just as good as his other books.

HTH,
Bernie
MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Transpose between sheets 4
counting how many of the same numbers 1
Display data from another worksheet 2
Fill formula down for cells 74 rows apart 3
How to use vlookup in an array formula 2
Formula to reference another worksheet 4
Conditional sum in a named range 8
Compare formula 2

Top