Create reference formula

GrahamS

New Member
Joined
Oct 25, 2019
Messages
6
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

Well-known Member
Joined
Nov 7, 2006
Messages
8,301
Re: How to create reference forula

Try

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

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,301
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
Joined
Oct 25, 2019
Messages
6
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
Joined
Nov 7, 2006
Messages
8,301
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
Joined
Apr 23, 2012
Messages
276
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
Joined
Oct 25, 2019
Messages
6
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
Joined
Apr 23, 2012
Messages
276
Re: How to create reference forula

Nah. My bad. :)
You're welcome and thanks for feedback :)
 
Last edited:

Forum statistics

Threads
1,077,784
Messages
5,336,332
Members
399,076
Latest member
vullistax

Some videos you may like

This Week's Hot Topics

Top