Dragging formulas

[David McRitchie]

Change the sheetname from MARCH 2004 to MAR 2004

 

[Frank Kabel]

Hi Connie
send me the file

--
Regards
Frank Kabel
Frankfurt, Germany

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




.
.

.

 

[Connie]

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

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

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]

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

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

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

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

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

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

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

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

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

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.


"Connie" <[email protected]> wrote in

message news:[email protected]...

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

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

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

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.


"Connie" <[email protected]> wrote in

message news:[email protected]...

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

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

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" <[email protected]>
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 McRitchie]

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.