Continuing INDIRECT dynamic series

marccc

New Member
Joined
Apr 22, 2015
Messages
5
Hi all,

I am trying to fill a series for an INDRECT formula that links to another worksheet.

For example I have 2 Worksheets: 'Overview' and 'Staff Member'.

The formula I'm using is: =INDIRECT("'"&$B$2&"'!a1") where B2 contains 'Staff Member' and a1 the information in the cell on the Staff Member worksheet.

This formula works fine.

What I am trying to do now is drag the cell to the right and down but a1 isn't changing to b1 and a2 respectively.

Ideally, I'd also like to be able to drag right and also skip to every 6th column also (a1 to f1), but I feel like I'm pushing my luck!


I've spent a few hours trying every combination I can think of, and online searching for a solution without any success.

Hopefully someone knows!
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
You absolute legend XOR LX!!

Now if I wanted to skip every 6th column, but still every row, is there a work around for that?

For example:

a1 f1 k1
a2 f2 k2
 
Last edited:
Upvote 0
Sure.

=INDIRECT("'"&$B$2&"'!"&CELL("address",INDEX(1:1,,5*(COLUMNS($A:A)-1)+1)))

Regards
 
Upvote 0
My last question (I swear!) - If I'm wanting to expand the number of worksheet so that the data in each cell is added together (ie Staff Member 1 cell a1 + Staff Member 2 cell a1....etc), where:

B2 - Staff Member 1
B3 - Staff Member 2
B4 - Staff Member 3 etc

I tried: =INDIRECT("'"&$B$2:$B$4&"'!"&CELL("address",INDEX(1:1,,5*(COLUMNS($A:A)-1)+1))) but get #VALUE!

Thoughts?
 
Upvote 0
Try:

=SUMPRODUCT(N(INDIRECT("'"&$B$2:$B$4&"'!"&CELL("address",INDEX(1:1,5*(COLUMNS($A:A)-1)+1)))))

Regards
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top