**Question description:**

*This user has given permission to use the problem statement for this blog.*

I'm using a formula to reference a closed spreadsheet but I only want it to return the data in the column not repeat the data from the start

Solved by A. H. in 60 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
10/03/2018 - 04:56

Hi

Excelchat Expert
10/03/2018 - 04:56

How are you

Excelchat Expert
10/03/2018 - 04:57

nd how may i assist you with today

User
10/03/2018 - 04:57

I'm good for 4am Sydney time

Excelchat Expert
10/03/2018 - 04:57

OOO, wake up too early

User
10/03/2018 - 04:57

im writing a number of formulas

User
10/03/2018 - 04:57

no still up late

Excelchat Expert
10/03/2018 - 04:57

Okay seems to be hard working

User
10/03/2018 - 04:58

hang on ill show the syntax

Excelchat Expert
10/03/2018 - 04:58

sure go ahead

Excelchat Expert
10/03/2018 - 04:58

m in india 11pm, working hard

User
10/03/2018 - 04:59

ok

Excelchat Expert
10/03/2018 - 05:00

Okay thats a referencing from different file i guess?

User
10/03/2018 - 05:00

so I put the correct file path and it returns the data in a column but if I copy down the column it keeps repeating the data

User
10/03/2018 - 05:00

it returns the data in the list then after it has returned all the data there is it starts returning from the top again

User
10/03/2018 - 05:01

I only want it to return the data from the list one time

Excelchat Expert
10/03/2018 - 05:01

while referencing it would have have been great if you have shared the sheet

Excelchat Expert
10/03/2018 - 05:02

can you now share me both the sheets?

User
10/03/2018 - 05:02

example if there are 10 names in a column and I use this formula and copy it down the whole column in the sheet where I want the return it doesn't stop after returning the ten names it keeps repeating down the column

User
10/03/2018 - 05:03

i cant share as its my wifes work but i can re create here'

Excelchat Expert
10/03/2018 - 05:04

yes that will workout for me, please go ahead

Excelchat Expert
10/03/2018 - 05:04

Names ae in column A,? right

User
10/03/2018 - 05:04

one moment

Excelchat Expert
10/03/2018 - 05:05

Okay

User
10/03/2018 - 05:06

well the idea is to use the example sheets here and the list of names in sheet 2

User
10/03/2018 - 05:07

then return the values in sheet 1

Excelchat Expert
10/03/2018 - 05:08

Okay

Excelchat Expert
10/03/2018 - 05:09

its seems the index formula is not working properly in shared sheet

Excelchat Expert
10/03/2018 - 05:09

wait i will share you the excel file with this example

User
10/03/2018 - 05:10

maybe it will help if i explain what im trying to do more

Excelchat Expert
10/03/2018 - 05:10

yes sure please go ahead

User
10/03/2018 - 05:11

i have a file with multiple tabs all of which have the same header title for a certain data

Excelchat Expert
10/03/2018 - 05:11

Okay

User
10/03/2018 - 05:12

i want to extract all the data from each tab in the column that matches the header title then return that data in one column on another spreadsheet

Excelchat Expert
10/03/2018 - 05:12

then i guess Vlookup clause would work well the

User
10/03/2018 - 05:12

so each list of names for example from each tab in one column after each other

User
10/03/2018 - 05:13

ive been told index match would work better

User
10/03/2018 - 05:13

but i don't mind as long as it does the job

Excelchat Expert
10/03/2018 - 05:13

yes, aggregation of index match = vlookup

User
10/03/2018 - 05:14

can you write me a formula that allows me to do this

User
10/03/2018 - 05:14

and then i can just change the file path myself

Excelchat Expert
10/03/2018 - 05:14

vlookup is the simplest form of index and match

Excelchat Expert
10/03/2018 - 05:14

okay

Excelchat Expert
10/03/2018 - 05:14

sure will do that

User
10/03/2018 - 05:14

thanks

Excelchat Expert
10/03/2018 - 05:15

See in sheet 2 different name have different values

User
10/03/2018 - 05:16

why do they have values at all

Excelchat Expert
10/03/2018 - 05:16

now for example we want to look up dave's value in sheet1

Excelchat Expert
10/03/2018 - 05:16

its for example

Excelchat Expert
10/03/2018 - 05:17

Now check in sheet 1

User
10/03/2018 - 05:17

ok so if i can explain using this example i just want to take the list of names from sheet 2 and return them in sheet 1 but with no duplicates

Excelchat Expert
10/03/2018 - 05:18

Okay

User
10/03/2018 - 05:18

obviously this is a simple version and i would change the formula so it will take the list from another spreadsheet that is also closed sheet

Excelchat Expert
10/03/2018 - 05:20

Now check in Sheet 1, i guess this is the issue you might be facing while copying to other cells, right?

User
10/03/2018 - 05:21

i see nothing in sheet 1

User
10/03/2018 - 05:21

oh ok

User
10/03/2018 - 05:21

wait

User
10/03/2018 - 05:21

well that's a start

User
10/03/2018 - 05:22

but i want it to return the whole list and only return each name once

User
10/03/2018 - 05:22

so no duplicates

Excelchat Expert
10/03/2018 - 05:22

see now

User
10/03/2018 - 05:23

getting there

User
10/03/2018 - 05:23

now i want tit to return only each name once

Excelchat Expert
10/03/2018 - 05:23

yes like john appeard twice you dont want that right?

User
10/03/2018 - 05:23

yes

Excelchat Expert
10/03/2018 - 05:26

it can done using the remove duplicate option

User
10/03/2018 - 05:26

what is that

User
10/03/2018 - 05:26

need the formula to do it automatically

Excelchat Expert
10/03/2018 - 05:27

yes i understand trying to evaluate it

User
10/03/2018 - 05:29

is there a simple way to copy the whole column and then auto remove duplicates after that

Excelchat Expert
10/03/2018 - 05:30

For that you would need vba code,

User
10/03/2018 - 05:30

oh i c

User
10/03/2018 - 05:30

im trying to get away from VBA

Excelchat Expert
10/03/2018 - 05:30

still will come up with a formula

User
10/03/2018 - 05:31

will use it if i need to but that means getting someone to help write it

User
10/03/2018 - 05:32

you see there are some other processes i need to do as well after i extract the list

Excelchat Expert
10/03/2018 - 05:32

Okay

User
10/03/2018 - 05:33

maybe i can add to this spreadsheet to show more of the example

Excelchat Expert
10/03/2018 - 05:34

yes just randomize your data and share it

User
10/03/2018 - 05:34

ok

User
10/03/2018 - 05:36

this data is time in hours minutes seconds

Excelchat Expert
10/03/2018 - 05:36

okay

Excelchat Expert
10/03/2018 - 05:39

this is the main file right?

Excelchat Expert
10/03/2018 - 05:39

where there is all the data

User
10/03/2018 - 05:39

yes so ill do just one tab but there are multiple tabs

Excelchat Expert
10/03/2018 - 05:40

okay

Excelchat Expert
10/03/2018 - 05:41

So you need all these data uniquely in other sheet, right?

Excelchat Expert
10/03/2018 - 05:43

Okay i got it, for this you would just 1 formula in sheet 3 ie: Sumifs

Excelchat Expert
10/03/2018 - 05:44

And for names i can see you would need unique values

User
10/03/2018 - 05:44

yeah i can make the sumifs but i need to be able to expand the cell to show the sub totals

User
10/03/2018 - 05:44

ill show you

Excelchat Expert
10/03/2018 - 05:47

So you just need the unique names from the list right,?

User
10/03/2018 - 05:49

so i need to sum each name separately that way they oinly appear once in sheet 3 with a total for that name then a total for all the names

User
10/03/2018 - 05:49

but i need this list of names in sheet 3 to be like an expandable cell

User
10/03/2018 - 05:50

this is for my wifes work and she is asleep so i cant ask but i understand what she wants

User
10/03/2018 - 05:50

she described it like a pivot table in sheet 3

User
10/03/2018 - 05:50

so the data is like "sort and filter"

User
10/03/2018 - 05:51

or appears like a pivot table and you can do the same as sort and filter

Excelchat Expert
10/03/2018 - 05:51

yes, also it should be able to add unique names

User
10/03/2018 - 05:51

yes sum each unique name from sheet one and give it 1 total for that name in sheet 3

User
10/03/2018 - 05:52

thn do this for each unique name

User
10/03/2018 - 05:52

the sum all the names in sheet 3 also

Excelchat Expert
10/03/2018 - 05:52

Sumifs will work out for this

User
10/03/2018 - 05:52

im sorry if this is difficult for you but im sure you are so good at this that you can come up with some great code

Excelchat Expert
10/03/2018 - 05:53

it would have but it should have been great if you could have shared the sample sheet

User
10/03/2018 - 05:53

so i think if you can write a formula that will work for this example i could adjust or change the file names and locations to suit what i need

User
10/03/2018 - 05:54

sorry the sample sheet is on another computer i don't have access too at this time

User
10/03/2018 - 05:55

i hope time doesn't run out for us

Excelchat Expert
10/03/2018 - 05:56

,seein for john

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*