Excel Dynamic Column Reference in Formula

Question

I want to update the cell reference in C3 depending on the 'Current Day'.

For example, if 'Current Day' is 'Thu' then I'd like C3 to be 'Z3'

Any suggestions to get me started? webpages to read?

Use index/match or hlookup to find the cell address (Z3), then indirect to get the value (34.2) — Tom Sharpe
– Tom Sharpe, Commented May 16, 2017 at 10:31

Mrig · Accepted Answer · 2017-05-16 10:35:23Z

3

To get the value on the basis of Current Day use the following formula:

=HLOOKUP(H3,$I$2:$O$3,2,FALSE)

See image for reference

On seeing the formula in image attached probably you are looking for INDIRECT function:

=INDIRECT("'5+ 000""s By Day'!" & C3)

You can combine both in one formula as

=INDIRECT("'5+ 000""s By Day'!" & HLOOKUP(H3,$I$2:$O$3,2,FALSE))

For details on INDIRECT function see this.

answered May 16, 2017 at 10:35

Mrig

11.7k2 gold badges15 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Boosted_d16 Over a year ago

Thanks for going the extra mile by doing the INDIRECT formula

hamish Over a year ago

thanks for the INDIRECT function, that was exactly what I needed to solve my issue. google led me here. allowed me to pull up a value from another cell based on the string version of the cell location. cheers! =INDIRECT("'worksheet1'!R$1")

Jeremy · Accepted Answer · 2017-05-16 10:34:13Z

1

A hlookup() would do it - =HLOOKUP(TEXT(TODAY(),"DDD"),I2:O3,2,0)

answered May 16, 2017 at 10:34

Jeremy

1,3373 gold badges13 silver badges28 bronze badges

Collectives™ on Stack Overflow

Excel Dynamic Column Reference in Formula

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related