Referencing values from another sheet - relative

[vispi]

Hello

I suppose that there are already some questions about referencing values from cells that are on another worksheet. However, I have not been able to find an answer to my question, so any suggestion will be very appreciated.

I will try to explain my situation as best as I can:

I need to complete several cells with values from several sheets. I have a summary sheet with the names of the other sheets, so what I do is applying the following function:

Please Login or Register  to view this content.

This way, I get the value from Summary!A6 that is the name of another sheet, and then the value from cell B4 on that sheet. That is, suppose I have the value "Sheet1" in sheet "Summary", cell A6. With that function I would get the value from Sheet1, cell B4.

My problem is, quotation marks around !B4 force that value, so when I copy the cell on the cell below, ir remains B4 and does not change to B5 as it would do if it was a relative reference.

I need this part of the function to be a relative reference, so I can easily copy and paste the content of the cell to the cells around and have the corresponding values automatically, without me having to change B4 into B5 or C4 when needed. I have tried a lot of quotation marks combinations, but I am not able to do it.

Would you know any way to do this? Maybe I have some concept error, but please forgive me as I am very new to excel.

I hope I have been able to explain the issue, please ask if anything is not clear.

Thank you very much in advance for your help

[DonkeyOte]

Welcome to the Board.

It would help if we knew in which cell the formula you provided is placed ?
(You can use that to help you create the dynamic cell reference you require)

[DonkeyOte]

If we assume B6 (to reference B4 on appropriate sheet) then:

Please Login or Register  to view this content.

[vispi]

Thanks for your answer.

If don't misunderstand you, for example, the function I have put:

Please Login or Register  to view this content.

is placed in cell B6 in a third sheet named for example Sheet2

[DonkeyOte]

OK, see my prior post which should do what you want.

[vispi]

Wow, you are so wise! Thank you very much, it works perfectly, I should have written in the forum much earlier! I have lost so much time... ^_^ (well, I think I have learned some excel tips though )

Thanks again!

PS: Just a thing, in excel 2003 we need a ; to separate rows and columns in address function

[DonkeyOte]

De nada

Note: the ; is required as a result of your regional settings rather than as a result of the XL version you are running.

[vispi]

Oh, ok, thanks again