D
David McRitchie
Change the sheetname from MARCH 2004 to MAR 2004
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
.
.
.
-----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
.
.
.
.
-----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
.
.
.
Connie said: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
.
.
.
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
.
.
.
message news:[email protected]...-----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
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
C3,-----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
you couldfilenames,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
doMcRitchieconsider 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
thatproblem. II 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
knowquestionsit's hard sometimes to figure out what people are trying
to do. I've looked at some of the other
too,inand 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
cellcellsH2 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
andyouwhere they are. So far the only cell address
supplieddowere 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
it theyoulong way, because I am not understanding what
areworksheettrying to tell me. I completed another
the long.
.
.
.
-----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
name-----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
and email addressesthanare generally going to provide more reliable answersmessage news:[email protected]...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 inwhichDavid, 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,number.incell
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
.
.
.
.
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.