Help in mapping Excel Row Data into Columns by Formula

TCM1960

New Member
Joined
Aug 4, 2010
Messages
1
Hello all,

Having used Excel for many years now, I continue to come across a 'new' user challenge and this particular one has me a bit baffled.

I have a user who is using a spreadsheet whereby each row of data contains a specific customer / job. For each row, there are several columns (labeled, "Bill Cycle #1", "Bill Cycle #2", etc) where a billing cycle date is calculated for that job. The billing date(s) are tied to the start of each project (and, thus, each customer (row) in the column for "Bill Cycle #1" may be different.

Our challenge is trying to "Match" this data to a sheet which has the corresponding Week for that customer billing (and the bill amount is pulled into the corresponding column). The approach 'so far' has been to use a set of 'nested IF' statements whereby each Bill Cycle Date for a customer is checked against the WEEK columns (using Logical Operands to determine if the bill date falls within a specific week). Besides being a nasty "nested IF", the user is on Excel 2003 and is restricted to only 7 nested statements. Since a customer may have an open balance for a number of months (and billing is done biweekly), we end up being restricted in using the IF statements to compare individual bill cycle dates to corresponding Week columns.

The users aren't VBA savy and I'm nervous about taking a macro-approach. However, the nested IF won't get us there, due to being limited to 7 nested IFs. . . . Any ideas on a formulaic approach?
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

iliace

Well-known Member
Joined
Jan 15, 2008
Messages
3,546
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
Can you post an example of each (1) your customer billing sheet, and (2) the manner in which customer/job are identified? Is customer identifiable from job, i.e. each job number is unique between all customers?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,742
Messages
5,833,434
Members
430,209
Latest member
addms

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top