| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need formula to increment cell reference every 8th row
I have a forumla where I'm looking up a Client name and finding all the
contacts that relate to that Client. The most contacts per Client is 8. This formula works great, I just need it to reference R14C1 for 8 rows and then increase to R15C1 for 8 rows, etc. I have 562 unique Clients x 8 =4496 and I'm sure there is a way to Fill Down this formula instead of pasting it 4496 times. Thank you! =VLOOKUP('List of Clients'!R14C1,'List of contacts'!R2C1:R4475C[38],2,FALSE) -- Thank you, scrowley(AT)littleonline.com |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need formula to increment cell reference every 8th row
Have you give R1C1 reference for illustration or is it a real formula?
What you want can be achieved by combining ="R"&INT((ROW()+7)/8)+13&"C1" with INDIRECT The formula above if entered in Row 1 and copied down will give you R14C1 for 14 rows then R15C1 for 14 rows and so on.. If you give your correct VLOOKUP formula then I can build the formula for you "Sandy Crowley" wrote: I have a forumla where I'm looking up a Client name and finding all the contacts that relate to that Client. The most contacts per Client is 8. This formula works great, I just need it to reference R14C1 for 8 rows and then increase to R15C1 for 8 rows, etc. I have 562 unique Clients x 8 =4496 and I'm sure there is a way to Fill Down this formula instead of pasting it 4496 times. Thank you! =VLOOKUP('List of Clients'!R14C1,'List of contacts'!R2C1:R4475C[38],2,FALSE) -- Thank you, scrowley(AT)littleonline.com |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need formula to increment cell reference every 8th row
Sheeloo,
Thanks for your reply. I have Use the R1C1 turned on in my Excel Options. This is the formula without the R1C1: =VLOOKUP('List of Clients'!$A$2,'List of contacts'!$A$2:AM$4475,2,FALSE) Ideally, 8 cells in the same column will have =VLOOKUP('List of Clients'!$A$2,'List of contacts'!$A$2:AM$4475,2,FALSE) and the next 8 cells in the same column will have =VLOOKUP('List of Clients'!$A$3,'List of contacts'!$A$2:AM$4475,2,FALSE) The only thing that changes is $A$2 to $A$3 then $A$4, etc. Does this clarify? -- Thank you, scrowley(AT)littleonline.com "Sheeloo" wrote: Have you give R1C1 reference for illustration or is it a real formula? What you want can be achieved by combining ="R"&INT((ROW()+7)/8)+13&"C1" with INDIRECT The formula above if entered in Row 1 and copied down will give you R14C1 for 14 rows then R15C1 for 14 rows and so on.. If you give your correct VLOOKUP formula then I can build the formula for you "Sandy Crowley" wrote: I have a forumla where I'm looking up a Client name and finding all the contacts that relate to that Client. The most contacts per Client is 8. This formula works great, I just need it to reference R14C1 for 8 rows and then increase to R15C1 for 8 rows, etc. I have 562 unique Clients x 8 =4496 and I'm sure there is a way to Fill Down this formula instead of pasting it 4496 times. Thank you! =VLOOKUP('List of Clients'!R14C1,'List of contacts'!R2C1:R4475C[38],2,FALSE) -- Thank you, scrowley(AT)littleonline.com |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need formula to increment cell reference every 8th row
Yes it does...
One more question... If you use =VLOOKUP('List of Clients'!$A$2,'List of contacts'!$A$2:AM$4475,2,FALSE) 14 times you will get the same value back every time... the first match for A2 Replace 'List of Clients'!R14C1 in your formula with =INDIRECT("'List of Clients'!"&("R"&INT((ROW()+7)/8)+13&"C1")) assuming you start at row1.. "Sandy Crowley" wrote: Sheeloo, Thanks for your reply. I have Use the R1C1 turned on in my Excel Options. This is the formula without the R1C1: =VLOOKUP('List of Clients'!$A$2,'List of contacts'!$A$2:AM$4475,2,FALSE) Ideally, 8 cells in the same column will have =VLOOKUP('List of Clients'!$A$2,'List of contacts'!$A$2:AM$4475,2,FALSE) and the next 8 cells in the same column will have =VLOOKUP('List of Clients'!$A$3,'List of contacts'!$A$2:AM$4475,2,FALSE) The only thing that changes is $A$2 to $A$3 then $A$4, etc. Does this clarify? -- Thank you, scrowley(AT)littleonline.com "Sheeloo" wrote: Have you give R1C1 reference for illustration or is it a real formula? What you want can be achieved by combining ="R"&INT((ROW()+7)/8)+13&"C1" with INDIRECT The formula above if entered in Row 1 and copied down will give you R14C1 for 14 rows then R15C1 for 14 rows and so on.. If you give your correct VLOOKUP formula then I can build the formula for you "Sandy Crowley" wrote: I have a forumla where I'm looking up a Client name and finding all the contacts that relate to that Client. The most contacts per Client is 8. This formula works great, I just need it to reference R14C1 for 8 rows and then increase to R15C1 for 8 rows, etc. I have 562 unique Clients x 8 =4496 and I'm sure there is a way to Fill Down this formula instead of pasting it 4496 times. Thank you! =VLOOKUP('List of Clients'!R14C1,'List of contacts'!R2C1:R4475C[38],2,FALSE) -- Thank you, scrowley(AT)littleonline.com |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need formula to increment cell reference every 8th row
I'm sorry. I just don't get it. I tried:
=VLOOKUP(INDIRECT('List of Clients'!&(R&INT((ROW()+7)/8)+13&C1)),'List of contacts'!R2C1:R4475C[38],2,FALSE) I guess I don't understand INDIRECT very well. Can you step me through the formula you wrote? Thanks -- Thank you, scrowley(AT)littleonline.com "Sheeloo" wrote: Yes it does... One more question... If you use =VLOOKUP('List of Clients'!$A$2,'List of contacts'!$A$2:AM$4475,2,FALSE) 14 times you will get the same value back every time... the first match for A2 Replace 'List of Clients'!R14C1 in your formula with =INDIRECT("'List of Clients'!"&("R"&INT((ROW()+7)/8)+13&"C1")) assuming you start at row1.. "Sandy Crowley" wrote: Sheeloo, Thanks for your reply. I have Use the R1C1 turned on in my Excel Options. This is the formula without the R1C1: =VLOOKUP('List of Clients'!$A$2,'List of contacts'!$A$2:AM$4475,2,FALSE) Ideally, 8 cells in the same column will have =VLOOKUP('List of Clients'!$A$2,'List of contacts'!$A$2:AM$4475,2,FALSE) and the next 8 cells in the same column will have =VLOOKUP('List of Clients'!$A$3,'List of contacts'!$A$2:AM$4475,2,FALSE) The only thing that changes is $A$2 to $A$3 then $A$4, etc. Does this clarify? -- Thank you, scrowley(AT)littleonline.com "Sheeloo" wrote: Have you give R1C1 reference for illustration or is it a real formula? What you want can be achieved by combining ="R"&INT((ROW()+7)/8)+13&"C1" with INDIRECT The formula above if entered in Row 1 and copied down will give you R14C1 for 14 rows then R15C1 for 14 rows and so on.. If you give your correct VLOOKUP formula then I can build the formula for you "Sandy Crowley" wrote: I have a forumla where I'm looking up a Client name and finding all the contacts that relate to that Client. The most contacts per Client is 8. This formula works great, I just need it to reference R14C1 for 8 rows and then increase to R15C1 for 8 rows, etc. I have 562 unique Clients x 8 =4496 and I'm sure there is a way to Fill Down this formula instead of pasting it 4496 times. Thank you! =VLOOKUP('List of Clients'!R14C1,'List of contacts'!R2C1:R4475C[38],2,FALSE) -- Thank you, scrowley(AT)littleonline.com |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need formula to increment cell reference every 8th row
Use
=INDIRECT("'List of Clients'!"&("R"&INT((ROW()+7)/8)+13&"C1")) It basically evaluates INT((ROW()+7)/8)+13 to get a number (14 in row 1) which is then inserted between 'List of Clients'! and C1 to the completed address... Indirect then goes to that cell and gets the value there... You did not put quotes around the sheet name ... Just put the indirect function in a cell and see what you get =INDIRECT("'List of Clients'!"&("R"&INT((ROW()+7)/8)+13&"C1")) also copy it to the right and below to see how it changes... "Sandy Crowley" wrote: I'm sorry. I just don't get it. I tried: =VLOOKUP(INDIRECT('List of Clients'!&(R&INT((ROW()+7)/8)+13&C1)),'List of contacts'!R2C1:R4475C[38],2,FALSE) I guess I don't understand INDIRECT very well. Can you step me through the formula you wrote? Thanks -- Thank you, scrowley(AT)littleonline.com "Sheeloo" wrote: Yes it does... One more question... If you use =VLOOKUP('List of Clients'!$A$2,'List of contacts'!$A$2:AM$4475,2,FALSE) 14 times you will get the same value back every time... the first match for A2 Replace 'List of Clients'!R14C1 in your formula with =INDIRECT("'List of Clients'!"&("R"&INT((ROW()+7)/8)+13&"C1")) assuming you start at row1.. "Sandy Crowley" wrote: Sheeloo, Thanks for your reply. I have Use the R1C1 turned on in my Excel Options. This is the formula without the R1C1: =VLOOKUP('List of Clients'!$A$2,'List of contacts'!$A$2:AM$4475,2,FALSE) Ideally, 8 cells in the same column will have =VLOOKUP('List of Clients'!$A$2,'List of contacts'!$A$2:AM$4475,2,FALSE) and the next 8 cells in the same column will have =VLOOKUP('List of Clients'!$A$3,'List of contacts'!$A$2:AM$4475,2,FALSE) The only thing that changes is $A$2 to $A$3 then $A$4, etc. Does this clarify? -- Thank you, scrowley(AT)littleonline.com "Sheeloo" wrote: Have you give R1C1 reference for illustration or is it a real formula? What you want can be achieved by combining ="R"&INT((ROW()+7)/8)+13&"C1" with INDIRECT The formula above if entered in Row 1 and copied down will give you R14C1 for 14 rows then R15C1 for 14 rows and so on.. If you give your correct VLOOKUP formula then I can build the formula for you "Sandy Crowley" wrote: I have a forumla where I'm looking up a Client name and finding all the contacts that relate to that Client. The most contacts per Client is 8. This formula works great, I just need it to reference R14C1 for 8 rows and then increase to R15C1 for 8 rows, etc. I have 562 unique Clients x 8 =4496 and I'm sure there is a way to Fill Down this formula instead of pasting it 4496 times. Thank you! =VLOOKUP('List of Clients'!R14C1,'List of contacts'!R2C1:R4475C[38],2,FALSE) -- Thank you, scrowley(AT)littleonline.com |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need formula to increment cell reference every 8th row
I copied your formula and removed the double quotes around the sheet name
'List of Clients'! and I get #REF as well as an error message saying the formula I have entered contains an error. ARGGG!!! Sorry for the thick head. Once the light bulb comes on, I'll be good. -- Thank you, scrowley(AT)littleonline.com "Sheeloo" wrote: Use =INDIRECT("'List of Clients'!"&("R"&INT((ROW()+7)/8)+13&"C1")) It basically evaluates INT((ROW()+7)/8)+13 to get a number (14 in row 1) which is then inserted between 'List of Clients'! and C1 to the completed address... Indirect then goes to that cell and gets the value there... You did not put quotes around the sheet name ... Just put the indirect function in a cell and see what you get =INDIRECT("'List of Clients'!"&("R"&INT((ROW()+7)/8)+13&"C1")) also copy it to the right and below to see how it changes... "Sandy Crowley" wrote: I'm sorry. I just don't get it. I tried: =VLOOKUP(INDIRECT('List of Clients'!&(R&INT((ROW()+7)/8)+13&C1)),'List of contacts'!R2C1:R4475C[38],2,FALSE) I guess I don't understand INDIRECT very well. Can you step me through the formula you wrote? Thanks -- Thank you, scrowley(AT)littleonline.com "Sheeloo" wrote: Yes it does... One more question... If you use =VLOOKUP('List of Clients'!$A$2,'List of contacts'!$A$2:AM$4475,2,FALSE) 14 times you will get the same value back every time... the first match for A2 Replace 'List of Clients'!R14C1 in your formula with =INDIRECT("'List of Clients'!"&("R"&INT((ROW()+7)/8)+13&"C1")) assuming you start at row1.. "Sandy Crowley" wrote: Sheeloo, Thanks for your reply. I have Use the R1C1 turned on in my Excel Options. This is the formula without the R1C1: =VLOOKUP('List of Clients'!$A$2,'List of contacts'!$A$2:AM$4475,2,FALSE) Ideally, 8 cells in the same column will have =VLOOKUP('List of Clients'!$A$2,'List of contacts'!$A$2:AM$4475,2,FALSE) and the next 8 cells in the same column will have =VLOOKUP('List of Clients'!$A$3,'List of contacts'!$A$2:AM$4475,2,FALSE) The only thing that changes is $A$2 to $A$3 then $A$4, etc. Does this clarify? -- Thank you, scrowley(AT)littleonline.com "Sheeloo" wrote: Have you give R1C1 reference for illustration or is it a real formula? What you want can be achieved by combining ="R"&INT((ROW()+7)/8)+13&"C1" with INDIRECT The formula above if entered in Row 1 and copied down will give you R14C1 for 14 rows then R15C1 for 14 rows and so on.. If you give your correct VLOOKUP formula then I can build the formula for you "Sandy Crowley" wrote: I have a forumla where I'm looking up a Client name and finding all the contacts that relate to that Client. The most contacts per Client is 8. This formula works great, I just need it to reference R14C1 for 8 rows and then increase to R15C1 for 8 rows, etc. I have 562 unique Clients x 8 =4496 and I'm sure there is a way to Fill Down this formula instead of pasting it 4496 times. Thank you! =VLOOKUP('List of Clients'!R14C1,'List of contacts'!R2C1:R4475C[38],2,FALSE) -- Thank you, scrowley(AT)littleonline.com |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need formula to increment cell reference every 8th row
Double quotes have to be there...
Just enter this in row 1 =INDIRECT("'List of Clients'!"&("R"&INT((ROW()+7)/8)+13&"C1")) You should get the value in 'List of Clients'!R14C1 Hope you still have R1C1 setting on... Once you get the above to work, insert it into your VLOOKUP formula "Sandy Crowley" wrote: I copied your formula and removed the double quotes around the sheet name 'List of Clients'! and I get #REF as well as an error message saying the formula I have entered contains an error. ARGGG!!! Sorry for the thick head. Once the light bulb comes on, I'll be good. -- Thank you, scrowley(AT)littleonline.com "Sheeloo" wrote: Use =INDIRECT("'List of Clients'!"&("R"&INT((ROW()+7)/8)+13&"C1")) It basically evaluates INT((ROW()+7)/8)+13 to get a number (14 in row 1) which is then inserted between 'List of Clients'! and C1 to the completed address... Indirect then goes to that cell and gets the value there... You did not put quotes around the sheet name ... Just put the indirect function in a cell and see what you get =INDIRECT("'List of Clients'!"&("R"&INT((ROW()+7)/8)+13&"C1")) also copy it to the right and below to see how it changes... "Sandy Crowley" wrote: I'm sorry. I just don't get it. I tried: =VLOOKUP(INDIRECT('List of Clients'!&(R&INT((ROW()+7)/8)+13&C1)),'List of contacts'!R2C1:R4475C[38],2,FALSE) I guess I don't understand INDIRECT very well. Can you step me through the formula you wrote? Thanks -- Thank you, scrowley(AT)littleonline.com "Sheeloo" wrote: Yes it does... One more question... If you use =VLOOKUP('List of Clients'!$A$2,'List of contacts'!$A$2:AM$4475,2,FALSE) 14 times you will get the same value back every time... the first match for A2 Replace 'List of Clients'!R14C1 in your formula with =INDIRECT("'List of Clients'!"&("R"&INT((ROW()+7)/8)+13&"C1")) assuming you start at row1.. "Sandy Crowley" wrote: Sheeloo, Thanks for your reply. I have Use the R1C1 turned on in my Excel Options. This is the formula without the R1C1: =VLOOKUP('List of Clients'!$A$2,'List of contacts'!$A$2:AM$4475,2,FALSE) Ideally, 8 cells in the same column will have =VLOOKUP('List of Clients'!$A$2,'List of contacts'!$A$2:AM$4475,2,FALSE) and the next 8 cells in the same column will have =VLOOKUP('List of Clients'!$A$3,'List of contacts'!$A$2:AM$4475,2,FALSE) The only thing that changes is $A$2 to $A$3 then $A$4, etc. Does this clarify? -- Thank you, scrowley(AT)littleonline.com "Sheeloo" wrote: Have you give R1C1 reference for illustration or is it a real formula? What you want can be achieved by combining ="R"&INT((ROW()+7)/8)+13&"C1" with INDIRECT The formula above if entered in Row 1 and copied down will give you R14C1 for 14 rows then R15C1 for 14 rows and so on.. If you give your correct VLOOKUP formula then I can build the formula for you "Sandy Crowley" wrote: I have a forumla where I'm looking up a Client name and finding all the contacts that relate to that Client. The most contacts per Client is 8. This formula works great, I just need it to reference R14C1 for 8 rows and then increase to R15C1 for 8 rows, etc. I have 562 unique Clients x 8 =4496 and I'm sure there is a way to Fill Down this formula instead of pasting it 4496 times. Thank you! =VLOOKUP('List of Clients'!R14C1,'List of contacts'!R2C1:R4475C[38],2,FALSE) -- Thank you, scrowley(AT)littleonline.com |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need formula to increment cell reference every 8th row
I may be putting this on the wrong sheet. I'm pasting the formula on a Blank
Sheet of the same workbook and not on the List on Contacts sheet. I'm still getting #REF -- Thank you, scrowley(AT)littleonline.com "Sheeloo" wrote: Double quotes have to be there... Just enter this in row 1 =INDIRECT("'List of Clients'!"&("R"&INT((ROW()+7)/8)+13&"C1")) You should get the value in 'List of Clients'!R14C1 Hope you still have R1C1 setting on... Once you get the above to work, insert it into your VLOOKUP formula "Sandy Crowley" wrote: I copied your formula and removed the double quotes around the sheet name 'List of Clients'! and I get #REF as well as an error message saying the formula I have entered contains an error. ARGGG!!! Sorry for the thick head. Once the light bulb comes on, I'll be good. -- Thank you, scrowley(AT)littleonline.com "Sheeloo" wrote: Use =INDIRECT("'List of Clients'!"&("R"&INT((ROW()+7)/8)+13&"C1")) It basically evaluates INT((ROW()+7)/8)+13 to get a number (14 in row 1) which is then inserted between 'List of Clients'! and C1 to the completed address... Indirect then goes to that cell and gets the value there... You did not put quotes around the sheet name ... Just put the indirect function in a cell and see what you get =INDIRECT("'List of Clients'!"&("R"&INT((ROW()+7)/8)+13&"C1")) also copy it to the right and below to see how it changes... "Sandy Crowley" wrote: I'm sorry. I just don't get it. I tried: =VLOOKUP(INDIRECT('List of Clients'!&(R&INT((ROW()+7)/8)+13&C1)),'List of contacts'!R2C1:R4475C[38],2,FALSE) I guess I don't understand INDIRECT very well. Can you step me through the formula you wrote? Thanks -- Thank you, scrowley(AT)littleonline.com "Sheeloo" wrote: Yes it does... One more question... If you use =VLOOKUP('List of Clients'!$A$2,'List of contacts'!$A$2:AM$4475,2,FALSE) 14 times you will get the same value back every time... the first match for A2 Replace 'List of Clients'!R14C1 in your formula with =INDIRECT("'List of Clients'!"&("R"&INT((ROW()+7)/8)+13&"C1")) assuming you start at row1.. "Sandy Crowley" wrote: Sheeloo, Thanks for your reply. I have Use the R1C1 turned on in my Excel Options. This is the formula without the R1C1: =VLOOKUP('List of Clients'!$A$2,'List of contacts'!$A$2:AM$4475,2,FALSE) Ideally, 8 cells in the same column will have =VLOOKUP('List of Clients'!$A$2,'List of contacts'!$A$2:AM$4475,2,FALSE) and the next 8 cells in the same column will have =VLOOKUP('List of Clients'!$A$3,'List of contacts'!$A$2:AM$4475,2,FALSE) The only thing that changes is $A$2 to $A$3 then $A$4, etc. Does this clarify? -- Thank you, scrowley(AT)littleonline.com "Sheeloo" wrote: Have you give R1C1 reference for illustration or is it a real formula? What you want can be achieved by combining ="R"&INT((ROW()+7)/8)+13&"C1" with INDIRECT The formula above if entered in Row 1 and copied down will give you R14C1 for 14 rows then R15C1 for 14 rows and so on.. If you give your correct VLOOKUP formula then I can build the formula for you "Sandy Crowley" wrote: I have a forumla where I'm looking up a Client name and finding all the contacts that relate to that Client. The most contacts per Client is 8. This formula works great, I just need it to reference R14C1 for 8 rows and then increase to R15C1 for 8 rows, etc. I have 562 unique Clients x 8 =4496 and I'm sure there is a way to Fill Down this formula instead of pasting it 4496 times. Thank you! =VLOOKUP('List of Clients'!R14C1,'List of contacts'!R2C1:R4475C[38],2,FALSE) -- Thank you, scrowley(AT)littleonline.com |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need formula to increment cell reference every 8th row
Maybe I should start over:
Sheet 1 has unique names of all the Clients in C1 (each Client per Row = 526 Clients) Sheet 2 has a list of all contacts and the companies they work for (Table Array Sheet 2 R2C1:R4476C[38] I would like to return all the contacts that relate to each client. I'll Fill the formula down and over and I need at least 8 rows to allow for 8 different contacts per Client. How would you write that nested formula? -- Thank you, scrowley(AT)littleonline.com "Sheeloo" wrote: Double quotes have to be there... Just enter this in row 1 =INDIRECT("'List of Clients'!"&("R"&INT((ROW()+7)/8)+13&"C1")) You should get the value in 'List of Clients'!R14C1 Hope you still have R1C1 setting on... Once you get the above to work, insert it into your VLOOKUP formula "Sandy Crowley" wrote: I copied your formula and removed the double quotes around the sheet name 'List of Clients'! and I get #REF as well as an error message saying the formula I have entered contains an error. ARGGG!!! Sorry for the thick head. Once the light bulb comes on, I'll be good. -- Thank you, scrowley(AT)littleonline.com "Sheeloo" wrote: Use =INDIRECT("'List of Clients'!"&("R"&INT((ROW()+7)/8)+13&"C1")) It basically evaluates INT((ROW()+7)/8)+13 to get a number (14 in row 1) which is then inserted between 'List of Clients'! and C1 to the completed address... Indirect then goes to that cell and gets the value there... You did not put quotes around the sheet name ... Just put the indirect function in a cell and see what you get =INDIRECT("'List of Clients'!"&("R"&INT((ROW()+7)/8)+13&"C1")) also copy it to the right and below to see how it changes... "Sandy Crowley" wrote: I'm sorry. I just don't get it. I tried: =VLOOKUP(INDIRECT('List of Clients'!&(R&INT((ROW()+7)/8)+13&C1)),'List of contacts'!R2C1:R4475C[38],2,FALSE) I guess I don't understand INDIRECT very well. Can you step me through the formula you wrote? Thanks -- Thank you, scrowley(AT)littleonline.com "Sheeloo" wrote: Yes it does... One more question... If you use =VLOOKUP('List of Clients'!$A$2,'List of contacts'!$A$2:AM$4475,2,FALSE) 14 times you will get the same value back every time... the first match for A2 Replace 'List of Clients'!R14C1 in your formula with =INDIRECT("'List of Clients'!"&("R"&INT((ROW()+7)/8)+13&"C1")) assuming you start at row1.. "Sandy Crowley" wrote: Sheeloo, Thanks for your reply. I have Use the R1C1 turned on in my Excel Options. This is the formula without the R1C1: =VLOOKUP('List of Clients'!$A$2,'List of contacts'!$A$2:AM$4475,2,FALSE) Ideally, 8 cells in the same column will have =VLOOKUP('List of Clients'!$A$2,'List of contacts'!$A$2:AM$4475,2,FALSE) and the next 8 cells in the same column will have =VLOOKUP('List of Clients'!$A$3,'List of contacts'!$A$2:AM$4475,2,FALSE) The only thing that changes is $A$2 to $A$3 then $A$4, etc. Does this clarify? -- Thank you, scrowley(AT)littleonline.com "Sheeloo" wrote: Have you give R1C1 reference for illustration or is it a real formula? What you want can be achieved by combining ="R"&INT((ROW()+7)/8)+13&"C1" with INDIRECT The formula above if entered in Row 1 and copied down will give you R14C1 for 14 rows then R15C1 for 14 rows and so on.. If you give your correct VLOOKUP formula then I can build the formula for you "Sandy Crowley" wrote: I have a forumla where I'm looking up a Client name and finding all the contacts that relate to that Client. The most contacts per Client is 8. This formula works great, I just need it to reference R14C1 for 8 rows and then increase to R15C1 for 8 rows, etc. I have 562 unique Clients x 8 =4496 and I'm sure there is a way to Fill Down this formula instead of pasting it 4496 times. Thank you! =VLOOKUP('List of Clients'!R14C1,'List of contacts'!R2C1:R4475C[38],2,FALSE) -- Thank you, scrowley(AT)littleonline.com |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need formula to increment cell reference every 8th row
Yes, that may work...
Now if you have contacts and their company (client is same as company, right?) names on Sheet2 then why not simply copy those two columns over to Sheet1? You can always sort on Comany name... Since contacts would be unique not the company, you should look for company name given a contact...Simple VLOOKUP will do. What am I missing here? btw what does C[38] signify... I believe it refers to 38th column from C1... "Sandy Crowley" wrote: Maybe I should start over: Sheet 1 has unique names of all the Clients in C1 (each Client per Row = 526 Clients) Sheet 2 has a list of all contacts and the companies they work for (Table Array Sheet 2 R2C1:R4476C[38] I would like to return all the contacts that relate to each client. I'll Fill the formula down and over and I need at least 8 rows to allow for 8 different contacts per Client. How would you write that nested formula? -- Thank you, scrowley(AT)littleonline.com |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need formula to increment cell reference every 8th row
Thank you, Sheeloo!
I did as you suggested and it worked. Thanks again. -- Thank you, scrowley(AT)littleonline.com "Sheeloo" wrote: Yes, that may work... Now if you have contacts and their company (client is same as company, right?) names on Sheet2 then why not simply copy those two columns over to Sheet1? You can always sort on Comany name... Since contacts would be unique not the company, you should look for company name given a contact...Simple VLOOKUP will do. What am I missing here? btw what does C[38] signify... I believe it refers to 38th column from C1... "Sandy Crowley" wrote: Maybe I should start over: Sheet 1 has unique names of all the Clients in C1 (each Client per Row = 526 Clients) Sheet 2 has a list of all contacts and the companies they work for (Table Array Sheet 2 R2C1:R4476C[38] I would like to return all the contacts that relate to each client. I'll Fill the formula down and over and I need at least 8 rows to allow for 8 different contacts per Client. How would you write that nested formula? -- Thank you, scrowley(AT)littleonline.com |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro - How to increment cell reference by one row | Excel Worksheet Functions | |||
| Increment cell reference | Excel Discussion (Misc queries) | |||
| how to auto increment cell location within formula | Excel Worksheet Functions | |||
| how to make cell address reference increment? | Excel Worksheet Functions | |||
| Row reference increment but preserve column reference | Excel Worksheet Functions | |||
Linear Mode
