Use ComboBox value to reference table and dynamically output corresponding column data

I've tried a few different things but I can't seem to wrap my head around this -- Or maybe I'm just showing my just how infrequently I use excel (It's true what they say, use it or lose it -- in this case, I feel like I've lost it.)... It's disconnected in my head, but I can sorta see it -- committing anything to code, however, has proved useless thus far.

I feel like a tool for asking, but would anyone be so kind as to provide suggestion(s) on how I might implement a combobox on sheet2 to reference column values on sheet1 and dynamically output only the corresponding adjacent column values back to sheet 2?

I've attached an example of my data... Hopefully it makes some kind of sense.

Template example.xlsm

It looks like the combobox needs the entries to be in a column not in a row

I have modified your template for you

Hey, thanks for the reply, jaslake -- Yes, the row menu names will remain static... The columns will remain static for the most part, too. Only the grid data may/will change... If anything else changes, it shouldn't be much of a problem to adapt, I've just not had any success with my previous attempts -- The grid I attached previously is much smaller than the data set I'm trying to sort out, which represents about 35 columns and 500 rows between 15 servers.

WOW! You made short work of my little brainbuster, jaslake!

I haven't yet applied it to the full-size dataset, but it looks like your code might do the trick!!! I'll study the code and try appending it to my sheet tonight and let you know how it works out.

Thank you for your expertise!!!

Just out of curiosity, was the addition of the 'Data' sheet just a redundancy to ensure that sheet1's data remain untouched?

HAHAHAAAA... I totally missed that the first time I looked at it.

Jaslake,

Thank you, again, for your assistance with this -- I was pulled away for some other work over the last couple of weeks and wasn't able to revisit this until the last day or two...

That said, your code did the trick for the most part and I was able to change a few elements and get it to work, however I discovered that this layout requires almost as much scrolling as the previous version... Still, it's a vast improvement over trying to maintain a visual lock on each row/column intersection.

In the interest of improving this further, I wonder if I might ask your assistance again -- I've attached an updated version with a visual to give you an idea how the output would ideally be presented -- please let me know if you think it's possible, or if you have any suggestions on how the original table might be better conceived/executed/managed.

Many thanks...

Jaslake,

No, for now, there will be at least three servers, but in the future, potentially more. Currently, the menu names are roughly the same across each server and there are, currently, a maximum number of 8, however there may be more in the future and some menus may or may not be displayed based on the job titles selected. If it's possible to make the output dynamic in that those empty menus won't be shown in the formatted output, then great... If not, then no big deal -- the important thing is the horizontal format.

Ultimately what I'm considering is, leaving the original tables alone on their respective tabs (3 on the sheet this applies to, not including what is named sheet 2 and data on our workbook (Template example v1-2.xlsm)), consolidating them to one sheet and creating the data sheet using prep_data (Module2) to make the data sheet and then pulling from that for the formatted horizontal output, if that makes any sense...

Thanks for taking the time! your efforts are truly appreciated...

I don't have one prepared, unfortunately... To simplify it, though, envision three tabs, one for each server rather than consolidated as sheet1 now looks...

In the case of 'Template example v1-2.xlsm', I only have two servers represented on sheet 1; To further clarify, if you were to recreate it with the current file, one tab for each -- server1 on one tab and server2 on another would be how it would be laid out. Then sheet3 where the combobox resides, and the fourth to be created by the prep_data module. That bit you really don't have to worry about -- I'm just trying, at this point, to get a frame of reference for how I might code the horizontal layout...

Thanks!

I think you misunderstood -- I wasn't asking you to rebuild it... lol

...don't like moving targets

I can appreciate this sentiment -- I assure you not much is going to change with sheet 1, just the addition of the third server. If I can understand how you would adapt the current vertical output to the suggested horizontal layout on sheet2, then I might likely be able to adapt and apply your logic to the third and any additional servers I might add in the future...

I'll try and come up with something soon -- My wife's PC crashed and I'm currently knee deep in repairing the HDD and building a hardware agnostic image for it... Since I use it as our alarm clock and I return to work tomorrow, it's imperative I focus on it at the moment, before I can jump back into this...

Thanks again for your assistance, and patience.

I lucked out with the hard drive and was able to restore from a previous image I captured, run backups and verify everything in record time to try and get back to it to complete your request -- only to find you were done! WOW! I truly envy your abilities for engineering code, jaslake. I dream of having the spatial capacity vba requires -- To execute in the amount of time you just did, twice I might add, completely blows my mind... I see things in my head, put pen to pad (so to speak) to try and figure it out, but in the end, it's usually days before I've worked it out. Inexperience with a side of split-focus, I suppose...

Anyhow, I checked your solution and everything appeared to work flawlessly... Unfortunately, I hit a snag while playing with options from the Sheet 1 matrix to see if it was calculating properly -- I found that if you remove all values from just one menu, it returns a "Runtime error '1004': Application-defined or Object defined error". Is there any way to avoid this?

Anyhow, brilliant imagination, my friend... Just.Brilliant.

Hi Jaslake!

Sorry for the delay -- I tried to adapt it myself, but I wasn't able to figure it out, entirely... I thought I was very close a couple of times, but I ended up accidentally overwriting my work or crashing excel and losing the changes.

I modified the table data with more "logical" values for error checking purposes and fleshed it out a bit more, but not alot of code -- Mostly just to make it easier to see what's happening in the output, since I can't seem to figure it out.

I'm currently left with several anomalies -- Some of the data is being mixed in the output table, some isn't showing up at all and offsets the remainder of the data being copied, and my addition of the third server and inability to successfully hide the blank rows effectively, is subsequently causing the EntireRow.Hidden statement to hide data, rather than blank rows. I've tried multiple times, for multiple hours to get one or all of these things resolved on my own, but end up creating more problems than I do fixing any, at this point... The only time I came close to fixing it was when I had it setup to display the Server names and menus, as well as the data dynamically, much the same way you had provided in the last module, but I managed to crash excel and lose the changes...

Anyhow, if you have the time to provide any clues, your assistance would be welcome and very much appreciated

Couldn't sleep -- my mind is racing... especially now.

I played with it a little and your code looks fantastically complicated...

If I manage to break it, I'll let you know, but I have very little doubt that I won't be able to build off of it. I loved the backup code example, too -- got a good laugh outta that... Very good suggestion!

I DO love gettin schooled by gurus -- Rest assured, I've learned (and I'm sure I'll continue to learn) quite a bit from your code.

Thank you, again, for your most excellent instruction, Jaslake...

Mind.Blown.

Looks great! I'll look over your amendments and fixes if I have the time tonight at work...

Thanks again, Jaslake!

Took me a while, but I think it was the fact that I had overlooked Find("Server*", ) when I linked the original spreadsheet contents into the workbook that was causing my problem, compounded by the fact that the original sheet's format wasn't "exactly" the same as what we'd been working with.

I did, however, introduce a bit of latency that I'm not sure how to address with my recent changes and I was wondering if you might take a look and let me know if you have any suggestions... I'm at a significant disadvantage in that I'm not at all versed in any of the debugging tools inbuilt to VBA. I've tried using them, but working with them without much experience, I liken to wearing a blindfold in a dark room without walls and legos scattered about the floor.

Not sure why the filesize is almost a MB -- maybe the file links?

I rebuilt the workbook in a fresh workbook and it reduced the filesize by another 300K or so... Once I ran the code, filesize increased to what it is now... Really, filesize isn't a huge concern -- I realize anything I add to it is only going to increase the size and or processing time and it's probably the linked data and formulas contributing to the size when compared to your last revision.

I found that using linked data was adding 0 values to the linked data -- I'm not sure if that had any overall impact on the misalignment I was experiencing... I finally got everything working with the below code, but then in playing with the sheet realized that the 0 values might not be doing anything to the output at all, so I tried commenting out the following code in Module 2.Prep_Data

Please Login or Register  to view this content.

but found when commented out would result in Module 3.Finish_Up

Please Login or Register  to view this content.

erroring out at .Cells with 'Subscript out of range'. It works when it's un-commented, so I'm not sure exactly what it is that's not within "range" when the code isn't used since it's just an additional With expression...

Anyway, the latency's not a huge concern either -- more of a curiosity as to whether or not there would be a better way of addressing the calculation speed or error or both. I'm fine with everything in it's current state. In any event, I'm just soooo happy to be in a somewhat completed, if not just a "working" state with this little project.

I've attached, what should be, the final revision (with the exception of some additional aesthetic modifications) -- at some point I may come back to it and clean it up further, but now since everything just works at this point I'm going to leave it alone. Thank you again, Jaslake, for your patience and excellent assistance!

*EDIT*

I just refreshed the page and noticed that you had replied... I'll take a look at your suggestion and let you know -- in the meantime, I'm marking this as solved since it's, more or less, complete and I'm happy with the overall result. Thanks!

The reason I linked the data is because the original dataset being linked resides on a sharepoint server, which, if you're not familiar, requires a check-out/check-in process for accessing documents... To reduce the potential for problematic behavior, accessing the external data in this way seemed to be the simplest solution.

Morning, Jaslake... I noticed something tonight when trying to update the links to the master workbook -- the menu names on sheet 4 aren't being pulled from Data1 as I had originally thought -- They're actually being created on the fly by a bit of code in the 'Finish_Up' sub:

Please Login or Register  to view this content.

I've spent a couple of hours off and on again trying to determine where I might be able modify your code to achieve my desired result, but I haven't been able to figure it out... It "looks like" the 'test' sub might be copying and pasting the server name value to Sheet4, but I can't figure where to integrate the menu names into the code -- Thinking about it, that approach might not work since it's there's more menu names than there are servers, which leaves it to the Split Cell "C"/"D" data and somehow integrating an offset with those (if that's even possible), maybe into the existing Arange(?). I know I've probably worn you out with this and I apologize for reviving this admittedly completed thread, but could you shed some light on how I might fix this? Without the menu names, the output, unfortunately, isn't of much use...

Apologies and thanks...

It seems that Column B on Data1 isn't being used in Module 3's 'test' sub and the menu names aren't being copied to the output on Sheet4. The menu names in the current output appear to be being created or possibly overwritten by the code in my last comment... I've tried commenting out those lines of code in my worksheet and the data is looks like it's not being copied at all -- with those lines commented out, the resulting output on Sheet4 lists the server name and the data from columns C and D in Data1, without menu names.

Not sure if my previous reply made any sense -- to further clarify, the menu labels that are offset 1 row below each server name on Sheet4 appear to be generated by concatenating the first line of each of column D's value in the previous set value (Module2.test.Arange) pasted to Sheet4 -- the final value being something like ColumnD.value"&/&ColumnD."value" instead of ColumnB.value.

Below is where it looks like where the copy "should" be happening with the amended code in Module2.test you've provided...

Please Login or Register  to view this content.

I'm "slowly" putting it all together in my head -- I guess, at this point, I'm just not clear on how to integrate Data1.ColumnB's value into your code, that is, provided my observations are correct -- I've tried a couple of ways, and nothings worked yet.

I had added that code when I was testing to determine if the cell blanks (either "" or 0 values) were being read as values and throwing off the calculation and causing my aformentioned misalignment, I just hadn't removed it yet...

Notes in the attachment...

Data updated and comments added

So I took a look at the change(s) you provided -- I get the feeling I've either ticked you off or you're having a funny at my expense. lol

If the latter is true, then, good one -- I got a good laugh from it (For a few moments, I was "almost" convinced that a comma was the only thing that was preventing the code from including the needed data from Column B) If the former is true -- I sincerely apologize, that was never my intent. If I'm wrong altogether and you misunderstood my last explanation, I've provided yet another file that might clarify things further...

In any case, thanks for your time and your help.

Thank you for this, Jaslake... This seems to have done the trick!

Please Login or Register  to view this content.

I kept looking at the line above it

Please Login or Register  to view this content.

but the (RowOffset + 5, ColumnOffset) didn't make sense to me. It still kinda doesn't since +5, in my mind, would imply offsetting +5 rows -- given this, your addition after that (RowOffset +4, ColumnOffset), then, seems counter-intuitive with respect to placement of the output with the pastespecial directive... I'm sure I'll figure it out, eventually...

Thanks for your patience with me, Jaslake. I haven't had alot of time to spend towards learning and working with the debugging tools (I didn't assign myself the numbnuts handle without good reason. I hope that was apparent...) -- I've learned a little about it over the course of our correspondence and to be sure, with each iteration, I've learned something new about VBA -- One of these days (hopefully sooner, rather than later), I'll be able to dedicate some time to learning how to properly use them -- speaking of which, do you have any recommendations for third party open training resources for debugging VBA, aside from the usual suspects (MSDN, Ozgrid, etc)?

Thanks again for your time and instruction -- I'll continue to apply what I've learned from you for many years, I'm sure.

Hey Jaslake!

I wonder if I might ask your assistance one more time -- I've not been able to come back to this until recently having been ill or otherwise busy -- I've been using it somewhat regularly but I've decided to clean up one last remaining problem.

I discovered shortly after our last correspondence that adding any servers where that server's output may not apply to the dropbox selection results in some data being carried over from the clipboard. Having dealt with that particular problem, I'm left with a server name that takes the cell formatting in the finish_up sub and subsequently put's the text partially off screen... Ultimately, I'd prefer to clear that server name's cell if no data exists for that server, but I'm perplexed as to how to accomplish it. I've tried several different ways to either clear or delete it using either the test or finish_up subs, but end up clearing or deleting the first cel.address for all of them or not affecting any data at all.

If you have a moment, and don't mind, could you take a look and offer your thoughts/insight?

In it's current "saved" state, Server 2's out put on sheet four is non-existent. I added some error trapping in the 'test' sub to prevent it dropping the previous paste value

Please Login or Register  to view this content.

When I added the On Error Resume Next statement, it would paste the last value from Server 1 into Server 2. If you remove the first line of the above code, you'll kinda see what I mean... In stopping it pasting that value, the value 'Server 2' on sheet 4 is formatted by the 'finish_up' sub and subsequently ugly to the resulting output for the entire page and not even necessary. What I had tried didn't work in removing it:

Please Login or Register  to view this content.

I tried placing the above before the last 'With' statement in the 'finish_up' sub (apologies, that's the only iteration of my attempts that I've saved -- I've tried various 'If/Then' one liners in various places throughout both the 'test' and 'finish_up' subs) but it blows away the 'Server 1' field and subsequently throws off the formatting of the entire sheet. In the above, I figured since there's data in next row/next column of all the other fields I wanted to keep, it would ignore those cells cell and loop until it found one where that matched the criteria; That's what most of my attempts had been generally based on -- I didn't want to blow away your "X" placeholder, so I used the cell next to it for reference in most of my attempts, but with disastrous results most of the time...

Jaslake,

Based on preliminary testing, your additions appear to do what I need them to; I'll merge them into my data later tonight when I'm at work to confirm...

Thank you, yet again, for your excellent assistance!!!