INDIRECT (I Think)

[Chris26]

Hi, looking for a bit of help with Excel. With my limited knowledge I think I need to use the Indirect command. Just not sure how. I am using Excel 2007.
In my SHEET I have Pump Data in the following format. There could be weeks of data so runs to quite a few lines in the excel sheet.
Col A<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Dd/mm/yyyy hh:mm
This is a date/time reading for whenever there is a change of state in a pump i.e On/Off
Col B<o></o>
Just text i.e. Either ON or OFF<o></o>
I have set up in same SHEET a Table for checking the pump performance etc. I would like to be able to enter the CELL in Column A that my Table starts reading at. For example if my data in Col A runs from CELL A1 to CELL A500 I would like to be able to input say CELL A150 as the starting point in the table. I want this manually inputted CELL value to be changeable and not fixed
If I use CELL C1 as my input and enter A150
In the first line of my Table (CELL D1) I enter =INDIRECT (C1), this works fine and CELL D1 in Table displays the Value for the relevant CELL in COLUMN A that was entered in C1.
But How do I then extend this down my table so that the next line in my table D2 reads value of CELL A151, then D3 reads value of CELL A152 etc.
<o> </o>
Sorry if this a bit long, apologies if I confused you (confused myself i think !!!), and many thanks in advance for replies.

 

[jasonb75]

Try entering only the row in C1 (i.e. 150 not A150) then in D1

=INDIRECT("C"&C1+(ROW()-1))

Or, as you say that column A contains dates, would looking for the date intered in C1 be more practical than entering the cell address?

If it would try

=index($B$1:$B$200,(Row(a1)-1)+match($C$1,$A$1:$A$200,0))

 

[snowblizz]

This is a small example of something you might do. It only inputs the row number having the letter fixed.
It requires and extra column, R which lists the row numbers to be referred to. There might be a way to do without, but someone more clever than me will have to do that.

Excel Workbook
	R	S
24	3	A1
25	4	A2
26	5	A3
27	6	A4
comp prob

Excel 2007

Cell Formulas
Range	Formula
S24	=INDIRECT("A" & R24)
S25	=INDIRECT("A" & R25)
S26	=INDIRECT("A" & R26)
S27	=INDIRECT("A" & R27)
R25	=R24+1
R26	=R25+1
R27	=R26+1

 

[Chris26]

Thanks for taking the time to help out

I took your advice Jason and used the Index formula. Had to manipulate it a bit to get what I wanted, but got there in the end.


Many Thanks