Creation of a data validation cell that can contain values from MULTIPLE worksheets

Dear forum,

I am a beginner at creating excel Formulas, so i have an issue creating a data validation cell.

I want to create a data validation cell that can contain values from multiple worksheets:

The background is that I am creating a summary page, in where I want to be able to choose between different data from multiple worksheets (where the layout is the same but with different figures). These worksheets will contain prices on multiple Products from multiple vendors and in the summary page I will compare these and therefore I want to be able to easily swich from one vendor's price to another's.

The way I am trying to accomplish this is by creating a data validation cell, allowing a list with the source: "='Data sheet 1'!$C$8, 'Data sheet 2'!$C$8, 'Data sheet 3'!$C$8"

But this only returns error messages. It works perfect if i have all the data pasted into the same worksheet and the source looks like this: "='Data sheet 1'!$C$8:$E$8"

Does anyone know how to solve this? The issue is that the data sheets will contain a lot of data, and it will be a mess if I should have to copy all the data into one sheet, especially if some of the data should change.

I have attached an example of a mockup I made:example.PNG

Very thankful for advice! // Zimmermann

You can use a macro to add all data in sheet.

After that use a pivot table to get the summary etc.

If you want it that way, you get better help if you add an small excel file without confidentional information.

Please also add the desired result.

Hi Zimmermann and welcome to the forum

(just fyi, I live in Zimmerman's lane lol)

If you have a bunch of data on different sheets that you want to include in a DV list, maybe you could just reference the entries from each sheet into a summary somewhere. By referencing the data, instead of copy/pasting, if you change the data, the list will update automatically.

As suggested though, it will be easier to help you if we had some data to work with

With a macro you get all the data to one sheet.

After that you can make an pivot table or filter on the data.

The macro who is been used is:

Please Login or Register  to view this content.

See the attached file (for the result).

Based on the sample you provided, the following formula, copied down and across in your table, will provide the answers you want...

=INDEX(INDIRECT("'"&$C7&"'!$B$3:$M$9"),MATCH(Summary!D$5,INDIRECT("'"&$C7&"'!$B$3:$B$9"),0),MATCH(Summary!$B7,INDIRECT("'"&$C7&"'!$B$3:$M$3"),0))

That does work across multiple sheets. It uses the sheet name you have in column C for the sheet name reference

=INDEX(INDIRECT("'"&$C7&"'!$B$3:$M$9"),MATCH(Summary!D$5,INDIRECT("'"&$C7&"'!$B$3:$B$9"),0),MATCH(Summary!$B7,INDIRECT("'"&$C7&"'!$B$3:$M$3"),0))

So as long as you use exactly the same sheet names in C, it will pull the required info

what do you mean by "data validation"? DV is used to ensure that only specified info is entered into a cell by a user

@Keiralea

In your respond #8, your breaking the forum rules.

You ask a question in the question of somebody else.

Please start your own question an refer to this treat.

oops thanks oeldere, I didnt notice that wasnt the OP

Keiralea no problem. If you think its valid, you can add a link to this thread in your own thread. just copy/paste the web address above