Excel - Relative & Absolute References

[suzreid]

This is a bit difficult to explain so bear with me please.

I have a workbook that users enter data in many sheets and there is a hidden sheet that consolidates all the data into a single sheet. All the sheets all have the same format.

Sheet1 is the data entry sheet and Sheet2 is the consolidation sheet

In Sheet1, Cell1 contains a title (called Title for example) and cell2 contains a number.

On Sheet2 Cell1 there is a reference to Sheet1 Cell1 and therefore shows the title

In order to collate the correct numbers together on Sheet2 I need to use the row function based on where the title is on Sheet 1.

Because all the workings are on Sheet 2 I was hoping that because Sheet2 Cell1 contained the reference to Sheet 1 Cell1 I would be able to use Sheet 2 Cell1 as the starting point and use the row function there – but that it would somehow know that it was in fact referencing Sheet1 Cell1.

Is there any way that I can use references to Sheet1 in many places in Sheet2 without having to enter direct references throughout Sheet2 i.e. If I use a cell in Sheet 2 as a reference and that cell is referencing Sheet1, will the results be based on Sheet 2 or Sheet 1?

 

[KenWright]

I think you are referring to using the INDIRECT function, which allows you to build a refrence from a text string, eg:-

Sheet1 is named Sheet1 and contains say the number 6 in cell A3.

On sheet2, if you put the text Sheet1 into cell A1, and then in any other cell on sheet2 you use

=INDIRECT(A1&"!A3")

you will get 5 returned.

You can then incorporate the ROW function within the formula to build part of the string.

Note though, that if the sheet name has any spaces in it, then it needs to be enclosed in single quotes in the refrence, ie assuming it had been sheet 1 instead of sheet1, then the formula would have had to have been

=INDIRECT("'"&A1&"'!A1")

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[prinand]

if sheet 1: A1=a a2=b a3=c
now in sheet 2 : A1 press = and then click on A1 in sheet 1.

now sheet2 A1 displays the contents of Sheet1 A1

is you now select sheet2 A1 and use the drag (click and hold the small square at the bootm right corner of the cell, and drag it down, cell A2 and A3 will automaticly also refer to the cells on sheet 1

but you always first have to reference the top left cell of a block that you want to refer to, and then you can use the drag to select the block from sheet1.

hope that this is what you are trying to ask ?