# Create reference formula

#### GrahamS

##### New Member
Here is a screenshot of the spreadsheet i am working with:

https://ibb.co/3smrHPb

I have cells B2, e2, H2 etc calculating % of target achievement that calculates % of target achievement so B2 cell formula is =b24/b4 for sales day 18. Each day i have to manually adjust the formula to reflect the changing day so for day 19 i will have to change the formula to b25/b4. Looking for a formula that would automate this process referencing the sales day in cell A3.

Help greatly appreciated.

Thanks

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### Special-K99

##### Well-known Member
Re: How to create reference forula

Try

=LOOKUP(2,1/(B7:B1000<>""),B7:B1000)/B4

#### GrahamS

##### New Member
Re: How to create reference forula

Try

=LOOKUP(2,1/(B7:B1000<>""),B7:B1000)/B4
I get #VALUE ! error

#### Special-K99

##### Well-known Member
Re: How to create reference forula

Then the values in column B are not numbers or you have spaces in that column

Delete the values from column B7:B1000
Type a few numbers into B7 going down, only a few cells.

The formula should now week, proving the previous values were not numbers or you had spaces in the blank cells.

Last edited:

#### GrahamS

##### New Member
Re: How to create reference forula

Then the values in column B are not numbers or you have spaces in that column

Delete the values from column B7:B1000
Type a few numbers into B7 going down, only a few cells.

The formula should now week, proving the previous values were not numbers or you had spaces in the blank cells.
Column B was formatted as currency

#### Special-K99

##### Well-known Member
Re: How to create reference forula

Did you do what I said (as a test)?

1. Delete ALL the values in B7:B10
2. Enter the numbers 1 to 4 in in B7:B10
3. in B2
=LOOKUP(2,1/(B7:10<>""),B7:B10)/B4

If the #VALUE error disappears then the data you had earlier is causing the error, not the formula.

If this still doesn't work then upload the file to a storage site and post a link to it, make sure the data is does not contain sensitive information.

Last edited:

#### nardagus

##### Active Member
Re: How to create reference forula

Hello,

Maybe this will work?

=INDEX(B\$7:B\$40,MATCH(\$A\$3,\$A\$7:\$A\$40,0))/B\$4

Just place it in B2. Then you can copy it to E2, H2 and so on. Address of Mid % Target will be updated automatically.
When you change the day in A3 it will automatically find right value to divide by B4, E4 and so on.

Cheers

#### GrahamS

##### New Member
Re: How to create reference forula

Hello,

Maybe this will work?

=INDEX(B\$7:B\$40,MATCH(\$A\$3,\$A\$7:\$A\$40,0))/B\$4

Just place it in B2. Then you can copy it to E2, H2 and so on. Address of Mid % Target will be updated automatically.
When you change the day in A3 it will automatically find right value to divide by B4, E4 and so on.

Cheers
This worked! Thank you!
Curious why you specified range from 7 to 40? The sheet stops at 28 is there a particular reason?

#### nardagus

##### Active Member
Re: How to create reference forula

You're welcome and thanks for feedback

Last edited:

#### GrahamS

##### New Member
Re: How to create reference forula

You're welcome and thanks for feedback
Is it possible to create a drop down menu for days from 1-22 for cell A3?