Results 1 to 10 of 10
Hybrid View
-
Tsikot Member Rank 8
- Join Date
- Oct 2002
- Posts
- 21,373
November 20th, 2014 12:15 PM #1I have an excel workbook with several worksheets. In one of the worksheets, I have the following format:
-----A------B------C-----D------E
1 XXXX BBBB CCCC DDDD EEEE
2 Jan 1 Jan 2 Jan 3 Jan 4 Jan 5
3
4
5
What formula should I use to get the value in row 1 corresponding to a particular date in row 2?Signature
-
Dalek
- Join Date
- Dec 2006
- Posts
- 17,314
November 20th, 2014 12:27 PM #2Index Match pwede.
=index(A1:E1, match(reference cell, A2:E2))
When you type Jan 3 in your reference cell, it'll return CCCC. Pwede rin siya baliktad just switch the range of the index and match arrays (i.e. type BBBB tapos yung result is Jan 2).
-
Tsikot Member Rank 8
- Join Date
- Oct 2002
- Posts
- 21,373
-
Verified Tsikot Member
- Join Date
- Oct 2008
- Posts
- 380
-
Dalek
- Join Date
- Dec 2006
- Posts
- 17,314
-
Tsikoteer
- Join Date
- Nov 2013
- Posts
- 2,077
June 19th, 2015 07:07 PM #6Help guys.
Trying to drag down this formula ='Deal 01'!C6
Result I need is this ='Deal 02'!C6
But when I drag I'm getting this ='Deal 01'!C7
-
Tsikoteer
- Join Date
- Nov 2013
- Posts
- 2,077
-
Dalek
- Join Date
- Dec 2006
- Posts
- 17,314
June 19th, 2015 08:34 PM #8Your dragging really just advances cells, not reference sheets.
Here's what I would do:
1. Make a separate column that has Deal 01 in the first row. Drag it down such that the next rows will be Deal 02, Deal 03, and so on until Deal 1850.
2. On the adjacent cell (if string Deal 01 is in cell A1, then type in B1), use the formula, =INDIRECT("'"&A1&"'!C7")
Just tried it and it works. The text "Value in Deal 01 C7" is what I put in cell C7 of sheet Deal 01 and it was able to reference it properly:
-
Tsikoteer
- Join Date
- Nov 2013
- Posts
- 2,077
June 22nd, 2015 10:56 AM #9^thanks jut. appreciate the effort, i vaguely remember you posting that you came from a long drive last Friday.
I took an easier route, adding columns would have been a pain. I failed to mention that I already had 74 columns to begin with. Wanted to drag down into 25 rows, each with different reference sheets.
Here's what I did:
1. Locked the reference cell for each of the 74 columns.
2. Copied/dragged to next row.
3. Higlighted new row. Used find and replace all to change the reference sheet for the whole row - Repeated 25 times.
-
Dalek
- Join Date
- Dec 2006
- Posts
- 17,314
Reply With Quote
Originally Posted by african888
