Cell input number as row number in function between sheets

**jblewis** · 11-01-2010, 10:27 AM

Hello,

I am trying to use a input cell to average a row from the input number down. So if i put a number in my input box I would like my average function to use that row number that was imputed to the end of the data.

I can not get it reference another excel sheet it gives me #REF! error when i try to use column L from sheet "XRF Data Paste Here"

What is wrong

Where H17 is the input cell and " is the name of the sheet that data is on. with this code i am trying to pull data from column L

=AVERAGE(INDIRECT("'XRF Data Paste Here'!L"&H17&":'XRF Data Paste Here'!L"))

**TMS** · 11-01-2010, 10:39 AM

I think it needs to be something like:

Please Login or Register  to view this content.

Take out the second sheet reference and include the end cell, assuming the value in H17 is the start cell. In this case, I added a 35 to complete the range

Regards

**Richard Buttrey** · 11-01-2010, 10:44 AM

Hi,

Try

Please Login or Register  to view this content.

Change the last row number (1000) as appropriate.

HTH

**jblewis** · 11-30-2010, 09:47 AM

Works great guys thanks. Now i thinking of taking it to the next level. I am not to good at writing code.

I am thinking of doing a macro that will search a column M for a code SC (stable conditions), look at the time stamp in column N. Then find that time or before on Sheet XRF Data Paste here and export that row number to a specific cell.

Thanks for the help