Autofill data by Dropdown menu

**JethroJohn** · 04-05-2011, 02:23 PM

Hi

I am trying to perform the following task with excel...

I have created a dropdown menu... what i would like to do is make the data in a range of cells change depending upon what is selected in the dropdown menu... For example... i have 2 columns... one that says quantity, the other says price. In the dropdown menu there is 2 choices... concrete blocks and bricks. So when i select concrete blocks i would like the price and quantity to show the relevant figures which will be drawn from another spreadsheet. Is this possible??

Thanks

John

**Marcol** · 04-05-2011, 02:59 PM

If you are thinking on these lines, (all lists can be dynamic), we can offer a solution if you can give us a sample workbook with lists of products, sizes, manufacturer/supplier, etc any prices should be falsified.

Cheers

**JethroJohn** · 04-05-2011, 03:42 PM

Hi Marcol

Thanks for the reply... Thats not really what i was looking for though. I have already made the dropdown menus, thats not a problem... Want i am trying to figure out, i dont even know if its possible to do.... Say in cells A1, A2, A3 i have price quantity and availability... in A4 i have a dropdown menu with bricks, concrete blocks and paving slabs (these are just examples). When i choose an item in the dropdown menu... say "bricks"... i want cells A1, A2 and A3 to go and fetch the specific data that is relevant to that product and display it in those cells.... then when i change the dropdown menu and select "paving slabs" the data in A1, A2 and A3 will fetch and display the data for that specific product... I have to do this myself and understand how it works as it is part of University project.

Thanks again and hope you can help....

John

**Marcol** · 04-05-2011, 05:53 PM

I'm fishing in the dark here.
Is this more what you have in mind?

This demo is a bit crude but you haven't given me a lot to go on.

**JethroJohn** · 04-06-2011, 02:02 AM

Hi Marcol,

Thanks again for trying to help me with this. I really appreciate it.

Yeah what you have done is the king of thing i want...

Your right it would be easier if i just let you have a small section of what i am working on. Its actually a co-efficients chart for my acoustics assignment. I have made a dropdown in the cell G5. I did try to get i to pull the list from sheet 2 by using a named range but it didnt work and was only showing 3 items in the list instead of 5?? Dont know why. So to make it easier i have put the list on the same sheet.

In cells A5:F5 the figures shown are for concrete blocks from the table below. What i need to happen is, when i select on of the materials in the dropdown list, the figures in cells A5:F5 will change to the corresponding values in the table below. There is only 5 items in the list of materials. The actual finished project will have much more...

Thanks again for your help

John

**Marcol** · 04-06-2011, 03:26 AM

That's a bit easier, no guesswork!
In A5

Please Login or Register  to view this content.

Drag Across to Column F

See the attachment for the formula using two sheets

It would be better, in your final sheet, to use Dynamic Named Ranges.

Hope this helps.

**JethroJohn** · 04-06-2011, 08:00 AM

Thanks mate...

I can see it works fine in yours but i cant seem to get it to work in mine. I have copied the formula exactly and modified the cell numbers. I have sent you the entire spreadsheet. You can see where i have attempted it in cell W4. Can you tell me where i have gone wrong??

I cant get it to work from another sheet either but i am not really worried about that...

Also, there is something else i was wondering if you know how to do...

Can you see the Basstraps and the Acoustic panels. They have a dropdown menu that selects which wall they are on ie End Wall, Side Wall... They all correspond with surface areas to the right of them in column S

What i wanted to do is this... Say on Basstrap 1, if it is set to Left Wall, then the area of that basstrap needs to be subtracted from the area of the Left Wall... If it set to Ceiling then the area needs to be subtracted from the area of the ceiling... Does that make sense?? There also may be multiple things that need to be subtracted from any given surface...

Is that do-able??

Thanks again for your help....

John

**Marcol** · 04-06-2011, 10:58 AM

Okay Where do we start?

1/. Create 2No Dynamic Named Lists

Material
Refers to:=
=OFFSET('Control Room'!$AC$4,0,0,MATCH("*",'Control Room'!$AC:$AC,-1)-3,1)

=OFFSET(reference,rows,cols,height,width)

reference, is the top left Data Cell (Excludes Headers)
When you build this you need only refer to the cell if you are on the active sheet, excel will add the sheet name

rows,cols is the offset from the reference cell 0,0 therefore includes this cell.

MATCH("*",'Control Room'!$AC:$AC,-1)-3,1)
The MATCH Function returns the last row number with text relative to the whole column in this case, the -3 adjusts this to return the height of the list relative to the reference cell and as this is a single column list the width is one.

Similarly
Absorption_Co_Efficients
Refers to:=
=OFFSET('Control Room'!$AC$4,0,0,MATCH("*",'Control Room'!$AC:$AC,-1)-3,8)

The MATCH Function is the same as for Materials this means the list and table will always have a matching height.
The difference is that this is a table, in this exercise is 8 columns wide.

2/. Materials can now be used for the Data Validation in AA4 down
Allow:= List
Source:=
=Material

3/. The original static formula can now be written
In U4

Please Login or Register  to view this content.

Drag Across to Z4 and then Down as required.
This reduces the ammount of critical Anchors required to keep the formula accurate if the formula is dragged.
The COLUMN function returns the column number of the column it refers to and this is selected to match the first column in the named range/table Absorption_Co_Efficients that we need, in our case this is 3 therefore C is the column to refer to.
By using COLUMN(C$1) we can drag the formula across and the value will increment therefore returning the correct column from our table.

A similar "trick" can be used to increment down by using ROW($A1) for example.

4/. Don't put merged cells in Data Tables/Lists the just give you grief, in fact design your sheets without merged cells, it will serve you well in the long run.

You should now be able to access these ranges from any sheet by refering to the name, and add/subtract from your data without changing any formulae, just avoid blanks in the Reference Cell Column, it will only tolerate blanks to a limited degree.

Use Help in Excel to understand how each Function works and Google for Dynamic Named Ranges, DonkeyOtes' signature has some of the best links

Hope this rant helps.

**JethroJohn** · 04-06-2011, 11:46 AM

Thanks for that!! WOAH... Thats all a bit too advanced for me... But you got it all working... Brilliant.

One last question. Ever since i openned your new, edited spreadsheet... it is really slow... If i select one of the dropdown menus it takes about 10-15 seconds for the page to refresh with the new results... Is that normal??

Cheers dude

John

**Marcol** · 04-06-2011, 12:07 PM

Seems normal to me, pretty well instantaneous.
I opened the attachment again and it seems okay too, sometimes working through Temporary Internet Files can slow things a bit, but not this time.

If the problem persists, try copying the worksheet to a new workbook, and see if the copy runs any better for you. This often clears mystery "bugs" especially with named ranges and conditional formatting.

Stick with the project, you can compare the different approaches to the problem as it progressed, that should be good for learning these more advanced formulae.

Check the file size, it should only be about 30kB.

Hey you aint seen nuthin' yet, wait 'til the Big Guns arrive, they have some truely amazing stuff.....

... nae "Exelchemy" indeed.

**JethroJohn** · 04-06-2011, 12:22 PM

Nope... its not having it mate. I've tried pasting it to a new worksheet, i've tried downloading it again, i've even tried openning it with Pages... i cant even enter a simple numeric value into a cell without it taking at least 10 seconds to refresh the page...

Annoying!!!

John

PS maybe the file got corrupted when i downloaded it or something...

Can you try emailing it to me?

djvinyljunkie at me dot com

thanks

**jwright650** · 04-06-2011, 12:38 PM

just a note:
The spreadsheet is pretty fast for me....(neat sheet)

**JethroJohn** · 04-06-2011, 12:54 PM

Hi John....

Not for me... its proper slow

Damn!!! What could be wrong with it ??

I've opened the old one before Marcol done all that fancy stuff.... which is the dogs Nads by the way....

And that one is as fast as ever....

grrrrrrrrr

John

**Marcol** · 04-06-2011, 01:04 PM

When I said copy to a new workbook, I meant this

Right Click on the Sheet Tab
Then > Move or Copy.....
To Book:= (new book) from Drop-Down then check the "Create a Copy" checkbox
Press OK.

No pasting involved.

I just noticed you are using a mac with 2008 that might be the problem I don't know, I don't use macs, maybe someone with a mac could try it and see.

**JethroJohn** · 04-06-2011, 01:12 PM

No that didnt work either... thanks for trying tough.

John

**Marcol** · 04-06-2011, 01:18 PM

I've put out a call for someone with a mac and 2008 to give it a try.

See what happens.

**JethroJohn** · 04-06-2011, 01:31 PM

Cool, thanks man!!! I just tried rebooting the computer and restarting excel with no other programs running and that hasnt made any difference, I've got a macbook pro with dual core processors and 4G of ram so its surely not that??

**Marcol** · 04-06-2011, 01:35 PM

I don't know but there are issues with mac 2008, I know that that version can't run VBa macros, just maybe it doesn't like some native excel formulae, let's wait and see.

**JethroJohn** · 04-06-2011, 01:47 PM

OK... Hopefully someone will know!!

Weird though cos the first one you sent me.... The one i called HelpmeMarcol...
that one works fine.... Anyway, let me know what you find out...

**Marcol** · 04-06-2011, 01:59 PM

Here's a thought
Try this in AA1

Please Login or Register  to view this content.

And this in AA2

Please Login or Register  to view this content.

The answers should be 43 & 42 respectively.

Other checks are VBa and mac 2008 can't run them.

**JethroJohn** · 04-06-2011, 02:10 PM

Yeah, just did that... answers were as you said they would be. 43 and 42

**Marcol** · 04-06-2011, 02:24 PM

Okay it was just a thought that the dynamic lists might have been running past the last text entry in AA that would have meant the formula was calculating millions of times each time it was called, it was a slim and unlikely thought, that the mac read blanks differently from a PC.
obviously not.
Got to go will look back late tonight.

**romperstomper** · 04-07-2011, 04:05 AM

I've just tested it in Excel 2008 (12.2.8) and there is no slowdown - updates in under a second.

**JethroJohn** · 04-07-2011, 04:21 AM

Hey Romperstomper, Thanks mate... Was that on Windows or mac??

John

**romperstomper** · 04-07-2011, 04:57 AM

Mac - Excel 2008 is a mac only version.

**JethroJohn** · 04-07-2011, 09:11 AM

Well i've just got back from uni and i opened the file on one of the iMacs there, and its doing exactly the same thing there... so what can it be??

Weird...

**romperstomper** · 04-07-2011, 09:22 AM

Dunno - which specific version of 2008 are you using? Fully up to date?

**JethroJohn** · 04-07-2011, 11:30 AM

Okay Romper Stomper... good call.... i reformatted my hard drive a few months ago and when i reinstalled office... i didn't to the updates... I just done them and it is working fine now...

Thanks to everyone who helped me out on this

:o)

**Marcol** · 04-07-2011, 02:30 PM

Thanks for the help out romperstomper

Autofill data by Dropdown menu

LinkBack

Thread Tools

Rate This Thread

Display

Autofill data by Dropdown menu

Re: Autofill data by Dropdown menu

Re: Autofill data by Dropdown menu

Re: Autofill data by Dropdown menu

Re: Autofill data by Dropdown menu

Re: Autofill data by Dropdown menu

Re: Autofill data by Dropdown menu

Re: Autofill data by Dropdown menu

Re: Autofill data by Dropdown menu

Re: Autofill data by Dropdown menu

Re: Autofill data by Dropdown menu

Re: Autofill data by Dropdown menu

Re: Autofill data by Dropdown menu

Re: Autofill data by Dropdown menu

Re: Autofill data by Dropdown menu

Re: Autofill data by Dropdown menu

Re: Autofill data by Dropdown menu

Re: Autofill data by Dropdown menu

Re: Autofill data by Dropdown menu

Re: Autofill data by Dropdown menu

Re: Autofill data by Dropdown menu

Re: Autofill data by Dropdown menu

Re: Autofill data by Dropdown menu

Re: Autofill data by Dropdown menu

Re: Autofill data by Dropdown menu

Re: Autofill data by Dropdown menu

Re: Autofill data by Dropdown menu

Re: Autofill data by Dropdown menu

Re: Autofill data by Dropdown menu

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions