INDIRECT (I Think)

Chris26

New Member
Joined
Feb 18, 2010
Messages
10
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-com:eek:ffice:eek:ffice" /><o:p></o:p>
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:p></o:p>
Just text i.e. Either ON or OFF<o:p></o:p>
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:p> </o:p>
Sorry if this a bit long, apologies if I confused you (confused myself i think !!!:confused:), and many thanks in advance for replies.
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,705
Office Version
2019
Platform
Windows
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

Well-known Member
Joined
Mar 16, 2009
Messages
1,123
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
RS
243A1
254A2
265A3
276A4
comp prob
Excel 2007
Cell Formulas
RangeFormula
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

New Member
Joined
Feb 18, 2010
Messages
10
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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,922
Messages
5,514,193
Members
408,989
Latest member
tommo1949

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top