Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm trying to forecast increasing vendor costs in a spreadsheet.

My sheet looks something like this

col     | A         | B         | C         | D         | E         | F
vendor  | 2013-09   | 2013-10   | 2013-11   | 2013-12   | 2014-01   | 2014-02
inet    | 50        | 50        | 50        | 60        | 60        | 60
scm (yr)| 300       |                                   | 400       |
  - (mn)| =(300/12) | =(300/12) | =(300/12) | =(300/12) | =(400/12) | =(400/12) 

The 'scm' column shows the Source Control vendor's yearly cost (yr) and then monthly cost, which references the yearly cost's column and then divides it by 12. [ ie , "($A,$scm(yr))/12"

The annoying part is when I have a new cost come in -- as in Janu 2014 -- and I have to update the formulas.

Is there a command that would let me reference "the last value in row X , before or equal to the current column"? this way I could use a single formula for an entire row ( like the monthly 'scm' costs ), and just shift numbers around in the yearly row , without having to recode formulas. For example, in relation to the 'scm' row, the value in cols A,B,C,D would be 300 ; while the value in cols E,F would be 400 ?

share|improve this question

2 Answers 2

up vote 1 down vote accepted

You can use LOOKUP() as well:

=LOOKUP(9.99E207, $B$3:B$3)
                       ^----- Column formula is inputted
                  ^--^------- Starting cell

In cell B4 and drag to the right.

enter image description here

share|improve this answer
    
Selected because this is simpler. Both EXCELLENT answers that have ended years of wonder! –  Jonathan Vanasco Jul 9 '13 at 14:23

Starting in column B, any row,

=INDEX(3:3,MATCH(99^99,$A$3:B$3,1))

copy across. Integrate into a formula as required.

See screenshot. Note that the sample file for the screenshot has the labels in column A, so the values start in column B. Adjust as needed.

enter image description here

share|improve this answer
    
thank you! i've wanted to know how to do this forever! –  Jonathan Vanasco Jul 9 '13 at 14:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.