• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Inserting a value in a formula

ericavil

New Member
I feel like this should be an easy question but I can't figure it out.

I am using an INDEX formula to get a value from another spreadsheet. Sometimes the value is found on Row "17", other times on row "31" or "52', etc.

an example of the formula is:

INDEX('[FY 2019 Hour Allocations.xlsx]Q2 - Alloc'!$I$17:$BK$17,,MATCH($A8,'[FY 2019 VLA Hour Allocations.xlsx]Q2 - Alloc'!$I$4:$BK$4,0))

I use this formula, or a variant about 80 time on each of 40 sheets. I would like to lookup the "17" in the formula, from a single cell at the top of the sheet (or perhaps another sheet). The best I could come up with is pretty inelegant: CONCATENATE("'[FY 2019 VLA Hour Allocations.xlsx]",INDIRECT("k7"),"'!","$I$",INDIRECT("M1"),":$bk$",INDIRECT("M1")), where "k7" has the name of referenced sheet, and "M1" has the row number.

Is there a way to replace the "17" with a cell reference?

Thanks
 
You can use the Offset() function

Offset(INDEX('[FY 2019 Hour Allocations.xlsx]Q2 - Alloc'!$I$17:$BK$17, Choose(A1,0,14,35),0),,MATCH($A8,'[FY 2019 VLA Hour Allocations.xlsx]Q2 - Alloc'!$I$4:$BK$4,0))

in the above Offset take the range INDEX('[FY 2019 Hour Allocations.xlsx]Q2 - Alloc'!$I$17:$BK$17 as a base range.
It uses Choose() to choose the offset values of 0, 14 and 35 which will offset the range by 0, 16 or 35 rows according to the values of 1, 2 or 3 in cell A1
 
I don't know how you guys get on with direct referencing; for me it is unintelligible! I guess it is my failing; my brain must be wired differently but for the life of me I cannot see the significance of a cell's location.

I did get the formula down to
= INDEX( specifiedRecord, MATCH(selectedValue, heading, 0) )
which sort of made sense.

That left me choosing the 'specifiedRecord' by index
= CHOOSE(select, RecordA, RecordB, RecordC)
or selecting it by record number within a table
= INDEX( TableBody, select, 0 )

Each of these returns a single row, allowing the value to be looked up later.
 
Back
Top