Dragging formulas

Change the sheetname from MARCH 2004 to MAR 2004

Hi Connie
send me the file :-)

--
Regards
Frank Kabel
Frankfurt, Germany


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




.
.

.

Frank, I may do that. I was thinking that yesterday,
wondering if that could be a possibility. Right now I'm
working on another part of the file, and I want to see if
I can make heads or tales of what David McRitchie is
saying in his post 1:12:49 first. If I can't figure it
out I will send it to you. Thank you. Connie

-----Original Message-----
Hi Connie
send me the file :-)

--
Regards
Frank Kabel
Frankfurt, Germany


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




.
.

.

.

David, you've lost me. I tried to follow what you were
saying, step-by-step, but I ended up with #REF!. I am
going to e-mail the file to Frank. Thank you so much for
all your input. Maybe Frank can post back here with what
he found. Connie


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




.
.




.

Hi Connie,
If you used a real email address, I would have sent you
a small workbook a while back. But thought I had the solutions for you each
time. Then I saw Frank's reply to email him the workbook, which
would make sure that you get the correct solution.

I think those that use their first and last names along with a real email
address (even if the email address is only for newsgroups), get a lot more
out of the newsgroups. Those that don't hide their name and email addresses
are generally going to provide more reliable answers than someone
with a name like "masked bandit" or "MB1239" who figures you'd
never know who they are. Many of the sites with so called
"throwaway" addresses are improving so you don't have to throw them
away because they are doing a better job at filtering spam, and viruses.
But you do have to check them just as often as you check your regular
email.
---
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 ...
David, you've lost me. I tried to follow what you were
saying, step-by-step, but I ended up with #REF!. I am
going to e-mail the file to Frank. Thank you so much for
all your input. Maybe Frank can post back here with what
he found. Connie


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




.
.




.

Hi Connie
as a follow-up. the formula I used on your file was:
=INDIRECT("'" & B$1 & " 2004'!H2")

where row 1 starting in cell B1 contains the month name in the format
'MMM' (e.g. JAN, FEB, ..., DEC)


--
Regards
Frank Kabel
Frankfurt, Germany


Connie wrote:
David, you've lost me. I tried to follow what you were
saying, step-by-step, but I ended up with #REF!. I am
going to e-mail the file to Frank. Thank you so much for
all your input. Maybe Frank can post back here with what
he found. Connie


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




.
.




.

I hear you, and I understand. I don't have time to go
through my lengthy explanation as to why I chose not to
put a legitimate e-mail address, but am considering
setting up another identity with my current home e-mail
address for this type of thing. I have reasons to not
divulge my work e-mail address or home e-mail address in
these newsgroups.

Thank you very much for all your input. I really
appreciate it. This newsgroup is an invaluable tool.
You'll be seeing me again likely, and will know me by my
name "Connie Martin".

Regards,
Connie


-----Original Message-----
Hi Connie,
If you used a real email address, I would have sent you
a small workbook a while back. But thought I had the
solutions for you each
time. Then I saw Frank's reply to email him the
workbook, which
would make sure that you get the correct solution.

I think those that use their first and last names along
with a real email
address (even if the email address is only for
newsgroups), get a lot more
out of the newsgroups. Those that don't hide their name
and email addresses
are generally going to provide more reliable answers than
someone
with a name like "masked bandit" or "MB1239" who
figures you'd
never know who they are. Many of the sites with so
called
"throwaway" addresses are improving so you don't have to
throw them
away because they are doing a better job at filtering
spam, and viruses.
But you do have to check them just as often as you check
your regular
email.
---
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 ...
David, you've lost me. I tried to follow what you were
saying, step-by-step, but I ended up with #REF!. I am
going to e-mail the file to Frank. Thank you so much
for
all your input. Maybe Frank can post back here with
what
he found. Connie


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




.
.




.



.

I just reread my post. It sounds abrupt. I didn't mean
to sound that way because I wasn't feeling that way!

Thanks again!
Connie

-----Original Message-----
I hear you, and I understand. I don't have time to go
through my lengthy explanation as to why I chose not to
put a legitimate e-mail address, but am considering
setting up another identity with my current home e-mail
address for this type of thing. I have reasons to not
divulge my work e-mail address or home e-mail address in
these newsgroups.

Thank you very much for all your input. I really
appreciate it. This newsgroup is an invaluable tool.
You'll be seeing me again likely, and will know me by my
name "Connie Martin".

Regards,
Connie


-----Original Message-----
Hi Connie,
If you used a real email address, I would have sent you
a small workbook a while back. But thought I had the
solutions for you each
time. Then I saw Frank's reply to email him the
workbook, which
would make sure that you get the correct solution.

I think those that use their first and last names along
with a real email
address (even if the email address is only for
newsgroups), get a lot more
out of the newsgroups. Those that don't hide their
name
and email addresses
are generally going to provide more reliable answers
than
someone
with a name like "masked bandit" or "MB1239" who
figures you'd
never know who they are. Many of the sites with so
called
"throwaway" addresses are improving so you don't have to
throw them
away because they are doing a better job at filtering
spam, and viruses.
But you do have to check them just as often as you check
your regular
email.
---
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 ...
David, you've lost me. I tried to follow what you were
saying, step-by-step, but I ended up with #REF!. I am
going to e-mail the file to Frank. Thank you so much
for
all your input. Maybe Frank can post back here with
what
he found. Connie


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




.
.




.



.

.

Hi Connie,
I didn't think of your post as abrupt at all, far from it, especially
since i'd already noticed your full name before I opened it.
Seems a lot more friendly.and like someone who might even
stick around to help to help others as well.
---
Thanks,
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 Martin" wrotel...
I just reread my post. It sounds abrupt. I didn't mean
to sound that way because I wasn't feeling that way!

Thanks again!
Connie