Incrementing worksheet number in a formula ?

[Millsy_22]

I have data in workbooks and on worksheets and I would like to show some of it on another workbook. I understand how to get the values from the cell I want but is there a way to copy and paste the formula so that it changes incrementally.

I have a workbook called Trends, with numbered worksheets (1, 2, 3..etc) and I want to display the values in another workbook called Reports.

The cell values I need from Trends Are B7, D7 and F7 and want to display them in Reports at A1, A2 and A3.
So in I have in Reports A1=’[Trends]1’!$B$7
A2=’[Trends]1’!$D$7
A3=’[Trends]1’!$F$7

Now in A4, A5 and A6 I want the Values from Trends BUT from the 2nd worksheet. I know it would just be, A4=’[Trends]2’!$B$7…etc

I have a lot to do, so I do not want to manually type them but when you copy and paste the formula down, the “sheet number” does not increment it stays the same.

I have never had to do this before (and am only assuming it is possible) and tried a couple of small things but I can not seem to get it to work.

I hope I have explained this clearly enough that it makes sense. If not or if more information is needed to assist me, please let me know.

 

[Special-K99]

Google 3D Reference Excel

If the sheets you want to sum have the same format you can sum the same cell on multiple worksheets wih one small formula

 

[steve the fish]

Hi. This seems to work. Looks a bit long so may be able to be improved on.

=INDEX(INDIRECT("'[Trends.xlsx]"&CEILING(ROWS($A$1:A1)/3,1)&"'!B7:F7"),MOD(ROWS($A$1:A1)*2-2,6)+1)

 

[Millsy_22]

Sorry for the slow response, Special-K99 I was looking to keep the values from the first sheet and show them on the 2nd sheet, not do sums, but thank you anyway, the 3D reference will come in handy

Google 3D Reference Excel

If the sheets you want to sum have the same format you can sum the same cell on multiple worksheets wih one small formula

 

[Millsy_22]

Steve The Fish,

You lost me with your formula but it does exactly what I asked for thank you. It took me a bit to figure out how but I have managed to get it working for other cells in my sheets to, so I can transfer all the info I wanted to.

The only problem I have found, which I did not think of ahead of time was that it will only work if I have both workbooks open, otherwise I get #ref errors. This does make sense I guess, I just didn't consider it beforehand.

Is there a way to "import" the info I want to cherry pick from the 1st workbook into the 2nd, and then somehow save it with the actual values so that if another person was to open the 2nd workbook only they would see the actual values, or am I just asking to much.

Thanks Steve and anyone else who may offer any advice.