Shortening a code in Excel (by using VBA)

[AndersLa]

Hi,

First of I am new to posting on this forum, but have been using it a nomerous of times, and it has been of great help when I have been stuck in Excel, so thank you all for your contributions!

I have been searching the last hour for how to shorten the formula below/create a VBA function/make a macro to autofill it, with out any luck:

=HYPERLINK("#'Raw data'!"&SUBSTITUTE(ADDRESS(1;1+51);"1";"")&MATCH($B$2;'Raw data'!$B:$B;0);VLOOKUP($B$2;'Raw data'!$B$4:$CZ$86;51;FALSE))

What I am trying to do is to preferably create a function where i only type something along the lines of this in a cell: =short(51,B2) where the number and cell reference would be the variable (as this function would be used on a lot of cells). Another option would be that the full formula would be put in the cell, but where i only needed to write i.e. =short(30,B1) to call forth the formula with the number and cell change - thus not having to copy and change the whole formula for each cell.

I appreciate any help I can get!

Best regards
Anders

 

[Legacy 234512]

ok, can you explain what you want your function to do exactly?

 

[Andrew Poulsom]

Welcome to MrExcel.

Since 51 is a constant try:

=HYPERLINK("#'Raw data'!AZ"&MATCH($B$2,'Raw data'!$B:$B,0),VLOOKUP($B$2,'Raw data'!$B$4:$CZ$86,51,FALSE))

 

[AndersLa]

Andrew: 51 is actually a variable, as this would be a new number for each cell, but thank you for your input

Mehowski:
I have two sheets, where one contains all raw data and the other sheet has a drop down list of names (which is the selection criteria for my vlookup (B2)) and cells with key information about the name (which is found by using the vlookup to the raw data sheet). When a name is selected from the list, the other cells in that sheet displays the corresponding data from the "raw data sheet", e.g. if I select "john" I would display John's age, location, work place etc. What I want to do is click e.g. on John's location (cell containing location) and I automatically jump to the raw data sheet where his location is the input such that I can change that information if it is not correct. Preferably I would like to be able to overwrite the information in the second sheet directly, but I guess this requres a large amount of VBA programming to ble able to work, as the cell only contains the hyperlink/vlookup function.

Looking forward to your reply.

Best regards
Anders

 

[Legacy 234512]

ok, the VBA for this shouldnt be too complex. you need to add a listener to the cells that are displayed on your sheet two ( name, age, location, work place etc ) , and you need to store your address to sheet2 ( cell address or row number from sheet 1 ) in a variable. Then if you decide to change any details the vba would take care of overwriting original values in sheet1.
can you send a dummy of your worksheet?

 

[AndersLa]

That sounds excellent!

I have uploaded a sample of the sheet here: https://docs.google.com/file/d/0B2t_22eubzuVN3k5SXl4T21TRWM/edit?usp=sharing

I really appreciate this, thank you!

Edit: To download it, just open the link and click on download

 

[Legacy 234512]

ok... there you go: Example 1.xlsm :: Free File Hosting - File Dropper: File Host for Mp3, Videos, Music, Documents.

click on any of the 4 cells ( B3, B4, D3, D4 ) in "Platform" to edit values , they will be automatically updated in your other sheet.

 

[AndersLa]

Hi again,

I finaly got the link to work, and downloaded the example. It works excellent, thank you very much for your time and help. This is much appreciated!

Is there any way to make using the "cancel" button (when clicking on one of the four cells) to get the previous value in that cell - as of now the new input would be 0 if I accidentally click one of the cells and click "cancel"?

Best regards
Anders