Pasting cells from multiple sheets onto one sheet

**Jhertilus** · 06-30-2013, 10:57 AM

I have created a workbook wilth multiple sheets, with each sheet being exactly the same. I would like to be able to post cells from each sheet onto a new sheet in a results page on a row.

My issue

I want to paste - "=Person!C19&"%"&" - "&Person!B1" down a row with each new entry assuming the name of the next sheet, but instead of getting "=Person2!C19&"%"&" - "&Person2!B1", I get =Person!C20&"%"&" - "&Person!B2

Is there anything short of entering in each value manually that I can do to expidite this process? All subsequent sheets have a the name person2,person3,person4 etc. all the way to 100. Any advice would be greatly appreciated.

**XOR LX** · 06-30-2013, 11:06 AM

Hi,

Try: =INDIRECT("Person"&ROWS($A$1:$A1)&"!C19")&"%"&" - "&INDIRECT("Person"&ROWS($A$1:$A1)&"!B1")

in your first row and copy down.

Regards

**Jhertilus** · 06-30-2013, 11:12 AM

I got a #ref message, what am I supposed to be replacing the ($A$1:$A1) or what is that supposed to connect me to because it just highlights the first row of the results sheet

**XOR LX** · 06-30-2013, 11:19 AM

Have you not actually got a tab called Person1 then? The formula will, on successive rows, return a concatenation of cells C19 and B1 from sheet Person1, Person2, Person3, etc.

Regards

**Jhertilus** · 06-30-2013, 11:35 AM

The results tab however is named results...does that matter?

**Jhertilus** · 06-30-2013, 11:33 AM

Sorry for the double post if that's taboo

I pasted the formula into the next cell and it read
=INDIRECT("Person"&ROWS($A$1:$A2)&"!C19")&"%"&" - "&INDIRECT("Person"&ROWS($A$1:$A2)&"!B1")

I do have a sheet named person1

**Jhertilus** · 06-30-2013, 11:46 AM

WAIT....it worked! I dont know what I did different but it worked!

**XOR LX** · 06-30-2013, 11:52 AM

Ok, so you pasted the formula, but what result did you get?

The ROWS part, when copied down, is designed to give successively ROWS($A$1:$A2) (=2), ROWS($A$1:$A3) (=3), ROWS($A$1:$A4) (=4), etc. which will append to the end of "Person" and therefore tell Excel to look in, successively, Person1, Person2, Person3, Person4, etc. Providing your tabs are named precisely that (with no spaces), I can't see what the problem could be.

Regards

Edit: just seen your post - so all is good, then? Can you mark this one as solved now?