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:

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

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

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

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