Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Raf Raf is offline
external usenet poster
 
Posts: 13
Default How do I get a worksheet reference to increment when copied

I have a significant number of identical worksheets that may contain
different values I need to consolidate to a single worksheet. If I have a
column in the consolidation worksheet that pulls data from a source worksheet
using cell formulae such as: ='Sheet 1'!$K9, is there a way in which I can
get this to copy into adjacent, assending columns such that the worksheet
reference would be automatically incremented i.e.: ='Sheet 2'!$K9, ='Sheet
3'!$K9, and so forth? I cannot seem to find anything that is on point but I'm
sure there must be a way to accomplish this.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 299
Default How do I get a worksheet reference to increment when copied

You can use INDIRECT

=INDIRECT("'Sheet "&COLUMNS($A$1:A1)&"'!K9")

copied across


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com



"RAF" wrote in message
...
I have a significant number of identical worksheets that may contain
different values I need to consolidate to a single worksheet. If I have a
column in the consolidation worksheet that pulls data from a source
worksheet
using cell formulae such as: ='Sheet 1'!$K9, is there a way in which I can
get this to copy into adjacent, assending columns such that the worksheet
reference would be automatically incremented i.e.: ='Sheet 2'!$K9, ='Sheet
3'!$K9, and so forth? I cannot seem to find anything that is on point but
I'm
sure there must be a way to accomplish this.



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Raf Raf is offline
external usenet poster
 
Posts: 13
Default How do I get a worksheet reference to increment when copied

Peo,

Thank you for responding and pardon my apparent stupidity but this is one
Excel function that I simply cannot seem to grasp. Perhaps if I more
accurately descibed what I'm doing: I have six work sheets named "Disp 1" -
"Disp 6" respectfully and I need to pull values from various cells in the "K"
column of each source sheet and post them into disparate cells on the columns
"X" through "AC", respectfully, of a consolidation sheet named "BOM."

I've tried various revisions of the sample you provided without success. If
possible could you provide a little more detail?

"Peo Sjoblom" wrote:

You can use INDIRECT

=INDIRECT("'Sheet "&COLUMNS($A$1:A1)&"'!K9")

copied across


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com



"RAF" wrote in message
...
I have a significant number of identical worksheets that may contain
different values I need to consolidate to a single worksheet. If I have a
column in the consolidation worksheet that pulls data from a source
worksheet
using cell formulae such as: ='Sheet 1'!$K9, is there a way in which I can
get this to copy into adjacent, assending columns such that the worksheet
reference would be automatically incremented i.e.: ='Sheet 2'!$K9, ='Sheet
3'!$K9, and so forth? I cannot seem to find anything that is on point but
I'm
sure there must be a way to accomplish this.




  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,572
Default How do I get a worksheet reference to increment when copied

It would have been just as easy for you to post the actual sheet names in
your OP, whereas Peo's solution matched your scenario.

Since your sheet numbers are consecutive, just replace the sheet name in the
formula with your sheet names.
NOTE: This is including a <space between name and number just as Peo used,
following your original example:

=INDIRECT("'Disp "&COLUMNS($A:A)&"'!K9")

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"RAF" wrote in message
...
Peo,

Thank you for responding and pardon my apparent stupidity but this is one
Excel function that I simply cannot seem to grasp. Perhaps if I more
accurately descibed what I'm doing: I have six work sheets named "Disp
1" -
"Disp 6" respectfully and I need to pull values from various cells in the
"K"
column of each source sheet and post them into disparate cells on the
columns
"X" through "AC", respectfully, of a consolidation sheet named "BOM."

I've tried various revisions of the sample you provided without success.
If
possible could you provide a little more detail?

"Peo Sjoblom" wrote:

You can use INDIRECT

=INDIRECT("'Sheet "&COLUMNS($A$1:A1)&"'!K9")

copied across


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com



"RAF" wrote in message
...
I have a significant number of identical worksheets that may contain
different values I need to consolidate to a single worksheet. If I have
a
column in the consolidation worksheet that pulls data from a source
worksheet
using cell formulae such as: ='Sheet 1'!$K9, is there a way in which I
can
get this to copy into adjacent, assending columns such that the
worksheet
reference would be automatically incremented i.e.: ='Sheet 2'!$K9,
='Sheet
3'!$K9, and so forth? I cannot seem to find anything that is on point
but
I'm
sure there must be a way to accomplish this.





  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Raf Raf is offline
external usenet poster
 
Posts: 13
Default How do I get a worksheet reference to increment when copied

My apologies for not doing exactly that. Up until recently I have had no
issues in Excel I could not work through myself thus I have little experience
using the discussion group and I used what appeared to be the form from a few
other posts in trying to craft a reasonably clear question however, it is
quite possible that the particular posts that looked at simply were still
using the default worksheet names.

I had actually logged back in to let Peo know that I had succeeded in using
his recommended formula with the obvious substitution and the correct
punctuation which I discovered I got wrong on prior attempts. I do have an
additional question based on your response of:
=INDIRECT("'Disp "&COLUMNS($A:A)&"'!K9")

What difference does the ($A:A) make versus Peo's version ($A$1:A1) which
I'm using?

"Ragdyer" wrote:

It would have been just as easy for you to post the actual sheet names in
your OP, whereas Peo's solution matched your scenario.

Since your sheet numbers are consecutive, just replace the sheet name in the
formula with your sheet names.
NOTE: This is including a <space between name and number just as Peo used,
following your original example:

=INDIRECT("'Disp "&COLUMNS($A:A)&"'!K9")

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"RAF" wrote in message
...
Peo,

Thank you for responding and pardon my apparent stupidity but this is one
Excel function that I simply cannot seem to grasp. Perhaps if I more
accurately descibed what I'm doing: I have six work sheets named "Disp
1" -
"Disp 6" respectfully and I need to pull values from various cells in the
"K"
column of each source sheet and post them into disparate cells on the
columns
"X" through "AC", respectfully, of a consolidation sheet named "BOM."

I've tried various revisions of the sample you provided without success.
If
possible could you provide a little more detail?

"Peo Sjoblom" wrote:

You can use INDIRECT

=INDIRECT("'Sheet "&COLUMNS($A$1:A1)&"'!K9")

copied across


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com



"RAF" wrote in message
...
I have a significant number of identical worksheets that may contain
different values I need to consolidate to a single worksheet. If I have
a
column in the consolidation worksheet that pulls data from a source
worksheet
using cell formulae such as: ='Sheet 1'!$K9, is there a way in which I
can
get this to copy into adjacent, assending columns such that the
worksheet
reference would be automatically incremented i.e.: ='Sheet 2'!$K9,
='Sheet
3'!$K9, and so forth? I cannot seem to find anything that is on point
but
I'm
sure there must be a way to accomplish this.







  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,572
Default How do I get a worksheet reference to increment when copied

AFAIK, less typing with mine.<g

They both return the same values.

If you click in the formula bar, you'll see less cells referenced with his
then mine ... but I doubt if that has any bearing on the XL efficiency.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"RAF" wrote in message
...
My apologies for not doing exactly that. Up until recently I have had no
issues in Excel I could not work through myself thus I have little
experience
using the discussion group and I used what appeared to be the form from a
few
other posts in trying to craft a reasonably clear question however, it is
quite possible that the particular posts that looked at simply were still
using the default worksheet names.

I had actually logged back in to let Peo know that I had succeeded in
using
his recommended formula with the obvious substitution and the correct
punctuation which I discovered I got wrong on prior attempts. I do have an
additional question based on your response of:
=INDIRECT("'Disp "&COLUMNS($A:A)&"'!K9")

What difference does the ($A:A) make versus Peo's version ($A$1:A1) which
I'm using?

"Ragdyer" wrote:

It would have been just as easy for you to post the actual sheet names in
your OP, whereas Peo's solution matched your scenario.

Since your sheet numbers are consecutive, just replace the sheet name in
the
formula with your sheet names.
NOTE: This is including a <space between name and number just as Peo
used,
following your original example:

=INDIRECT("'Disp "&COLUMNS($A:A)&"'!K9")

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"RAF" wrote in message
...
Peo,

Thank you for responding and pardon my apparent stupidity but this is
one
Excel function that I simply cannot seem to grasp. Perhaps if I more
accurately descibed what I'm doing: I have six work sheets named "Disp
1" -
"Disp 6" respectfully and I need to pull values from various cells in
the
"K"
column of each source sheet and post them into disparate cells on the
columns
"X" through "AC", respectfully, of a consolidation sheet named "BOM."

I've tried various revisions of the sample you provided without
success.
If
possible could you provide a little more detail?

"Peo Sjoblom" wrote:

You can use INDIRECT

=INDIRECT("'Sheet "&COLUMNS($A$1:A1)&"'!K9")

copied across


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com



"RAF" wrote in message
...
I have a significant number of identical worksheets that may contain
different values I need to consolidate to a single worksheet. If I
have
a
column in the consolidation worksheet that pulls data from a source
worksheet
using cell formulae such as: ='Sheet 1'!$K9, is there a way in which
I
can
get this to copy into adjacent, assending columns such that the
worksheet
reference would be automatically incremented i.e.: ='Sheet 2'!$K9,
='Sheet
3'!$K9, and so forth? I cannot seem to find anything that is on
point
but
I'm
sure there must be a way to accomplish this.






  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ali Ali is offline
external usenet poster
 
Posts: 80
Default How do I get a worksheet reference to increment when copied

Hi All
I am trying to use same solution as providede below.
I have a sheet called PPD, which is reading through other sheets.
Sheets are labelled 1, 2, 3, 4 etc till 100

on PPD: Starting in cell B8 = 1!$B$3
B9 = 2!$B$3
B10 = 3!$B$3
I would like to drag this formula down to as many sheets as we land up
needing instead of manually having to change the sheet number but i can't
seem to get below suggestion to work
=INDIRECT("'Sheet "&COLUMNS($B:B)&"'!B3")
What am i messing up?
Thanks
"Ragdyer" wrote:

AFAIK, less typing with mine.<g

They both return the same values.

If you click in the formula bar, you'll see less cells referenced with his
then mine ... but I doubt if that has any bearing on the XL efficiency.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"RAF" wrote in message
...
My apologies for not doing exactly that. Up until recently I have had no
issues in Excel I could not work through myself thus I have little
experience
using the discussion group and I used what appeared to be the form from a
few
other posts in trying to craft a reasonably clear question however, it is
quite possible that the particular posts that looked at simply were still
using the default worksheet names.

I had actually logged back in to let Peo know that I had succeeded in
using
his recommended formula with the obvious substitution and the correct
punctuation which I discovered I got wrong on prior attempts. I do have an
additional question based on your response of:
=INDIRECT("'Disp "&COLUMNS($A:A)&"'!K9")

What difference does the ($A:A) make versus Peo's version ($A$1:A1) which
I'm using?

"Ragdyer" wrote:

It would have been just as easy for you to post the actual sheet names in
your OP, whereas Peo's solution matched your scenario.

Since your sheet numbers are consecutive, just replace the sheet name in
the
formula with your sheet names.
NOTE: This is including a <space between name and number just as Peo
used,
following your original example:

=INDIRECT("'Disp "&COLUMNS($A:A)&"'!K9")

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"RAF" wrote in message
...
Peo,

Thank you for responding and pardon my apparent stupidity but this is
one
Excel function that I simply cannot seem to grasp. Perhaps if I more
accurately descibed what I'm doing: I have six work sheets named "Disp
1" -
"Disp 6" respectfully and I need to pull values from various cells in
the
"K"
column of each source sheet and post them into disparate cells on the
columns
"X" through "AC", respectfully, of a consolidation sheet named "BOM."

I've tried various revisions of the sample you provided without
success.
If
possible could you provide a little more detail?

"Peo Sjoblom" wrote:

You can use INDIRECT

=INDIRECT("'Sheet "&COLUMNS($A$1:A1)&"'!K9")

copied across


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com



"RAF" wrote in message
...
I have a significant number of identical worksheets that may contain
different values I need to consolidate to a single worksheet. If I
have
a
column in the consolidation worksheet that pulls data from a source
worksheet
using cell formulae such as: ='Sheet 1'!$K9, is there a way in which
I
can
get this to copy into adjacent, assending columns such that the
worksheet
reference would be automatically incremented i.e.: ='Sheet 2'!$K9,
='Sheet
3'!$K9, and so forth? I cannot seem to find anything that is on
point
but
I'm
sure there must be a way to accomplish this.







  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,766
Default How do I get a worksheet reference to increment when copied

Hi,

Try this. You do not need a spare column

=INDIRECT("'Sheet "&row()&"'!B3")

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Ali" wrote in message
...
Hi All
I am trying to use same solution as providede below.
I have a sheet called PPD, which is reading through other sheets.
Sheets are labelled 1, 2, 3, 4 etc till 100

on PPD: Starting in cell B8 = 1!$B$3
B9 = 2!$B$3
B10 = 3!$B$3
I would like to drag this formula down to as many sheets as we land up
needing instead of manually having to change the sheet number but i can't
seem to get below suggestion to work
=INDIRECT("'Sheet "&COLUMNS($B:B)&"'!B3")
What am i messing up?
Thanks
"Ragdyer" wrote:

AFAIK, less typing with mine.<g

They both return the same values.

If you click in the formula bar, you'll see less cells referenced with
his
then mine ... but I doubt if that has any bearing on the XL efficiency.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"RAF" wrote in message
...
My apologies for not doing exactly that. Up until recently I have had
no
issues in Excel I could not work through myself thus I have little
experience
using the discussion group and I used what appeared to be the form from
a
few
other posts in trying to craft a reasonably clear question however, it
is
quite possible that the particular posts that looked at simply were
still
using the default worksheet names.

I had actually logged back in to let Peo know that I had succeeded in
using
his recommended formula with the obvious substitution and the correct
punctuation which I discovered I got wrong on prior attempts. I do have
an
additional question based on your response of:
=INDIRECT("'Disp "&COLUMNS($A:A)&"'!K9")
What difference does the ($A:A) make versus Peo's version ($A$1:A1)
which
I'm using?

"Ragdyer" wrote:

It would have been just as easy for you to post the actual sheet names
in
your OP, whereas Peo's solution matched your scenario.

Since your sheet numbers are consecutive, just replace the sheet name
in
the
formula with your sheet names.
NOTE: This is including a <space between name and number just as Peo
used,
following your original example:

=INDIRECT("'Disp "&COLUMNS($A:A)&"'!K9")

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may
benefit !
---------------------------------------------------------------------------
"RAF" wrote in message
...
Peo,

Thank you for responding and pardon my apparent stupidity but this
is
one
Excel function that I simply cannot seem to grasp. Perhaps if I more
accurately descibed what I'm doing: I have six work sheets named
"Disp
1" -
"Disp 6" respectfully and I need to pull values from various cells
in
the
"K"
column of each source sheet and post them into disparate cells on
the
columns
"X" through "AC", respectfully, of a consolidation sheet named
"BOM."

I've tried various revisions of the sample you provided without
success.
If
possible could you provide a little more detail?

"Peo Sjoblom" wrote:

You can use INDIRECT

=INDIRECT("'Sheet "&COLUMNS($A$1:A1)&"'!K9")

copied across


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com



"RAF" wrote in message
...
I have a significant number of identical worksheets that may
contain
different values I need to consolidate to a single worksheet. If
I
have
a
column in the consolidation worksheet that pulls data from a
source
worksheet
using cell formulae such as: ='Sheet 1'!$K9, is there a way in
which
I
can
get this to copy into adjacent, assending columns such that the
worksheet
reference would be automatically incremented i.e.: ='Sheet
2'!$K9,
='Sheet
3'!$K9, and so forth? I cannot seem to find anything that is on
point
but
I'm
sure there must be a way to accomplish this.







  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ali Ali is offline
external usenet poster
 
Posts: 80
Default How do I get a worksheet reference to increment when copied

Hi Ashish
I have tried both and still getting a #Ref! error. i have no idea what I am
doing wrong. thanks ali

"Ashish Mathur" wrote:

Hi,

=INDIRECT("'Sheet"&A3&"'!B3"), where A3 has 1, A4 has 2 etc. You can now
copy the formula down.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Ali" wrote in message
...
Hi All
I am trying to use same solution as providede below.
I have a sheet called PPD, which is reading through other sheets.
Sheets are labelled 1, 2, 3, 4 etc till 100

on PPD: Starting in cell B8 = 1!$B$3
B9 = 2!$B$3
B10 = 3!$B$3
I would like to drag this formula down to as many sheets as we land up
needing instead of manually having to change the sheet number but i can't
seem to get below suggestion to work
=INDIRECT("'Sheet "&COLUMNS($B:B)&"'!B3")
What am i messing up?
Thanks
"Ragdyer" wrote:

AFAIK, less typing with mine.<g

They both return the same values.

If you click in the formula bar, you'll see less cells referenced with
his
then mine ... but I doubt if that has any bearing on the XL efficiency.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"RAF" wrote in message
...
My apologies for not doing exactly that. Up until recently I have had
no
issues in Excel I could not work through myself thus I have little
experience
using the discussion group and I used what appeared to be the form from
a
few
other posts in trying to craft a reasonably clear question however, it
is
quite possible that the particular posts that looked at simply were
still
using the default worksheet names.

I had actually logged back in to let Peo know that I had succeeded in
using
his recommended formula with the obvious substitution and the correct
punctuation which I discovered I got wrong on prior attempts. I do have
an
additional question based on your response of:
=INDIRECT("'Disp "&COLUMNS($A:A)&"'!K9")
What difference does the ($A:A) make versus Peo's version ($A$1:A1)
which
I'm using?

"Ragdyer" wrote:

It would have been just as easy for you to post the actual sheet names
in
your OP, whereas Peo's solution matched your scenario.

Since your sheet numbers are consecutive, just replace the sheet name
in
the
formula with your sheet names.
NOTE: This is including a <space between name and number just as Peo
used,
following your original example:

=INDIRECT("'Disp "&COLUMNS($A:A)&"'!K9")

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may
benefit !
---------------------------------------------------------------------------
"RAF" wrote in message
...
Peo,

Thank you for responding and pardon my apparent stupidity but this
is
one
Excel function that I simply cannot seem to grasp. Perhaps if I more
accurately descibed what I'm doing: I have six work sheets named
"Disp
1" -
"Disp 6" respectfully and I need to pull values from various cells
in
the
"K"
column of each source sheet and post them into disparate cells on
the
columns
"X" through "AC", respectfully, of a consolidation sheet named
"BOM."

I've tried various revisions of the sample you provided without
success.
If
possible could you provide a little more detail?

"Peo Sjoblom" wrote:

You can use INDIRECT

=INDIRECT("'Sheet "&COLUMNS($A$1:A1)&"'!K9")

copied across


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com



"RAF" wrote in message
...
I have a significant number of identical worksheets that may
contain
different values I need to consolidate to a single worksheet. If
I
have
a
column in the consolidation worksheet that pulls data from a
source
worksheet
using cell formulae such as: ='Sheet 1'!$K9, is there a way in
which
I
can
get this to copy into adjacent, assending columns such that the
worksheet
reference would be automatically incremented i.e.: ='Sheet
2'!$K9,
='Sheet
3'!$K9, and so forth? I cannot seem to find anything that is on
point
but
I'm
sure there must be a way to accomplish this.







  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,766
Default How do I get a worksheet reference to increment when copied

Hi,

Try one of the following:

=INDIRECT("Sheet "&row()&"'!B3"); or
=INDIRECT("Sheet"&row()&"'!B3")

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Ali" wrote in message
...
Hi Ashish
I have tried both and still getting a #Ref! error. i have no idea what I
am
doing wrong. thanks ali

"Ashish Mathur" wrote:

Hi,

=INDIRECT("'Sheet"&A3&"'!B3"), where A3 has 1, A4 has 2 etc. You can
now
copy the formula down.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Ali" wrote in message
...
Hi All
I am trying to use same solution as providede below.
I have a sheet called PPD, which is reading through other sheets.
Sheets are labelled 1, 2, 3, 4 etc till 100

on PPD: Starting in cell B8 = 1!$B$3
B9 = 2!$B$3
B10 = 3!$B$3
I would like to drag this formula down to as many sheets as we land up
needing instead of manually having to change the sheet number but i
can't
seem to get below suggestion to work
=INDIRECT("'Sheet "&COLUMNS($B:B)&"'!B3")
What am i messing up?
Thanks
"Ragdyer" wrote:

AFAIK, less typing with mine.<g

They both return the same values.

If you click in the formula bar, you'll see less cells referenced with
his
then mine ... but I doubt if that has any bearing on the XL
efficiency.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may
benefit !
---------------------------------------------------------------------------
"RAF" wrote in message
...
My apologies for not doing exactly that. Up until recently I have
had
no
issues in Excel I could not work through myself thus I have little
experience
using the discussion group and I used what appeared to be the form
from
a
few
other posts in trying to craft a reasonably clear question however,
it
is
quite possible that the particular posts that looked at simply were
still
using the default worksheet names.

I had actually logged back in to let Peo know that I had succeeded
in
using
his recommended formula with the obvious substitution and the
correct
punctuation which I discovered I got wrong on prior attempts. I do
have
an
additional question based on your response of:
=INDIRECT("'Disp "&COLUMNS($A:A)&"'!K9")
What difference does the ($A:A) make versus Peo's version ($A$1:A1)
which
I'm using?

"Ragdyer" wrote:

It would have been just as easy for you to post the actual sheet
names
in
your OP, whereas Peo's solution matched your scenario.

Since your sheet numbers are consecutive, just replace the sheet
name
in
the
formula with your sheet names.
NOTE: This is including a <space between name and number just as
Peo
used,
following your original example:

=INDIRECT("'Disp "&COLUMNS($A:A)&"'!K9")

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may
benefit !
---------------------------------------------------------------------------
"RAF" wrote in message
...
Peo,

Thank you for responding and pardon my apparent stupidity but
this
is
one
Excel function that I simply cannot seem to grasp. Perhaps if I
more
accurately descibed what I'm doing: I have six work sheets named
"Disp
1" -
"Disp 6" respectfully and I need to pull values from various
cells
in
the
"K"
column of each source sheet and post them into disparate cells on
the
columns
"X" through "AC", respectfully, of a consolidation sheet named
"BOM."

I've tried various revisions of the sample you provided without
success.
If
possible could you provide a little more detail?

"Peo Sjoblom" wrote:

You can use INDIRECT

=INDIRECT("'Sheet "&COLUMNS($A$1:A1)&"'!K9")

copied across


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com



"RAF" wrote in message
...
I have a significant number of identical worksheets that may
contain
different values I need to consolidate to a single worksheet.
If
I
have
a
column in the consolidation worksheet that pulls data from a
source
worksheet
using cell formulae such as: ='Sheet 1'!$K9, is there a way in
which
I
can
get this to copy into adjacent, assending columns such that
the
worksheet
reference would be automatically incremented i.e.: ='Sheet
2'!$K9,
='Sheet
3'!$K9, and so forth? I cannot seem to find anything that is
on
point
but
I'm
sure there must be a way to accomplish this.







Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Reference to Worksheet name SKY Excel Worksheet Functions 3 May 12th 06 05:15 AM
Copy worksheet & maintain cell reference across worksheets dingy101 Excel Worksheet Functions 3 January 2nd 06 11:51 AM
How do I reference data from one worksheet to another using combob caricc New Users to Excel 0 December 17th 05 10:58 PM
Comparing a list to a Calendar worksheet. PatrickL Excel Worksheet Functions 0 August 25th 05 04:21 PM
How do I automatically increment worksheet number when pasting a . Mr Keldor Excel Worksheet Functions 1 April 21st 05 06:06 PM


All times are GMT +1. The time now is 02:50 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"