Connect values from sheets to a drop-down list

Hi,

Have been trying to find information on this forum regarding my "problem" but didn't have any luck. Maybe was searching for the wrong thing because im not sure of the correct name for my "problem". If anyone finds the related topic already exist on this forum just send me the link . Otherwise this is what i want to do.

I have a large excel document, with +10 different sheets which contain the exact same layout and number of cells (+1500 coloums, between koloum A-N).

I have created a sheet that I call the "gatherer" this sheets sorts out all relevant information I need. For example I use the "replace system" ctrl+B. This means I go in to my "gatherer" and press ctrl+b and then write the name of the sheet that is currently in use "example, sheet1" and replace this with for "example sheet2" then all the values that are connected in the "gatherer" for each cell will change depending on the sheet i wish to use (='sheet2'!XX), the XX is different in all cells numbering rom 1-1500 and A-N.

Can I make a dropdown list for my different sheets that will automaticly do the same thing as ctrl+b system as im currently using? My excel file is 10 MB so I can't upload it but that maybe shows why I am trying to simplify this...

@Glenn Kennedy, @davesexcel

Thanks for the tip, have deleted some sheets and just left sheet 1-2. I've highlighted the cell in "value generator" which I use when replacing the sheet name in use. Right now it's sheet1 so the "value generator" sheet is gathering all the data i want from "sheet1". As I mentioned I use the ctrl+b to change the name of the sheet so the value's I wish to work with are changed, so the only thing I would like is to stop using the ctrl+b to replace the values and instead have a drop-list where I choose which sheet I want my "value generator" to use.

Originally Posted by Glenn Kennedy

Cut down your sheet to a workable example with one or two sheets and a dozen rows....

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

I tried to create a sample workbook but without any success... Instead I re-uploaded the excel file with a simple guidence how I want it to work. The function i desire is possible to try out and have written a describtion with using ctrl+b. Please have a look again , btw my excel is in swedish and I don't have the english language pack, sorry for this in advance.

Originally Posted by Glenn Kennedy

I took a look at this earlier today, but I got confused... I think it's probably a VERY major job - but I'll take another look now.

That's maybe true, but I guess if it's possible to make it in excel I was thinking this forum was the best place to check. If it's shows to be impossible to create a function like this then im going to try and program it instead.

Originally Posted by Glenn Kennedy

It is do-able. I have a way of doing it. there may be a better way that someone else can come up with. I have to eat now. Look back later (1 hour) or in the morning.

no problem, thanks for looking into it, will try some things myself during the meanwhile

thanks for the information. I was heading in the same direction but didn't know what function to use. Will read more about this INDIRECT function and how to use it.

Originally Posted by davesexcel

I think I understand, You can use the Indirect Function, check out the attached.
Look at the yellow cells, select A11 for the drop down.

Note: oh, I see this has been discussed already.

You can still probably use the find replace function to edit the formulas, it might take some time but once done it's done.

Thanks, looks great will try it out. How did you get the drop-down list based on the sheets without adding an extra sheet with the names? (value1, value2)

Originally Posted by Glenn Kennedy

I have done this for a few of the "total" rows (coloured green) and for a range of the individual rows (coloured yellow).

Total rows. Copy the content of E22 into the next total cell (E103). Replace "6" with desoired target cell row, which is 550 Then drag to the right.

Individual rows. First work out the formula that you need. Your next individual row is in sorter E105. It is drawing data from the other sheets E572. The formula adjustment you need is

source row-(row()-destination row)/2 i.e. 572+(row()-105)/2 This will give you a result of 572, two rows down it will give you 573, etc, etc.

Then copy the formula in E26 into E105. Edit the formula replacing everything from the last & to the last bracket witht he phrase above. Copy across. Copy and paste into the individual rows, one at a time until you getto the next total row.

Repeat until you get to the end.

Relax.

If you do run into problems, shout... I haven't completed the exercise as I don't know howfar down your master sheet that you have to go.... I've left my "working notes"in the sheet (columns P & S) which you can delete.

Sry for not replaying yesterday, the master sheet start at row 6 and ends in 1776 and koloum starts in E and ends at N. Will try to add all master sheets today so there will not be any friday party for me hehe. Thanks for the amazing help guys, will throw in all masters sheet today and try to understand how everything works!

Will also try to add the same drop-down list that "davesexcel" made to try to skip the extra sheet. Will get back if i get stuck somewhere

Okey get it, yes it was easy to add all the master sheets and everything works fine. Will try out two things now during the weekend. One is to duplicate your functions to every rows and the other will be to create more drop-down lists that are depended to each other, will try first on my own first . will write again after the weekend of my progress.

Was duplicating the formulas for each cells down the row and it was a never ending work. Was trying to use your INDIRECT formula and attach a VLOOKUP instead. For this I created a depended drop-down list which I wanted to connect to the VLOOKUP. So in other words I will get on drop-down lists where its possible to choose which sheet to extract values from and another which makes it possible to choose what type of value to show.

Have been trying to get it work but get the Reference error all the time. Is it possible to do this? See pic

\1

Originally Posted by Glenn Kennedy

Yes... it is a long way down to row 1776... A few hundred would have been OK, but.

I accidentally deleted your price per kg calculations.

The two additional dropdowns haven't been constructed yet (no time now - pub time), but the INDIRECT/INDEX/MATCH combination is looking for the correct sheet, by main category, by subcategory and by city.

It's an ARRAY formula that can be copied across and down as needed. is this what you had in mind?

It works amazing now! added additional information in the sheets (over 4 millions cells now, almost 85 MB) and it works amazing, cut my work load with hours!

There is three sheets with a variation (a shift in the coloums) where I will need to jump rows. Guess the array formula will fix this also, just need to delete some of the merged cells. As u mentioned before merged cells are not nice

I got it to work now, just added one more condition for the state so now it has 4 conditions that are changed using the dropdown lists. When it comes to the variation i just switched from searching in the range E1 (rows) to searching between E-X in row 5. Now it searches for the intersection in the selected range varied by the dropdown lists . Will try out your function also "davesexcel", thanks for the help, the thread can close.