# Using INDIRECT function in multiple rows.

1. ## Using INDIRECT function in multiple rows.

I am building an attendance spreadsheet and trying to copy and paste all of the names from Sheet 1 to Sheet 6 for a different purpose. Thing is I need the names to match so I'm not having to copy and paste every time my data changes.

My problem was that when I insert a row into sheet 1, sheet 6 accommodates and then I'm missing data and have to insert a new line manually.

At first I tried using absolutes (\$) to fix the problem, but that's a different ballgame.
I've discovered INDIRECT and so now use this formula: =INDIRECT("'DIRECTORY 2014'!B5"). It works.

But I have almost 300 entries. Is there a way to expedite the process without having to change each entry? I don't want to have to retype the function on every cell.

2. ## Re: Using INDIRECT function in multiple rows.

hi JeffreyNile,

As i understood here, you don't want to re-type the indirect function?

regards

3. ## Re: Using INDIRECT function in multiple rows.

If your list of names is unique, you could do something like this...

 A B C 1 Name 2 aa aa 3 bb bb 4 cc cc 5 dd dd 6 ee ee 7 ff ff 8 gg gg 9 0 10 11 12

C2=IFERROR(INDEX(\$A\$2:\$A\$10,MATCH(0,INDEX(COUNTIF(\$C\$1:C1,\$A\$2:\$A\$10),0,0),0)),"")

You could move that to the sheet you need it on.

Or if you just want to pull the data in as it is...
=IF(INDEX(Sheet4!A:A,ROW(A2))="","",INDEX(Sheet4!A:A,ROW(A2)))

4. ## Re: Using INDIRECT function in multiple rows.

Worked like a charm. I made the proper substitutions:

=IF(INDEX('DIRECTORY 2014'!B:B,ROW(C5))="","",INDEX('DIRECTORY 2014'!B:B,ROW(C5)))

Guess my function skills have a lot more to improve on.

5. ## Re: Using INDIRECT function in multiple rows.

you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)

6. ## Re: Using INDIRECT function in multiple rows.

Hi there,

works also with INDIRECT:

Formula:
`Please Login or Register  to view this content.`

7. ## Re: Using INDIRECT function in multiple rows.

Will do. This is my first post. Where do I mark solved?

8. ## Re: Using INDIRECT function in multiple rows.

Miros, nice formula

Note though that INDIRECT() is a volatile function (it updates with every workbook change), and if used in significant numbers will tend to slow things down. If there are only a few, it's not a problem though

http://www.decisionmodels.com/calcsecretsi.htm

9. ## Re: Using INDIRECT function in multiple rows.

2. If your question is resolved, mark it SOLVED using the thread tools just above your 1st post

10. ## Re: Using INDIRECT function in multiple rows.

Perfect. I read right over that one. Thanks for your help once again.

There are currently 1 users browsing this thread. (0 members and 1 guests)