Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update a spreadsheet based on a drop-down selection
I have 3 spreadsheets in my workbook. Sheet1 has a dropdown w/all the months
Jan to Dec. Sheet2 has a standard Income Statement w/the months listed across Row 1, Jan to Dec (Jan starts in Col A and Dec is in Col L) and the same data listed below each month down the columns. Sheet3 is where I would eventually like to paste values copied from Sheet2 into cell range A1 down to A10. This is what I would like to do: when Jan is selected from Sheet1 dropdown I would like 10 data copied from Sheet2, Jan column (cells: A3, A4, A7, A9 etc.) and the values pasted into Sheet3 range A1:A10. Likewise if July or Aug was selected from the drop down I would like to paste the values from July or Aug into the same Sheet3 A1:A10 range. I know how to create macros but when the selected month changes I get lost. You guys have help me work w/VBA in the past so I am open to using VBA. Thanks for taking a look. -- Dan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update a spreadsheet based on a drop-down selection
Hi Dan
It doesn't really need a vb solution for this one. In Sheet 3 if you make a reference to your dropdown so cell A1 of Sheet 3 has the same month selected ie Jan, you can then use a HLookup to gather the data based on the month selected in A1 of sheet3. Then place this code in A2 of Sheet3. =HLOOKUP(A1,Sheet2!$A$1:$L$10,3,0) It will pick up the value in the third row of Sheet3. Change the number 3 in the lookup formula to get other figures ie 5 will pick up the data in the 5th row. The Sheet2!$A$1:$L$10 may need to be increased for your purposes. Take care Marcus |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update a spreadsheet based on a drop-down selection
Marcus,
Worked great. Thanks for helping this Newbie. -- Dan "marcus" wrote: Hi Dan It doesn't really need a vb solution for this one. In Sheet 3 if you make a reference to your dropdown so cell A1 of Sheet 3 has the same month selected ie Jan, you can then use a HLookup to gather the data based on the month selected in A1 of sheet3. Then place this code in A2 of Sheet3. =HLOOKUP(A1,Sheet2!$A$1:$L$10,3,0) It will pick up the value in the third row of Sheet3. Change the number 3 in the lookup formula to get other figures ie 5 will pick up the data in the 5th row. The Sheet2!$A$1:$L$10 may need to be increased for your purposes. Take care Marcus |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hide row(s) based on drop down selection | Excel Discussion (Misc queries) | |||
controlling a form drop down based on selection of another drop down | Excel Programming | |||
Update cell immediately upon selection from drop down | Excel Programming | |||
Update cell immediately upon selection from drop down | Excel Programming | |||
Update cell immediately upon selection from drop down | Excel Programming |