MS Excel help

[boybi]

I 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?

[jut703]

Index 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).

[boybi]

Thank you! It worked!

[african888]

pwede rin ba index sa vertical list?

[jut703]

pwede rin ba index sa vertical list?

Yup. Just select the correct arrays

[Yuki13]

Help 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

[Yuki13]

Help 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

Up! I have 1850 cells to fill in!

[jut703]

Your 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:

[Yuki13]

^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.

[jut703]

No prob bro. Glad to know you solved it.