Good day!
I am having quite a bit of a run in with this formula. I think I have it down but the information that is being pulled is the wrong information.
Background:
I am making a letter on excel for people, that will have information presented at the bottom of the letter, that is pertinent to each person. Each person has an ID number which I have added to the excel sheet for this purpose. I do not want to rid of this ID number because at the top of the letter I have utilized the Vlookup tool to lookup each persons contact information so it is AUTO filled and prepped per an address table I have in another excel (over 3000 addresses...so this number is important).
Where I am running into the wall is when I am trying to insert the list of items needed on the letter from a different excel sheet.
My letter workbook is called "Workup". Where my rows of information is, is called "Combined".
My combined worksheet is updated weekly and can range from 100 items to about 350. Every week is different so this will also need to be accounted for. (I have tried to lock the formula in to read just the max that I can have at a time in which it is 350)
Letter Worksheet -
My row of information I would like to pull over starts on cell A37. I usually have at most 15 items per person to as little as 1 item per person.
The value (persons unique number) I need to lookup is in cell M8
I have a count formula to count how many times the persons ID shows up and this is located in cell M12.
(M8 is my unique number per person and is also used as the Vlookup tool for an address book for the letter head)
Combined Worksheet - My headings start on A2 and my data begins on A3 and stretches across to J3. Column J has the unique number but only columns A-F need to be recorded on the Workup sheet.
The formula that I have attempted and can not get working right is
=IF(ROWS(A$32:A32)<=$M$12,INDEX(COMBINED!A$3:A$350,SMALL(IF(COMBINED!$J$3:$J$350=$M$8,ROW(COMBINED!$A$3:$A$350)-ROW(COMBINED!$A$3)+1),ROWS(A$32:A32))),"")
What is happening is on the combined page it is pulls up only items from A32 and over and when I copy down I get the #NUM!.
Where I think I go wrong is in the last part, ROW(COMBINED!$A$3:$A$350)-ROW(COMBINED!$A$3)+1). I have to account for values changing weekly in this excel and can not add a week by week. the information in the Combined sheet is forever rotating and can actually exceed 350 but (I pray it never does) but it should not exceed this amount or I am in trouble.
I have tried and repeated in error and am asking the group to aid on this one. I may be missing or overlooking something rather simple. I can not provide to much but can give examples if needed. I hope I painted a clear picture.
Thank you for your help,
~Tyrel
I am having quite a bit of a run in with this formula. I think I have it down but the information that is being pulled is the wrong information.
Background:
I am making a letter on excel for people, that will have information presented at the bottom of the letter, that is pertinent to each person. Each person has an ID number which I have added to the excel sheet for this purpose. I do not want to rid of this ID number because at the top of the letter I have utilized the Vlookup tool to lookup each persons contact information so it is AUTO filled and prepped per an address table I have in another excel (over 3000 addresses...so this number is important).
Where I am running into the wall is when I am trying to insert the list of items needed on the letter from a different excel sheet.
My letter workbook is called "Workup". Where my rows of information is, is called "Combined".
My combined worksheet is updated weekly and can range from 100 items to about 350. Every week is different so this will also need to be accounted for. (I have tried to lock the formula in to read just the max that I can have at a time in which it is 350)
Letter Worksheet -
My row of information I would like to pull over starts on cell A37. I usually have at most 15 items per person to as little as 1 item per person.
The value (persons unique number) I need to lookup is in cell M8
I have a count formula to count how many times the persons ID shows up and this is located in cell M12.
(M8 is my unique number per person and is also used as the Vlookup tool for an address book for the letter head)
Combined Worksheet - My headings start on A2 and my data begins on A3 and stretches across to J3. Column J has the unique number but only columns A-F need to be recorded on the Workup sheet.
The formula that I have attempted and can not get working right is
=IF(ROWS(A$32:A32)<=$M$12,INDEX(COMBINED!A$3:A$350,SMALL(IF(COMBINED!$J$3:$J$350=$M$8,ROW(COMBINED!$A$3:$A$350)-ROW(COMBINED!$A$3)+1),ROWS(A$32:A32))),"")
What is happening is on the combined page it is pulls up only items from A32 and over and when I copy down I get the #NUM!.
Where I think I go wrong is in the last part, ROW(COMBINED!$A$3:$A$350)-ROW(COMBINED!$A$3)+1). I have to account for values changing weekly in this excel and can not add a week by week. the information in the Combined sheet is forever rotating and can actually exceed 350 but (I pray it never does) but it should not exceed this amount or I am in trouble.
I have tried and repeated in error and am asking the group to aid on this one. I may be missing or overlooking something rather simple. I can not provide to much but can give examples if needed. I hope I painted a clear picture.
Thank you for your help,
~Tyrel
