View Full Version : Solved: Replace lookup formula
jwilder1
10-14-2008, 01:23 AM
The company I work for uses an accounting system that has
Period 1- 4 weeks,
Period 2- 4 weeks,
Period 3- 5 weeks, this pattern repeats through 52 weeks(12 periods).
I currently use a vlookup formula, but would like to replace this. I tried this but it fails after period 3
=IF(OR(MOD(A2,13)=0,MOD(A2,13)>8),3*ROUNDUP(A2/13,0),ROUNDUP(A2/4,0)) where A2 is the current week. Any help will be appreciated. Jim
Simon Lloyd
10-14-2008, 01:28 AM
The company....... 52 weeks(12 periods).
I currently use a vlookup formula, but would like to replace this. ..... A2 is the current week. Any help will be appreciated. JimJim you say you would like to replace it, for what?, why? what are you trying to achieve or what would you like to see?
jwilder1
10-14-2008, 01:47 AM
What: Replace the lookup formula with a formula that doesn't require a table.
Why:The lookup table exists in an another workbook, and as I update workbooks, I email out to 50 locations. At some of these locations, someone will open the attachment rather than save to the correct folder, which causes the workbook to link to a temp file.
I'm sorry that I wasn't clear enough with the first post. Jim
Bob Phillips
10-14-2008, 05:24 AM
Are you saying that you want to replace the table in column A? If so, what determines the week that a date is within? What is being looked up against this table?
jwilder1
10-14-2008, 10:47 AM
Thats right I want to replace the table in columns A & B, the week is manually chosen and incremented by weekly reset programming. Thanks, Jim
Bob Phillips
10-14-2008, 10:56 AM
Still not getting it. Manually chosen from where? I can't see any lookup in the workbook that you posted.
georgiboy
10-14-2008, 11:00 AM
Can you post a workbook with how you would like it to look, im confused.
mdmackillop
10-14-2008, 12:04 PM
=INT((MOD(A2-1,13)+0.1)/4.05)+INT((A2-1)/13)*3+1
jwilder1
10-14-2008, 01:15 PM
Thanks MD, that formula was exactly what I wanted. Sorry about all the confusion, that attachment was a stripped down workbook, showing only the table info, not the 15 workbooks that used the table. Thanks again, Jim
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.