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

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

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?