Dragging formulas

[Connie]

I have a summary worksheet at the end of 12 other sheets,
each sheet being the figures for the month. On the
summary sheet I have, for instance, "Average Transit Time"
as a row heading, and then JAN, FEB, MAR....DEC" as column
headings. In JAN, the simple formula is ='JAN 2004'!H2.
Now I would like to drag that to the end of the year, but
it gives, of course, ='JAN 2004'!I2, ='JAN 2004'!J2, etc.
How do I drag it to get ='FEB 2004'!H2, ='MAR 2004'!H2. I
want it to pick up the same cell on each worksheet, not
consecutive cells within the same worksheet. Hope I'm
explaining myself clearly.

Thank you
Connie

 

[Frank Kabel]

Hi
if your month names are in row 1 (A1:L1) try the following formula in
A2:
=INDIRECT("'" & A1 & " 2004'!H2")
and copy this formula

 

[Connie]

Frank, this one isn't working. I put in this formula:
=INDIRECT("'" & B3 & " FEB 2004'!H2"). My months are in
cells B3 to M3. I added FEB to the formula because the
worksheets are named JAN 2004, FEB 2004, etc. But I get
#REF! as the answer in the first cell, B4.

I will check back with you on Tuesday next week. I'm
heading home now, and Monday is a holiday in Canada, so
I'll check back here Tuesday.

Thank you
Connie

 

[icestationzbra]

frank's formula would have worked just fine, if you had not used 'FEB
in the middle of your formula.

lets say you have data setup in the following manner...

B3 C3 D3 E3 F3...
JAN FEB MAR APR MAY...

frank's formula picks up the 'JAN', 'FEB', 'MAR'... and appends it wit
'2004', which would result in 'JAN 2004', 'FEB 2004'... which is th
name of your sheet!

so... use frank's formula as is for best results... (changed A1 to B
as thats where your data starts)...

=INDIRECT("'" & B3 & " 2004'!H2"

 

[Frank Kabel]

Hi
remove 'FEB' from the formula. That is why I used B3 in this formula

 

[Connie]

Sorry, it still doesn't work. I still get #REF! I copied
and pasted your formula in C3, as follows:

=INDIRECT("'" & B3 & " 2004'!H2")

Obviously, I'm not understanding something here.

Column headings for the months are in B2 to M2. Therefore
in B3 I put ='JAN 2004'!H2, which gives me the figure for
January. When I put your formula (as above) then in C3 to
get February's, I get #REF! Then I realized you said to
put the formula is A2 which is my B3, so I put it there
(as shown above), and I get a circular reference.

Connie

 

[Frank Kabel]

Hi
if you enter this value in B3 use
=INDIRECT(B3)

 

[Connie]

I think it will be much faster for me to just do it the
long way, because I am not understanding what you are
trying to tell me. I completed another worksheet the long
way and will have to do this one the same way, because I
can't get my head around what you are saying. Sorry.
Connie

 

[David McRitchie]

Hi Connie,
I can't figure out what you want where.

Why don't you describe what you want in the cells and
where they are. So far the only cell address you supplied
were B3:M3 where you have "Jan" through "Dec" which
themselves can be fed in by dragging the Fill Handle
And would populate from your Custom List.

Are you trying to change the year or the H3, is the H3
to still be H3 in the other cells.

Take a look at Fill Handle
http://www.mvps.org/dmcritchie/excel/fillhand.htm

Also take a look at
http://www.mvps.org/dmcritchie/excel/buildtoc2.htm

 

[Connie]

Well, I thought I explained it clearly from the beginning,
but maybe I didn't and maybe that's the problem. I know
it's hard sometimes to figure out what people are trying
to do. I've looked at some of the other questions too,
and I have scratched my head at some of them. Anyway, I
will try to explain again:

My workbook is 13 worksheets. On the 13th worksheet,
which I've called Summary, it is as follow:
In B2:M2 I have JAN, FEB, MAR, etc.
In B3 I have the formula: ='JAN 2004'!H2, which is
Worksheet 1. Each worksheet is called a month, hence 12
other worksheets.

C3 now would normally be ='FEB 2004'!H2, and the only way
I know to do this is enter each one individually because
when you drag from B3 you get ='JAN 2004'!I2. I need a
formula that would fill in picking up the number in cell
H2 from each consecutive worksheet, not the next cell
within the same worksheet.

Hope this is clearer.

Thank you
Connie

 

[David McRitchie]

Hi Connie,
Since you are dragging horizontally and change the H2 to $H2
That way the "H" portion will not change when using the fill handle.

If you make changes to the other worksheets that change their
addresses then other worksheets will get a change to their addresses
as well as long as the address are accessible basically not within
quotes.

You might then reread some of the answers, particularly
.http://www.mvps.org/dmcritchie/excel/fillhand.htm

you might also look at Chip Pearson's relative vs absolute addressing
Relative And Absolute Addressing
http://www.cpearson.com/excel/relative.htm

 

[Frank Kabel]

Hi Connie
enter the following formula as is in cell B3:
=INDIRECT("'" & TRIM(B2) & " 2004'!H2")
and copy this to the right.

this formula expects that all your sheets arne name
MMM 2004

 

[Connie]

I get #REF! with this formula. Yes, my sheets are named
JAN, FEB, MAR, APR, MAY, JUN, ETC.

Connie

 

[Frank Kabel]

Hi Connie
first you told us that your sheets are named for example JAN 2004 (so
month name plus year value). If your sheets are named only JAN then use
the formula:
=INDIRECT("'" & TRIM(B2) &"!H2")

So to check if this formula is correct for you just enter everything
that is within the brackets as a sparate formula so
="'" & TRIM(B2) &"!H2")
This should return a string which matches EXACTLY with a manual entered
sheet reference.

If this still does not work email me your example file
email: frank[dot]kabel[at]freenet[dot]de


--
Regards
Frank Kabel
Frankfurt, Germany

I get #REF! with this formula. Yes, my sheets are named
JAN, FEB, MAR, APR, MAY, JUN, ETC.

Connie

-----Original Message-----
Hi Connie
enter the following formula as is in cell B3:
=INDIRECT("'" & TRIM(B2) & " 2004'!H2")
and copy this to the right.

this formula expects that all your sheets arne name
MMM 2004


--
Regards
Frank Kabel
Frankfurt, Germany

Well, I thought I explained it clearly from the beginning,
but maybe I didn't and maybe that's the problem. I know
it's hard sometimes to figure out what people are trying
to do. I've looked at some of the other questions too,
and I have scratched my head at some of them. Anyway, I
will try to explain again:

My workbook is 13 worksheets. On the 13th worksheet,
which I've called Summary, it is as follow:
In B2:M2 I have JAN, FEB, MAR, etc.
In B3 I have the formula: ='JAN 2004'!H2, which is
Worksheet 1. Each worksheet is called a month, hence 12
other worksheets.

C3 now would normally be ='FEB 2004'!H2, and the only way
I know to do this is enter each one individually because
when you drag from B3 you get ='JAN 2004'!I2. I need a
formula that would fill in picking up the number in cell
H2 from each consecutive worksheet, not the next cell
within the same worksheet.

Hope this is clearer.

Thank you
Connie


-----Original Message-----
Hi Connie,
I can't figure out what you want where.

Why don't you describe what you want in the cells and
where they are. So far the only cell address you supplied
were B3:M3 where you have "Jan" through "Dec" which
themselves can be fed in by dragging the Fill Handle
And would populate from your Custom List.

Are you trying to change the year or the H3, is the H3
to still be H3 in the other cells.

Take a look at Fill Handle
http://www.mvps.org/dmcritchie/excel/fillhand.htm

Also take a look at
http://www.mvps.org/dmcritchie/excel/buildtoc2.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages:
http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:
http://www.mvps.org/dmcritchie/excel/search.htm

I think it will be much faster for me to just do it the
long way, because I am not understanding what you are
trying to tell me. I completed another worksheet the long




.

.

 

[Connie]

If I put $H2 instead of just H2, then I get the same
answer in every month---that is, H2 from the worksheet JAN
2004 goes in all the months, instead of H2 from FEB 2004
going under the column heading FEB on the summary sheet,
H2 from MAR 2004 going under the column heading MAR on the
summary sheet, etc.

I have entered the formulas individually. Below are the
values I want in each cell without having to enter them
individually. I have many rows to do the same way. It's
a long process enter them this way, but so far is the only
thing that works.

A B C D
1 JAN FEB MAR
2 ='JAN 2004'!H2 ='FEB 2004'!H2 ='MAR 2004'!H2

Both web sites you gave me to read are to do with filling
cells within the SAME worksheet. I am wanting to pick the
H2 cell in each consecutive worksheet and plunk them all
within the summary worksheet without having to do each one
individually.

Connie

-----Original Message-----
Hi Connie,
Since you are dragging horizontally and change the H2 to $H2
That way the "H" portion will not change when using the fill handle.

If you make changes to the other worksheets that change their
addresses then other worksheets will get a change to their addresses
as well as long as the address are accessible basically not within
quotes.

You might then reread some of the answers, particularly
.http://www.mvps.org/dmcritchie/excel/fillhand.htm

you might also look at Chip Pearson's relative vs absolute addressing
Relative And Absolute Addressing
http://www.cpearson.com/excel/relative.htm



"Connie" <[email protected]> wrote in

message news:[email protected]...

 

[Connie]

Frank, you will see by my response to David McRitchie that
I have changed the cells. So, I did paste this formula
here in cell B2. I deleted a row, that's why things
changed from the last time I wrote about this.

Connie

 

[Frank Kabel]

Hi Connie
the problem of the #REF error is that the sheet name does not match
with the string in the INDIRECT formula. So it is absolutely necessary
to have the EXACT sheetname and the EXACT value from row 2 to make this
formula work.

--
Regards
Frank Kabel
Frankfurt, Germany

Frank, you will see by my response to David McRitchie that
I have changed the cells. So, I did paste this formula
here in cell B2. I deleted a row, that's why things
changed from the last time I wrote about this.

Connie

-----Original Message-----
Hi Connie
enter the following formula as is in cell B3:
=INDIRECT("'" & TRIM(B2) & " 2004'!H2")
and copy this to the right.

this formula expects that all your sheets arne name
MMM 2004


--
Regards
Frank Kabel
Frankfurt, Germany

Well, I thought I explained it clearly from the beginning,
but maybe I didn't and maybe that's the problem. I know
it's hard sometimes to figure out what people are trying
to do. I've looked at some of the other questions too,
and I have scratched my head at some of them. Anyway, I
will try to explain again:

My workbook is 13 worksheets. On the 13th worksheet,
which I've called Summary, it is as follow:
In B2:M2 I have JAN, FEB, MAR, etc.
In B3 I have the formula: ='JAN 2004'!H2, which is
Worksheet 1. Each worksheet is called a month, hence 12
other worksheets.

C3 now would normally be ='FEB 2004'!H2, and the only way
I know to do this is enter each one individually because
when you drag from B3 you get ='JAN 2004'!I2. I need a
formula that would fill in picking up the number in cell
H2 from each consecutive worksheet, not the next cell
within the same worksheet.

Hope this is clearer.

Thank you
Connie


-----Original Message-----
Hi Connie,
I can't figure out what you want where.

Why don't you describe what you want in the cells and
where they are. So far the only cell address you supplied
were B3:M3 where you have "Jan" through "Dec" which
themselves can be fed in by dragging the Fill Handle
And would populate from your Custom List.

Are you trying to change the year or the H3, is the H3
to still be H3 in the other cells.

Take a look at Fill Handle
http://www.mvps.org/dmcritchie/excel/fillhand.htm

Also take a look at
http://www.mvps.org/dmcritchie/excel/buildtoc2.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages:
http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:
http://www.mvps.org/dmcritchie/excel/search.htm

I think it will be much faster for me to just do it the
long way, because I am not understanding what you are
trying to tell me. I completed another worksheet the long




.

.

 

[Connie]

Sorry, Frank, I am getting so bogged down with trying to
figure this all out! The sheet tabs are named JAN 2004,
FEB 2004, MARCH 2004, etc. It's the column headings on
the Summary sheet that read simply JAN, FEB, MAR, etc.

Connie

-----Original Message-----
Hi Connie
first you told us that your sheets are named for example JAN 2004 (so
month name plus year value). If your sheets are named only JAN then use
the formula:
=INDIRECT("'" & TRIM(B2) &"!H2")

So to check if this formula is correct for you just enter everything
that is within the brackets as a sparate formula so
="'" & TRIM(B2) &"!H2")
This should return a string which matches EXACTLY with a manual entered
sheet reference.

If this still does not work email me your example file
email: frank[dot]kabel[at]freenet[dot]de


--
Regards
Frank Kabel
Frankfurt, Germany

I get #REF! with this formula. Yes, my sheets are named
JAN, FEB, MAR, APR, MAY, JUN, ETC.

Connie

-----Original Message-----
Hi Connie
enter the following formula as is in cell B3:
=INDIRECT("'" & TRIM(B2) & " 2004'!H2")
and copy this to the right.

this formula expects that all your sheets arne name
MMM 2004


--
Regards
Frank Kabel
Frankfurt, Germany


Connie wrote:
Well, I thought I explained it clearly from the beginning,
but maybe I didn't and maybe that's the problem. I know
it's hard sometimes to figure out what people are trying
to do. I've looked at some of the other questions too,
and I have scratched my head at some of them. Anyway, I
will try to explain again:

My workbook is 13 worksheets. On the 13th worksheet,
which I've called Summary, it is as follow:
In B2:M2 I have JAN, FEB, MAR, etc.
In B3 I have the formula: ='JAN 2004'!H2, which is
Worksheet 1. Each worksheet is called a month, hence 12
other worksheets.

C3 now would normally be ='FEB 2004'!H2, and the only way
I know to do this is enter each one individually because
when you drag from B3 you get ='JAN 2004'!I2. I need a
formula that would fill in picking up the number in cell
H2 from each consecutive worksheet, not the next cell
within the same worksheet.

Hope this is clearer.

Thank you
Connie


-----Original Message-----
Hi Connie,
I can't figure out what you want where.

Why don't you describe what you want in the cells and
where they are. So far the only cell address you supplied
were B3:M3 where you have "Jan" through "Dec" which
themselves can be fed in by dragging the Fill Handle
And would populate from your Custom List.

Are you trying to change the year or the H3, is the H3
to still be H3 in the other cells.

Take a look at Fill Handle
http://www.mvps.org/dmcritchie/excel/fillhand.htm

Also take a look at
http://www.mvps.org/dmcritchie/excel/buildtoc2.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages:
http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:
http://www.mvps.org/dmcritchie/excel/search.htm

I think it will be much faster for me to just do it the
long way, because I am not understanding what you are
trying to tell me. I completed another worksheet the long




.
.

.

 

[Connie]

I give up! I have no idea where the error lies.

-----Original Message-----
Hi Connie
the problem of the #REF error is that the sheet name does not match
with the string in the INDIRECT formula. So it is absolutely necessary
to have the EXACT sheetname and the EXACT value from row 2 to make this
formula work.

--
Regards
Frank Kabel
Frankfurt, Germany

Frank, you will see by my response to David McRitchie that
I have changed the cells. So, I did paste this formula
here in cell B2. I deleted a row, that's why things
changed from the last time I wrote about this.

Connie

-----Original Message-----
Hi Connie
enter the following formula as is in cell B3:
=INDIRECT("'" & TRIM(B2) & " 2004'!H2")
and copy this to the right.

this formula expects that all your sheets arne name
MMM 2004


--
Regards
Frank Kabel
Frankfurt, Germany


Connie wrote:
Well, I thought I explained it clearly from the beginning,
but maybe I didn't and maybe that's the problem. I know
it's hard sometimes to figure out what people are trying
to do. I've looked at some of the other questions too,
and I have scratched my head at some of them. Anyway, I
will try to explain again:

My workbook is 13 worksheets. On the 13th worksheet,
which I've called Summary, it is as follow:
In B2:M2 I have JAN, FEB, MAR, etc.
In B3 I have the formula: ='JAN 2004'!H2, which is
Worksheet 1. Each worksheet is called a month, hence 12
other worksheets.

C3 now would normally be ='FEB 2004'!H2, and the only way
I know to do this is enter each one individually because
when you drag from B3 you get ='JAN 2004'!I2. I need a
formula that would fill in picking up the number in cell
H2 from each consecutive worksheet, not the next cell
within the same worksheet.

Hope this is clearer.

Thank you
Connie


-----Original Message-----
Hi Connie,
I can't figure out what you want where.

Why don't you describe what you want in the cells and
where they are. So far the only cell address you supplied
were B3:M3 where you have "Jan" through "Dec" which
themselves can be fed in by dragging the Fill Handle
And would populate from your Custom List.

Are you trying to change the year or the H3, is the H3
to still be H3 in the other cells.

Take a look at Fill Handle
http://www.mvps.org/dmcritchie/excel/fillhand.htm

Also take a look at
http://www.mvps.org/dmcritchie/excel/buildtoc2.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages:
http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:
http://www.mvps.org/dmcritchie/excel/search.htm

I think it will be much faster for me to just do it the
long way, because I am not understanding what you are
trying to tell me. I completed another worksheet the long




.
.

.

 

[David McRitchie]

Hi Connie,
Don't give up yet.

B2: Jan
Propagates across row 2 as Jan, Feb, Mar, Apr etc.
using the Custom list, if you have it there otherwise
type them across as you have done.

If you have something in C2: like
=INDIRECT("'" & TRIM(B2) & " 2004'!H2")

Also make up a C4 like
="'" & TRIM(B2) & " 2004'!H2"
So you will see from the 4th row exactly what you are creating.
as used within the INDIRECT Worksheet Formula.

progagate your C4 cell across just like you did for C3, you could
do them both (B3:B4) or all three (B2:B4) the same time with the fill handle.

B2: Jan
B3: =INDIRECT("'" & TRIM(B2) & " 2004'!H2")
B4: ="'" & TRIM(B2) & " 2004'!H2"

C2: Feb
C3: =INDIRECT("'" & TRIM(C2) & " 2004'!H2")
C4: ="'" & TRIM(C2) & " 2004'!H2"

When using Month names in sheetnames, or in filenames, do
consider whether having a form like 2004-09 for a month,
or 2004-09-15 for a date, might make more sense as they can
be sorted and will appear in the correct order.

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Hi Connie
the problem of the #REF error is that the sheet name does not match
with the string in the INDIRECT formula. So it is absolutely necessary
to have the EXACT sheetname and the EXACT value from row 2 to make this
formula work.

--
Regards
Frank Kabel
Frankfurt, Germany

Frank, you will see by my response to David McRitchie that
I have changed the cells. So, I did paste this formula
here in cell B2. I deleted a row, that's why things
changed from the last time I wrote about this.

Connie

-----Original Message-----
Hi Connie
enter the following formula as is in cell B3:
=INDIRECT("'" & TRIM(B2) & " 2004'!H2")
and copy this to the right.

this formula expects that all your sheets arne name
MMM 2004


--
Regards
Frank Kabel
Frankfurt, Germany


Connie wrote:
Well, I thought I explained it clearly from the beginning,
but maybe I didn't and maybe that's the problem. I know
it's hard sometimes to figure out what people are trying
to do. I've looked at some of the other questions too,
and I have scratched my head at some of them. Anyway, I
will try to explain again:

My workbook is 13 worksheets. On the 13th worksheet,
which I've called Summary, it is as follow:
In B2:M2 I have JAN, FEB, MAR, etc.
In B3 I have the formula: ='JAN 2004'!H2, which is
Worksheet 1. Each worksheet is called a month, hence 12
other worksheets.

C3 now would normally be ='FEB 2004'!H2, and the only way
I know to do this is enter each one individually because
when you drag from B3 you get ='JAN 2004'!I2. I need a
formula that would fill in picking up the number in cell
H2 from each consecutive worksheet, not the next cell
within the same worksheet.

Hope this is clearer.

Thank you
Connie


-----Original Message-----
Hi Connie,
I can't figure out what you want where.

Why don't you describe what you want in the cells and
where they are. So far the only cell address you supplied
were B3:M3 where you have "Jan" through "Dec" which
themselves can be fed in by dragging the Fill Handle
And would populate from your Custom List.

Are you trying to change the year or the H3, is the H3
to still be H3 in the other cells.

Take a look at Fill Handle
http://www.mvps.org/dmcritchie/excel/fillhand.htm

Also take a look at
http://www.mvps.org/dmcritchie/excel/buildtoc2.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages:
http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:
http://www.mvps.org/dmcritchie/excel/search.htm

I think it will be much faster for me to just do it the
long way, because I am not understanding what you are
trying to tell me. I completed another worksheet the long




.
.