I've got a problem.

I've got 23 sheets that are constantly being edited. I've set up another sheet to populate a list of all the data in Column B from each of the 23 sheets. Now, from THAT list, I need to reference the actual text in each cell, but since the cells are actually equations, I don't know how to reference the actual text populated into the cell, and not the equation.

Ex.

Sheet 1:
A1:A3 are as follows:

Dog
Train
Dog

Sheet 2:
A1:A3 are as follows:

Elf
Truck
Train

My compilation sheet (Sheet 24, in this case):
A1:A6 are as follows
='Sheet 1'!A1
='Sheet 1'!A2
='Sheet 1'!A3
='Sheet 2'!A1
='Sheet 2'!A2
='Sheet 2'!A3

This spits out:

Dog
Train
Dog
Elf
Truck
Train


Now, I have another sheet (Sheet 25) that needs to reference the actual text in each cell from Sheet 24, and not the equations in each cell.

How can this be done?

you may have to elaborate. do you just want sheet 25 to spit out dog, train, efl, etc. or do you need to reference those values in another formulas?

I'm wanting to populate a de-duped column of everything from all the other sheets, basically.

So, what I want is for Sheet 25 to have a list like this:

Dog
Train
Elf
Truck

(Everything from before, minus dupes)

If there is a way to directly reference the other sheets, that's fine, too, though it IS 23 sheets that need referenced.

I'm wanting all the info on Sheet 25 (even the de-duped values) to continuously be updated when people edite Sheets 1-23.

Copy/Paste Special, Paste Values only.

That's the manual way to do it, if you are looking for an automatic version let me know and I can think harder.

macro. copy, paste special, values.

that's the best i can come up with.

jmog, yeah, I'm looking for an automated way, since these records are updated throughout the day. It'd be a royal PITA to have to copy/paste 23 times every time I wanted updated cumulative results.

How this is working is, the column I'm trying to transpose is a "campaign name," and we run multiple campaigns simultaneously. Each sheet represents a day. If we add a new campaign on a certain day (and thus, place it in the list I would be "copy/pasting"), I want it to automatically populate in the list on Sheet 25. That's the goal. If I have to do a macro, then I have to, but there are others in my company who will be using this who are not as ... savvy with Excel as other. I have a feeling explaining to them how it works would be more challenging than it should be, and I honestly don't trust all of them to run it every time they need their stats updated (some will probably just update the day sheet and then pull the numbers off Sheet 25 without updating), which could create some embarrassing meetings.

I've undertaken this project in hopes of trying to streamline the process of "pulling stats." As it is, most people take 30 to 60 minutes to pull necessary data, and most of it is because they've never even considered using Excel to calculate the relevant stats (right now they just pull the stats off the intranet charts and type in every last one by hand). The amount of time it could save, long term, will help everyone be more efficient.

Sheet 25 can just be a pivot table of the values from sheet 24. To do so, in column B of sheet 24 I would copy/paste values. Then give it an arbitrary title so that a pivot table won't think the first record is the title to the data. In the pivot table drag the data into rows. This will get rid of duplicates (what I assume you meant by dupes) and become your sheet 25. Whenever you need to update sheet 25 you will just have to edit the parameters of the pivot table to include the additional text cells of sheet 24, column B.

So from your example:

Dog
Train
Dog
Elf
Truck
Train

would be pivoted to become:

Dog
Train
Elf
Truck

I just saw your post above mine. As long as all 23 sheets results are streamed into the sheet 24 then you will only need to copy/paste once with what I instructed and then the pivot works to automate the list and eliminate duplicates.