Discussion
Hoping this is a simple one but here goes.
I have two columns of data - Column A contains 4 digit codes; Column B is the full ERP company code that corresponds.
I want to be able to click on any one of the codes in column A (as if it were a button) and the result of that click to be the corresponding code in Column B populates in a separate "control" cell.
The goal is being able to change a data header in a separate sheet quickly - this will link through to the "control" cell.
Not sure if this makes sense!
I have two columns of data - Column A contains 4 digit codes; Column B is the full ERP company code that corresponds.
I want to be able to click on any one of the codes in column A (as if it were a button) and the result of that click to be the corresponding code in Column B populates in a separate "control" cell.
The goal is being able to change a data header in a separate sheet quickly - this will link through to the "control" cell.
Not sure if this makes sense!
Legend83 said:
Hoping this is a simple one but here goes.
I have two columns of data - Column A contains 4 digit codes; Column B is the full ERP company code that corresponds.
I want to be able to click on any one of the codes in column A (as if it were a button) and the result of that click to be the corresponding code in Column B populates in a separate "control" cell.
The goal is being able to change a data header in a separate sheet quickly - this will link through to the "control" cell.
Not sure if this makes sense!
I'd suggest a third column, make it column A. Place a '1' in the corresponding column A cell and do a vlookup on your separate control cell, looking for the '1', and returning the results of column C.I have two columns of data - Column A contains 4 digit codes; Column B is the full ERP company code that corresponds.
I want to be able to click on any one of the codes in column A (as if it were a button) and the result of that click to be the corresponding code in Column B populates in a separate "control" cell.
The goal is being able to change a data header in a separate sheet quickly - this will link through to the "control" cell.
Not sure if this makes sense!
To do it as a button, you're looking at VB.
Legend83 said:
I want to be able to click on any one of the codes in column A (as if it were a button) and the result of that click to be the corresponding code in Column B populates in a separate "control" cell.
Would it be possible to select the short code from a drop down, and return the long code next to it?The closest solution I believe you can get without coding it in VB would be to create a ‘LOOKUP’ formula and you would need to type or select the code from a dropdown fin an input cell which in turn would sent the corresponding company code to your ‘control’ cell.
I’m not sure if this would make life any easier for you?
You could also do a large combination of hyperlinks via the 'place in this document' but this would be extremely time consuming and subject to error if there was any future updates.
I’m not sure if this would make life any easier for you?
You could also do a large combination of hyperlinks via the 'place in this document' but this would be extremely time consuming and subject to error if there was any future updates.
Legend83 said:
Thanks for the suggestions guys I'll give them a go. I think the easiest solution is the extra step of having an input cell for the 4 digit code which then generates the LOOKUP result.
The formula you need in your output cell is =LOOKUP(D2,A:A,B:B) with D2 being your input cell. In the other sheet where you need that data to insert i would just put ='Sheet1'!D4 with D4 being your output cell.
Hopefully that makes sense!
R8Steve said:
The formula you need in your output cell is =LOOKUP(D2,A:A,B:B) with D2 being your input cell.
In the other sheet where you need that data to insert i would just put ='Sheet1'!D4 with D4 being your output cell.
Hopefully that makes sense!
Yep that all makes sense and works thanks. The only thing that isn't working is for some reason in my other sheet with the header instead of giving me the result it just says =Sheet1'!D4...any idea why it would do that?In the other sheet where you need that data to insert i would just put ='Sheet1'!D4 with D4 being your output cell.
Hopefully that makes sense!
Legend83 said:
Yep that all makes sense and works thanks. The only thing that isn't working is for some reason in my other sheet with the header instead of giving me the result it just says =Sheet1'!D4...any idea why it would do that?
If the above formula is a direct copy you are missing a ' in front of sheet.I'm also assuming that the sheet where your initial formulas are is called Sheet1 and not something else!
Easiest way to do it is click on the header cell, type = then go and select D4 in the formula sheet.
You could:
Make column A a data validation list and have a hidden cell somewhere near it with a vlookup to the full list of numbers (so, =vlookup(A1,A:B,2,FALSE) - assuming the list is in cell A1). Then on your control sheet, in the cell you want populating, just refer to this new hidden cell.
Alternatively, the cell on the control sheet could be the data validation list and you could put your hidden cell next to it and then any lookup on that control sheet just references the hidden cell. Would cut down on users flitting between two sheets.
Make column A a data validation list and have a hidden cell somewhere near it with a vlookup to the full list of numbers (so, =vlookup(A1,A:B,2,FALSE) - assuming the list is in cell A1). Then on your control sheet, in the cell you want populating, just refer to this new hidden cell.
Alternatively, the cell on the control sheet could be the data validation list and you could put your hidden cell next to it and then any lookup on that control sheet just references the hidden cell. Would cut down on users flitting between two sheets.
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff