Help with a lookup function within a formula?

**Janbi** · 02-23-2015, 01:58 PM

So I've been working on a new rota and it is mostly complete however I want to make it a little bit better.

As you can see on the test file I have uploaded, C6:AG6 contains the formula. AI6 / AJ6 / AK6 contains the reference for said formula.

I want to change it so that it looks at A6 for the name, then references the relevant cells next to the name in the options sheet.

Hope this makes sense, any questions please ask.

**Janbi** · 02-23-2015, 02:54 PM

Anyone got an idea?

**FlameRetired** · 02-23-2015, 04:38 PM

I want to change it so that it looks at A6 for the name, then references the relevant cells next to the name in the options sheet.

You have 3 columns of names ...... next to each other. I am guessing you want the corresponding date(s) from column F

to show up in cell AI6 of the January sheet? If I have that part right what do you want to do about TONY MADDERN?

There are no dates (or months) in that row.

By the way. Consider modifying your formula in C6 to this

Formula:

Please Login or Register  to view this content.

then copying across and down to AG7. This way you only have to write formula once and the relative/absolute cell references take care of the rest of the cells as you fill down and across.

Beyond that I'm in the dark.