Create reference formula

[GrahamS]

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

 

[Special-K99]

Re: How to create reference forula

Try

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

 

[GrahamS]

Re: How to create reference forula

Try

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

I get #VALUE ! error

 

[Special-K99]

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.

 

[GrahamS]

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]

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.

 

[nardagus]

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]

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]

Re: How to create reference forula

Nah. My bad.
You're welcome and thanks for feedback

 

[GrahamS]

Re: How to create reference forula

Nah. My bad.
You're welcome and thanks for feedback

Is it possible to create a drop down menu for days from 1-22 for cell A3?