Connect values from sheets to a drop-down list

**hungry4more** · 11-27-2014, 03:54 AM

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** · 11-27-2014, 03:59 AM

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

**hungry4more** · 11-27-2014, 03:28 PM

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.

**davesexcel** · 11-27-2014, 04:03 AM

In my world Ctrl & B are the keystrokes for Bold
So I don't know what you are doing.
If you want to attach a sample workbook, make the sample smaller, by deleting some sheets, and only leaving a few rows in the existing sheets, we don't need a huge workbook to help you out.

**hungry4more** · 11-27-2014, 04:35 AM

@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.

**Glenn Kennedy** · 11-27-2014, 03:34 PM

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.

**hungry4more** · 11-27-2014, 03:50 PM

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.

**Glenn Kennedy** · 11-27-2014, 03:53 PM

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.

**hungry4more** · 11-27-2014, 03:55 PM

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

**Glenn Kennedy** · 11-27-2014, 04:27 PM

OK. You won't like this.... but.

I have added a sheet, onto which the sheet names have to be added (I have allowed for 20 sheets, but the Named Range can be adjusted to add more, if needed). I've used Data Validation on cell A2 o your Value Sorter. I had to kill the merged cell (A2&B2) to get it to work. Merged cells are the work of Satan and are best avoided wherever & whenever possible.

In cells C2 and right I have used the INDIRECT function to show you the problem. Cell E2 picks up the sheet name and combines it with E6 to correctly return 9,105,430. BUT. The syntax means that when you drag it to the right, the column letter does not update. To re-work your entire sheet would be a total nightmare.

However, by the addition of another phrase into the formula, you can get the column letter to update. See Cell F3. A more complicated formula, but when you drag it to the right, the use of RIGHT, CELL, etc, makes the column letter update as you drag to the right. BUT.

You cannot readily make something that copies DOWN as well. The rows that are being copied form the other sheets are not incrementing by 1 or 2 or 3. That would be something that could be addressed. However, in some places they increment by 1. In other cases it appears to be 22. There may be other variants.

You have two choices, maybe more.

1. Paste the formula in E3 into E22, E24, etc, adjusting the 6 at the end to reflect the target row. Then drag across.

2. It should be quite easy to get the formula set to increment the row number by 1, as you paste down two rows at a time. That would cut out a lot of the manual adjustments needed. However, I can't do that tonight. I'll certainly take a look in the morning for you. Just let me know how you'd like to proceed.

**hungry4more** · 11-27-2014, 04:59 PM

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.

**davesexcel** · 11-30-2014, 08:50 AM

Here's some code that you can try out to edit the formulas, check out the attached example, when you click new formula, Column E formulas will change to the indirect formulas.

Sub FigureItOut()
    Dim ws As Worksheet
    Dim r1 As Range, r2 As Range, s As String
    Dim x, p As String, fla As String
    Dim Rws As Long, Rng As Range, c As Range

    Set ws = Worksheets("Value genetrator")
    Set r1 = ws.Range("B:B")
    Set r2 = ws.Range("E:E")
    Application.ScreenUpdating = 0
    r1.AutoFilter Field:=1, Criteria1:="<>"
    r2.Replace What:="=Sheet", Replacement:="""", LookAt:=xlPart, _
               SearchOrder:=xlByRows, MatchCase:=False
    ws.AutoFilterMode = 0
    r2.AutoFilter Field:=1, Criteria1:="=*""*", _
                  Operator:=xlAnd

    Rws = Cells(Rows.Count, "E").End(xlUp).Row
    Set Rng = Range(Cells(2, 5), Cells(Rws, 5))
    For Each c In Rng.SpecialCells(xlCellTypeVisible)
        x = InStr(c, "!")
        s = Mid(c, x + 1, Len(c))
        p = """=INDIRECT(""'""&A11&""'!""&"""
        fla = p & s & """)"
        c = fla
    Next c
    r2.Replace What:="""=", Replacement:="=", LookAt:=xlPart, SearchOrder _
                                                              :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    ws.AutoFilterMode = 0
End Sub

Can you not use this forums upload to attach pics instead of using a 3rd party uploader??

**hungry4more** · 11-30-2014, 01:57 PM

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.

**davesexcel** · 11-27-2014, 05:17 PM

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.

**hungry4more** · 11-28-2014, 06:27 AM

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)

**Glenn Kennedy** · 11-27-2014, 06:43 PM

Sorted.... I'll be back in the morning. How many rows are there in the real master sheet?

**Glenn Kennedy** · 11-28-2014, 04:26 AM

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.

**hungry4more** · 11-28-2014, 06:33 AM

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

**Glenn Kennedy** · 11-28-2014, 06:37 AM

He typed them into the data validation souce box. In my opinion, it's better to have them in a named range on a separate sheet that you can hide - it's easier to adjust them from there.

**hungry4more** · 11-28-2014, 07:21 AM

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.

**Glenn Kennedy** · 11-28-2014, 07:32 AM

OK. No problem...

**hungry4more** · 11-28-2014, 03:05 PM

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

**Glenn Kennedy** · 11-28-2014, 03:44 PM

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?

**hungry4more** · 11-28-2014, 07:13 PM

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

**Glenn Kennedy** · 11-29-2014, 06:17 AM

If I have convinced you to avoid the horrors of the merged cell, then I'm happy. If there's anything more that you need, please don't hesitate to shut. However, if that is this problem sorted, please mark the thread as solved.

**hungry4more** · 11-29-2014, 07:39 AM

Im going to explain the variation of the three sheets now. U can use the same file u posted before. Then one called DD V3. If u see the picture i've posted called "value sorter sheet" and "sheet variation" you'll maybe understand the problem. Will try to explain at the same time.

So first of the value sorter is doing exatly what i wanted. First of the cell in A2 is a drop-down list, here we choose the sheet to analyze, also the names under the years (Cell E6 to N6) are equal to the A2. So when switching the sheet to analyze the name changes under the years. I've done this because if you look at the other picture ("sheet variation") you can see that there are double values for the same year. For this I have added the corresponding name in the drop-down list in A2. This means when I want to select this value the cell isn't jumping one step to the right. Is it possible to get the function to search in coloum and rows then find the intersection instead?

Btw also the state and city ("län" and "kommun" in cells C6 and D6 need to be connected the same way the undercategory and maincategory is in A6 and B6.

Sheet variation
\1

Value sorter sheet
\1

**Glenn Kennedy** · 11-30-2014, 02:14 PM

Good!! I was about to look at it. But if you're sorted, that's great.

Hybrid View

Connect values from sheets to a drop-down list

Re: Connect values from sheets to a drop-down list

Re: Connect values from sheets to a drop-down list

Re: Connect values from sheets to a drop-down list

Re: Connect values from sheets to a drop-down list

Re: Connect values from sheets to a drop-down list

Re: Connect values from sheets to a drop-down list

Re: Connect values from sheets to a drop-down list

Re: Connect values from sheets to a drop-down list

Re: Connect values from sheets to a drop-down list

Re: Connect values from sheets to a drop-down list

Re: Connect values from sheets to a drop-down list

Re: Connect values from sheets to a drop-down list

Re: Connect values from sheets to a drop-down list

Re: Connect values from sheets to a drop-down list

Re: Connect values from sheets to a drop-down list

Re: Connect values from sheets to a drop-down list

Re: Connect values from sheets to a drop-down list

Re: Connect values from sheets to a drop-down list

Re: Connect values from sheets to a drop-down list

Re: Connect values from sheets to a drop-down list

Re: Connect values from sheets to a drop-down list

Re: Connect values from sheets to a drop-down list

Re: Connect values from sheets to a drop-down list

Re: Connect values from sheets to a drop-down list

Re: Connect values from sheets to a drop-down list

Re: Connect values from sheets to a drop-down list

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Drop Down List hyperlink to sheets in another workbook

Bookmarks

Bookmarks

Posting Permissions