• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Production Schedule Breakouts

M

mbeane

New Member
Okay, you amazed me, yesterday; here's another. Multiple worksheets. Column headings vary in column placement among the sheets. I need to find data based on a job number. I enter the alphanumeric job number into cell c3 (example) on a master spread sheet. The formula needs to search across five 'feeder' worksheets, match the number, copy corresponding data from neighboring cells of the 'feeder' sheet with the number, and populate corresponding row data to the master spreadsheet. If the number is not found, "Not Found" is printed in the formula's cell. I've tried copying [=(cellcontents)] data from the five 'feeder' sheets to one sheet and tried sumproduct, vlookup, and index match formulas, but the results aren't consistent because the five sheets are updated daily which, somehow, adversely impacts the formulas and returns a #N/A error. I need a surefire fix. Also, is there any way I can be prompted when my question is responded to? Thanks, in advance...and probably afterward, too, but wanted to throw that in :<) !
 
mbeane

1. Are the columns in fixed locations on each of the five sheets?

2. Is there only 1 entry corresponding with C3 from each sheet or multiple ?

3. When you say adjacent cells, I assume you mean in the same row ?

4. Is the master sheet broken up into different areas based on the data from each sheet ?

5. Are you able to post the spreadsheet somewhere (randomise/anonymize the data) for us to review

It can be done, but the above will help alot
 
1. First few columns are the same for all feeder sheets. After D, columns are not in fixed locations. Due date for one customer may be in M, but may be in J for the next customer, based on specific customer needs. However, column C always contains job number.

2. Only one entry. Example: Job 12345 will only appear once among the customers (feeder sheets).

3. Same row. Finding job 12345 in column C, row 18 of customer ABC, I want to copy the information from adjacent cells of row 18 into their destination cells.

4. Master sheet is not divided. Basically, I'm creating job schedules for different work centers, each with their own 'master sheet'. I used the singular reference to simplify my request.

5. Data is proprietary by customer request. Wish I could, would simplify things.

6. Again, you guys amaze me at the rapidity of your response!
 
Follow-up Example:

Customer Feeder Sheet #1, Columns A through D, enter the following data.

Row 1: ABC, 4/5/10, 12345, 4A123

Row 2: BCD, 4/8/10, 23456, 4B123

Customer Feeder Sheet #2, Columns A through D, enter the following data.

Row 1: XYZ, 4/15/10, 34567, 4C123

Row 2: CDE, 4/18/10, 45678, 4D123

Master Sheet: When I enter "23456" in column C of row 1, I need to search all feeder sheets and extrapolate the following to their destination cells.

Column A: "BCD"

Column B: "4/8/10"

Column D: "4B123"
 
Do the sheets have consistant & regular names or do they change from job to job ?

Do you want to look up more than 1 job at a time ?


If the above Q's are Yes, you will probably need a macro to do the looking up for you


If the sheet names are consistant, it could be done without macro's, but will be difficult to advise with limited data
 
Sheet names are consistent. Only job data changes within each feeder sheet. Changes are dynamic and occur two or more times a day, depending on job completion status. (We're trying to provide near real-time data to our production leads.) Regrettably, I can spell "VBA", but cannot program in it or any other current language. Only real data that changes is the job number and its related row data. Thanks for your patience.
 
I've discovered a follow-on problem. When my job numbers break into the 20000 and above, range, Excel will not recognize them. Jobs numbered 19999 and below work in the formulas I've tried, but injected the number 20000, 20001, and above don't. All cells formatted the same. No differences in formulas between those that work and the 20k formulas. Am I unique or is this an MS issue I'm not aware of?
 
You must log in or register to reply here.
Back
Top