Refer to cell on different sheet plus 2 rows

[Jimbola]

Hello I have this formula pointing to a cell on a different sheet;

A1
='Sheet2'!A1

In A2 I want the formula to point to 'Sheet2'!A2 so basically 'Sheet2'!A1+1 row. I thought I could use OFFSET with INDIRECT but was unable to make it work.

 

[Jimbola]

Here you say you want B1 to refer to Sheet2!A2



Here you say you want B2 to refer to Sheet2!A2

Can you see my confusion? I don't understand your requirement, as it is changing every post.

The requirement is the same, I am sorry that I keep messing up the A1/B1/B2 it should be;


Sheet2A1 contains 1
Sheet2A2 contains 2

Sheet1A1 = 'Sheet2'!A1 returns 1
Sheet1B1 = formula that returns 2 by referencing Sheet1A1

 

[MrKowz]

Ok - I think I understand your requirement now.

Herein lies the problem - formulas are not able to look at another cell's formula and return things such as the sheet name (not without a UDF, at least to my knowledge). Is there a reason why mine or Rick's formulas will not work for you?

 

[Jimbola]

Ok - I think I understand your requirement now.

Herein lies the problem - formulas are not able to look at another cell's formula and return things such as the sheet name (not without a UDF, at least to my knowledge). Is there a reason why mine or Rick's formulas will not work for you?

The sheets I mentioned contain event booking information e.g. name, date, attendees, etc in a form format (i.e. A1=name, A2=Date, A6=Attendees, F6=Cancelled H6=Cancelled etc. So on sheet1 I want to create a one line summary of each event, for a few it's OK i can just manually link to the cells, but for 100+ that is not going to be feasible. Each form is the same, so I am looking for a way to create a formula that will get that information. So I was thinking in cell A1 is can automagically put in the first cell/name of each event form and then somehow use that.

Is that helpful?

 

[Swapnil Shah]

Hi there,

Please use below formula in Cell A1 of Sheet 1 and copy it down and across.

=IFERROR(INDIRECT("Sheet"&(ROW()+1)&"!A"&COLUMN()),"")

This will populate sheet 2 First Column to sheet 1 First Row , Sheet 3 First column to sheet 1 Second row and so on...

Hope this is what you wanted.

Thanks
Swapnil Shah

 

[Jimbola]

Hi there,

Please use below formula in Cell A1 of Sheet 1 and copy it down and across.

=IFERROR(INDIRECT("Sheet"&(ROW()+1)&"!A"&COLUMN()),"")

This will populate sheet 2 First Column to sheet 1 First Row , Sheet 3 First column to sheet 1 Second row and so on...

Hope this is what you wanted.

Thanks
Swapnil Shah

That works, I had to add in a single quote though. Thanks.

=IFERROR(INDIRECT(" ' Sheet"&(ROW()+1)&" ' !A"&COLUMN()),"")


*ignore the spaces before/after the single quote I just added those so you can see where I put the quote.