An input data sheet creation (method required)

[shaikhrulez]

I'm not sure if this is possible with excel or not, but after reading so many techniques & challenges posted by users, I thought to share my question. So here it is.

I have an excel worksheet with lots of rows and columns, what I'm trying to do is to make a single sheet for inputting data which can automatically feed in main worksheet when Computer Code (Column C) and the Location Code (Row 2) matches with that provided / selected in input sheet from a drop down menu using data validation. I've tried figuring out doing it by combining IF & VLOOKUP but failed.

I want the input sheet to be work as my main input / editing / feeding sheet for Main Worksheet; & when I finish feeding the data for a particular location it get saved in main worksheet. Moreover, when I select another Location code it offers me blank columns to feed data for that location. One last thing, when I re-select the Location for which I've already punched the data it shows me the data to edit & allow me to make necessary changes which I may need to do at a later stage.

I've attached the sample workbook. Please anyone look into this and post a solution. Thanks

 

[eibi]

Welcome to the forums!

I propose rethinking your request a little:

What if you change your Main Worksheet to display only one column displays at a time...this will function as your "input" worksheet, but it is also the master worksheet where all the data resides. All the columns are hidden except the one you call using the data validation dropdown.

Then, create a duplicate of this worksheet, where all of the columns are displayed -- reading its content from the data you enter on your input spreadsheet.

All it takes is a short macro, which I've created in the attached file.

(Remember, you'll have to enable the macro when you download the file).

Is this the functionality you wanted?

All best.

 

[shaikhrulez]

Welcome to the forums!

I propose rethinking your request a little:

What if you change your Main Worksheet to display only one column displays at a time...this will function as your "input" worksheet, but it is also the master worksheet where all the data resides. All the columns are hidden except the one you call using the data validation dropdown.

Then, create a duplicate of this worksheet, where all of the columns are displayed -- reading its content from the data you enter on your input spreadsheet.

All it takes is a short macro, which I've created in the attached file.

(Remember, you'll have to enable the macro when you download the file).

Is this the functionality you wanted?

All best.

Thank you so much Eibi, it is working fine and doing exactly what I was looking for. I've not worked with Macro or VBA Coding, although it is working fine but I would appreciate if you tell me briefly how did you get it done? I went through the VBA Code but didn't get much idea. Can you explain me how others columns are hiding when I'm selecting a particular location code?

I got around 85 locations (means 85 columns) and more than 600 rows, will this macro still work with that much of fields? One last question, what is the purpose of XXX - Input Sheet? why you've made this worksheet?

Thanks again bro for taking out time. I really appreciate.

 

[eibi]

Glad to hear that it's on the right track...

Please don't be intimidated. They are well worth the time it takes to get started. I'm a beginner myself with VBA.

Rather than post an explanation of the code here, I've inserted line-by-line explanations in the file and attached it for your review. Just open the attached file, press Alt+F11 and you'll see the code with comments inserted.

I also modified one line of the code (the WorsheetFunction.Match) to clean it up some.

The macro will have to be slightly modified to accommodate the 85 locations, and a few other issues will need tweaking (such as the data validation dropdown...) But the 600+ rows shouldn't require any changes at all...

I hope that my line-by-line explanation will be adequate for you to update the macro yourself, but please feel free to reply to this thread if you run into any bumps. I'm glad to help as best I can.

Regarding the XXX-Input Sheet -- this is not a new sheet, it was the Input Sheet from your original file. I left it in just in case you had other external connections that needed it...but I renamed it with the XXX so you would know that it wasn't being used any more. You can just delete it as far as I'm concerned.

 

[shaikhrulez]

Thanks again Eibi for all your help, this has been very helpful and I learned so much from the updated version as you explained each line of code in a very efficient manner.

One thing I'd like to know, While working on the sheet I noticed that 'Undo' option gets disabled as soon as I change the location code after feeding the data for that particular store. Why I can't use undo option after changing the location code? Is this happening because of the Macro we've used?

 

[eibi]

Correct -- when a macro runs, you lose the ability to "undo" previous actions in the file. That's one downside of using macros.

If you happen to run the macro and then realize a major mistake, the only solution I know is to close the file without saving changes; or to "Save as" with a new filename, so that you can go back to the previous version of the file and resurrect the old data.

 

[shaikhrulez]

Alright, so this is one of the downside of using macros, well I guess every good thing comes with its own shortcoming.

Thanks Eibi for all the time and quick responses These days I'm in a phase of constantly upgrading my knowledge & skills of Excel, I use Excel on daily basis and always try new techniques & ways to work smartly however, this one was my first experience of actually applying a Macro (VBA Code) in a worksheet. Learned a lot. I'll surely stay in touch with you for more help if required.