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