Indirect formula question

[StanSz]

I'm trying to use a formula on one worksheet that gets data from another worksheet that has an INDIRECT formula and I can't get my formula to work.

On the worksheet 'Results' I'm trying to get the data from the last entry in column G on the 'Prices' worksheet.

The last row of the 'Prices' worksheet will change as days of data are added. I keep track of the last row of data in cell A1 of 'Prices'.

If I was just trying to get the last cell of data on the same 'Prices' worksheet I would use =INDIRECT("G"&A1) which works and gives me the answer I want.

However, I'm trying to do this across 2 different worksheets -- I'm on the "Results" sheet and I want to use a formula to get the answer that uses the INDIRECT formula from the "Prices' sheet.

I've tried =INDIRECT("'Prices''"&"G"&A1) and other variations and I get a REF# error.

Thanks for your help

 

[steve the fish]

If you use a sheet name in a formula you will see an exclamation mark so similarly you need one in the INDIRECT. See if this works:

=INDIRECT("Prices!G"&A1)

 

[Marcelo Branco]

Try

=INDIRECT("Prices!G"&Prices!A1)

M.

 

[Jonmo1]

Perhaps without the volatile Indirect function..

=INDEX(Prices!G:G,Prices!A1)

 

[StanSz]

Try

=INDIRECT("Prices!G"&Prices!A1)

M.

The =INDIRECT("Prices!G"&Prices!A1) did work -- Thank you.

The other formula =INDIRECT("Prices!G"&A1) did not work and continued to give the REF# error.

Thanks to you both.

 

[njimack]

You could also do this:
=INDIRECT(ADDRESS(A1,7,,,"Prices"))

 

[Marcelo Branco]

The =INDIRECT("Prices!G"&Prices!A1) did work -- Thank you.

You are welcome.
See the formula suggested by Jonmo1 in post 4 - it's a better solution.

M.

 

[StanSz]

You are welcome.
See the formula suggested by Jonmo1 in post 4 - it's a better solution.

M.

Got it -- it also 'works' and appears more "elegant"

Thanks to all.

Stan

 

[Jonmo1]

You're welcome.