spreadsheet issues not sure if i'm using proper functions

feather1000

New Member
Joined
Feb 18, 2009
Messages
14
background :

workbook with 6 worksheets of input
1 worksheet with proper inputted data summary

sheet 1 - summary worksheet
cell: A2 drop down menu (different builder)
cell: A3 drop down menu (builder specific series)
cell: A4 drop down menu (series specific models)

_100 series spreadsheet of different models (61 different items x 26 models)
_500 series speadsheet of different models (61 different items x 13 models)

3 other speadsheets same info just different builders

what i'm after is that once specific builder,series,model are determined the data from the specific spreadsheet goes into appropiate "cell of sheet 1". i've tried to use macro but it appearently didn't copy the drop down menu info to get the data from the appropiate sheet. right now i've been able to use this formula for one cell but have to do find:replace as needed.

=IF(AND(A2=M12,A3=M13,A4=M28),series100a!D$5,"")
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

feather1000

New Member
Joined
Feb 18, 2009
Messages
14
Since no one has left a reply i'm going to assume that i've missed some inportant information for this problem.

both _100 and _500 series models have same data ( ie: recepticles, fixture outlets, etc). in same blocks. what i'm really after is after builder , series, model chosen the relevant data of specific model is transfered to "sheet 1" .
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,917
Can you give an example of the data you are inputing and the result you want from that data.

What is the name of the sheet on which the result data is found?

The function INDIRECT might be useful.

If you want to post a spreadsheet image, this utility helps. http://www.excel-jeanie-html.de/index.php?f=1
 

feather1000

New Member
Joined
Feb 18, 2009
Messages
14
Excel Workbook
ABCDE
2
3LOT:
4
5
6LIGHTING:
7DESCRIPTIONMODELTOTALDIFFERENCE
8
9FIXTURE OUTLETS #VALUE!
10KEYLESS LAMPHOLDERS #VALUE!
113" POT LIGHTS0
124" HALO WITH GIMBLE0
Sheet1
#VALUE!



this is sheet 1
 

feather1000

New Member
Joined
Feb 18, 2009
Messages
14

ADVERTISEMENT

<html><head><title>Excel Jeanie HTML</title></head><body>
Excel Workbook
ABCDEFG
4ARLINGTONBANTRIDGEBANTRIDGE IICANTERBURYCANTERBURY IICANTERBURY V
5FIXTURE OUTLETS131821131515
6KEYLESS LAMPHOLDERS888666
7PULL CHAIN LAMPHOLDERS000000
series100a

</body></html>

_100 series worksheet
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,917
I still don't understand what you want to be returned.
What inputs into Sheet1 A1:A3 cause what results?
From which of the _50 series or _100 series sheets would that data come?
 

feather1000

New Member
Joined
Feb 18, 2009
Messages
14

ADVERTISEMENT

A2: drop down menu consisting of:
Mapleton (Builder A)
Courtyard (Builder B)
Barry (Builder C)

A3: drop down menu specific to builders different "series of homes"

Mapleton 100 and 500 series homes
Courtyard Loft and 1 floor homes
Barry custom homes

A4: specific models of homes as what you see in the series _100a above.

Once determined from the "drop down menus" the appropiate info goes into given spot on "sheet 1"
Ie:
Excel Workbook
ABCDE
2Mapleton
3_100LOT:
4BANTRIDGE II
5
6LIGHTING:
7DESCRIPTIONMODELTOTALDIFFERENCE
8
9FIXTURE OUTLETS21-21
Sheet1
#VALUE!
</td></tr></table></td></tr></table>
 

feather1000

New Member
Joined
Feb 18, 2009
Messages
14
if builder , series or model changes then the data in column C9:C66 changes appropiately ..



Thank you for looking at this problem
 

feather1000

New Member
Joined
Feb 18, 2009
Messages
14
maybe i should be thinking of Vb scripting: something to this effect:

If A2 = "mapleton"
If A3= _100
If A4= "Arlington" then input data from _100seriesa!B5:B66 (into "sheet 1" cell c9:c91)
If A4 ="Bantridge" then input data from _100seriesa!c5:c66 (into "sheet 1" cell c9:c91)
If a4 = "Westgate III" then input data from _100seriesa!AI5:AI66 (into "sheet 1" cell c9:c91)

If A3 ="_500"
If A4 = "ALTURUS" then input data from _500series!B5:B66 (into "sheet 1" cell c9:c91)
If a4 = "ALTURUS III" then input data from _500series!c5:c66 (into "sheet 1" cell c9:c91)
If a4 = "WOODLAND" " then input data from _500series!X5:X66 (into "sheet 1" cell c9:c91)

If A2 = "Courtyards"
If A3= _1_floor"
If A4 = "Model Elm" then input data from courtyard!B5:B57(into "sheet 1" cell c9:c91)
If a4 = "Model Willow" " then input data from _100seriesa!c5:c66 (into "sheet 1" cell c9:c91)
If A3 = "loft"
If A4 = "Model Birch 1L" then input data from courtyard!n5:N57 (into "sheet 1" cell c9:c91)
If A4 = "Model HICKORY 4L" then input data from courtyard!Y5:Y57 (into "sheet 1" cell c9:c91)
If A2 = "Barry"
If A3 "custom"
If A4 = "Cambridge" " then input data from cambridge!B5:B57(into "sheet 1" cell c9:c91)
 

feather1000

New Member
Joined
Feb 18, 2009
Messages
14
Right now i'm using data validation for the pop down menus.

=IF(AND(A2=M12,A3=M13,A4=M28),series100a!D$5,"")


A2 = M12 (mapleton)
A2 = N12 (courtyards)
A2 = O12 (Barry)

A3 = M13 (100 series)homes
A3 = M14 (500 series)homes
A3 = N13 (1 floor)homes
A3 = N14 (loft) homes
A3 = O13 (custom)

A4 = different model homes for specific builders

of course the back half of statement must change as per what happens in column A3

.......),series100a!D$5,"")
that would end up being ,series100a
series500
1floor
loft
custom

thelast part d$5,"") part would change with the specific model rangeing from B$5,"") to AI$5,"") all for the 100series homes



any help would definately be much appreciated or should all this be done with Visual basic if so then could i be pointed in write direction.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,241
Messages
5,600,499
Members
414,383
Latest member
konmtu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top