Dragging formulas

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

---
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

"Connie" wrote in message ...
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

"Connie" wrote...
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




.

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

"Connie" wrote...
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




.

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

"Connie" wrote...
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




.
.

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


Connie wrote:
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

"Connie" wrote...
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




.
.

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

---
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

"Connie" wrote in
message ...
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

"Connie" wrote...
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




.



.

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

"Connie" wrote...
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




.
.

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


Connie wrote:
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

"Connie" wrote...
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




.
.

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


Connie wrote:
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

"Connie"
wrote...
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




.
.

.

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


Connie wrote:
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

"Connie"
wrote...
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




.
.

.

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

"Frank Kabel" wrote in 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


Connie wrote:
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

"Connie" wrote...
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




.
.