Shortening a code in Excel (by using VBA)

AndersLa

New Member
Joined
Apr 4, 2013
Messages
4
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
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
L

Legacy 234512

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

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

New Member
Joined
Apr 4, 2013
Messages
4
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
 
L

Legacy 234512

Guest

ADVERTISEMENT

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

New Member
Joined
Apr 4, 2013
Messages
4
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,336
Messages
5,601,023
Members
414,421
Latest member
tonybear1994

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top