spreadsheet issues not sure if i'm using proper functions

F

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,"")
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
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" .
 
Upvote 0
<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
 
Upvote 0
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?
 
Upvote 0
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>
 
Upvote 0
if builder , series or model changes then the data in column C9:C66 changes appropiately ..



Thank you for looking at this problem
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
VLOOKUP referencing subsequent cells in table / Data Validation Error
Replies
3
Views
2K
Joe4
Joe4
G
  • Question
Costings using VLOOKUP based on multiple dropdown options
Replies
8
Views
2K
AlanY
A
H
  • Question
Embedded CONCATENATE function to specify table array for VLOOKUP
Replies
0
Views
2K
helamansanchez
H
A
  • Solved
Macro taking a long time to run
Replies
3
Views
726
andygame
A
C
  • Question
Ton of script in case anyone wants to review, maybe something useful to someone
Replies
0
Views
2K
cizzett
C

Forum statistics

Threads
1,214,651
Messages
6,120,738
Members
448,988
Latest member
BB_Unlv

Share this page

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
Back
Top