×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Series Fill for sheet number

Series Fill for sheet number

Series Fill for sheet number

(OP)
I want to fill in a row where each column goes to a different worksheet and gets the value in cell B5

The follwoing would be the first 4 cells in a row  on a work sheet. The first sheet is linked to following sheet to get values for data in row.

Sheet2!$B$5     Sheet3!$B$5    Sheet4!$B$5    Sheet5!$B$5

I can type this manually but want to know if there is a way to drag the first cell and have the diget after the sheet number to fill automatically like it does when yo do a fill series operation.
I have about 40 sheets and have already spent enough time to do it manually.  I am asking to learn a new trick and for the next time.   
Thank you
BJC

RE: Series Fill for sheet number

Some combination of INDIRECT(), ROW() and COLUMN() functions should get you there.

RE: Series Fill for sheet number

There is a little problem with accessing a sequence of sheets. So you'll have to generate the address as a text string, then use INDIRECT to convert the text to a valid reference that Excel can use.
So you could start in row 1 as follows:

A1: =2
B1: =A1+1
C1: =B1+1
and so forth (by copying B1 to the right as far as you need)

Then, in row 2 generate the address text:

A2: ="Sheet"&A1&"!$B$5"
B2: ="Sheet"&B1&"!$B$5"
and so forth (by copying B2 to the right as far as you need)

Then, retrieve the values in row 3:

A3: =INDIRECT(A2)
B3: =INDIRECT(B2)
and so forth (by copying B3 to the right as far as you need)


You can combine a few formulas if you like. I hope it works, haven't tested it.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources