Using INDIRECT function in multiple rows.

**JeffreyNiles** · 05-16-2014, 04:37 PM

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.

**blueblink01** · 05-16-2014, 04:50 PM

hi JeffreyNile,

Attach your sheet so I can address you correctly.

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

regards

**FDibbins** · 05-16-2014, 05:01 PM

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)))

**JeffreyNiles** · 05-16-2014, 05:12 PM

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.

**FDibbins** · 05-16-2014, 05:19 PM

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)

**Miroslav R.** · 05-16-2014, 05:20 PM

Hi there,

works also with INDIRECT:

Formula:

Please Login or Register  to view this content.

**JeffreyNiles** · 05-16-2014, 05:25 PM

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

**FDibbins** · 05-16-2014, 05:28 PM

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

see this link for more info on voliatile functions
http://www.decisionmodels.com/calcsecretsi.htm

**FDibbins** · 05-16-2014, 05:28 PM

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

**JeffreyNiles** · 05-16-2014, 05:29 PM

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

Using INDIRECT function in multiple rows.

LinkBack

Thread Tools

Rate This Thread

Display

Using INDIRECT function in multiple rows.

Re: Using INDIRECT function in multiple rows.

Re: Using INDIRECT function in multiple rows.

Re: Using INDIRECT function in multiple rows.

Re: Using INDIRECT function in multiple rows.

Re: Using INDIRECT function in multiple rows.

Re: Using INDIRECT function in multiple rows.

Re: Using INDIRECT function in multiple rows.

Re: Using INDIRECT function in multiple rows.

Re: Using INDIRECT function in multiple rows.

Thread Information

Users Browsing this Thread

Similar Threads

Using Indirect function to sum across multiple sheets

Indirect function / absolute reference - multiple rows -new cells inserted by shiftingdown

Excel 2007 : Indirect function and pulling data from multiple files

Moving Down Rows using indirect and address function

[SOLVED] Sum Indirect function through multiple sheets

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions