Posts by Herbz

Re: VBA or Form To Update Base Data

Are you on skype by any chance?

Re: VBA or Form To Update Base Data

Hi Roy,

Please accept my apologies for the late reply, I have been away from the office since late last week, and have only just come back in. I have looked at the user form you have designed, I am very impressed. Thank you so much for taking the time to look into this for me, I really appreciate your help. By the way which part of the UK are you in? I went to Leeds Business School in the North and stayed in the UK for about 8.5 years. As you can see from my location, I am now based back home in Southern Africa in Zimbabwe.

Much appreciated

Herbz

Re: VBA or Form To Update Base Data

Quote from royUK;693069

It's a UserForm not an InputBox. I'm at work to now but if I have time tonight I'll add some notes to the code. In the meantime maybe tried to understand the code which cab be easily adapted to do the rest

Thanks Roy, I will try and fiddle with it, but I am a bit of a novice when it comes to the whole area of coding. If you can spare a moment in the evening that would be fantastic, but no pressure.

Many Thanks

H

Re: VBA or Form To Update Base Data

Quote from royUK;693052

Here's an example form that should get you started

UserForm Example

Also, see the DataBaseForm

Hi Roy,

This is absolutely awesome; I have never used input boxes before. What you sent through actually solves my exchange rate problem, but do not have the slightest idea what you did. Could you please point me to a tutorial.

The rest of the table is pay rates by grades from 72 to 77 by country. I need to be able to update these pay rates, say by selecting a country and then I have the option to ammend the pay rates from 72 to 77.

Really appreciate your help.

Thank you

Herbz

Good evening All,

Could someone kindly assist with ways on how to update base information in a model I am working on. I have attached a simulation of what the base information tab looks here: https://drive.google.com/file/…1bGNPZHM/edit?usp=sharing . This tab will be hidden, but I would like the master user to be able to update the base salaries which feed the rest of the model through other calculations.

Any ideas on how I could update this table, using some sort of code or form depending on which country's information I want to update.

All assistance appreciated

Many Thanks

H

Re: VBA To Find User Name

Quote from cytop;692946

Neither is best... The user name in Excel can be set using the Excel Options. If not set then the Environment username will be reported. So it all depends on your particular setup.

But based on the above, I would suggest using the Environment (or Login) name - the user does not get the option to easily change that.

Thank you for this, where would I find the login name?

Thanks

Hi All,

Could someone kindly help me with a code (to be used as a workbook open event), that will find the username (pc-name) and paste it in say cell A1 of the tab called "Data" in that workbook.

I have seen some discussions between using Application.UserName and Environ("UserName"), which is best. I am wanting the name to drive a dependant drop down list dependant on the user name.

Thank you

H

Hi All,

I have a report that looks at a number of regions. You can select a region by selecting from a drop down list. The report then gives you information specific to that region. I would like to send this model out to the different regions for use there, however, I need a way to make sure that the only options that each region will have in their drop down menu is their region and nobody else's. How can I create a drop down list which will be user dependant?

A curve ball I know, but all help is greatly appreciated.

H

Hi All,

I am looking for some help on a bit of a consolidation problem. I have 2 departments that have a bit of a conflict.

The first department (who captures the original data) needs to look at their data by region, therefore that capture their information say in 4 different spreadsheets, each representing a region (this is how they "understand" their data best). The 2nd department looks at a more holistic picture, and needs to look at that same data from an "total" view, but relies on department 1's data, as this is the source.

Department 2 is therefore having to copy and paste data from department 1's 4 spreadsheets. Is there a formula that I can use to combine the 4 spreadsheets so that department 2 now works from 1 spreadsheet and it is easier to write formulas for department 2 to analyse their data without having to do copy and paste.

Please assist

Thank you H

Hi All,

I have a bit of a weird one. Our Excel files are housed on a server to which all our machines are networked. If I open my model and the supporting feeder source spreadsheets, the links are not recognised and I get #VALUES. However if I go to the actual server, and open the model from there and the supporting spreadsheets, everything works fine.

Any ideas on what the problem could be and how to resolve it?

Thanks

Herbz

Re: Text Formula

Wow, guys this is awesome, many thanks for all the varied approaches, really appreciate. I have just learnt some funky new approaches.

Herbz

Good morning All,

Part of my vlookup formula uses the expression, TRIM($E$2&$B$4&LEFT(A17,SEARCH("(",A17)-1)&E4). This returns the value 14845ALAT 2013 with a space between the T & the 2. The cells I am referencing to do not have any spaces and the trim function doesn't seem to help. How do I get rid of the space, as the value being looked up shouldn't have a space.

I have tried:

SUBSTITUTE(TRIM($E$2&$B$4&LEFT(A17,SEARCH("(",A17)-1)&E4),CHAR(32),"")
SUBSTITUTE(TRIM($E$2&$B$4&LEFT(A17,SEARCH("(",A17)-1)&E4),CHAR(160),"") but I get the same result.

Please help.

Thank you

Good morning All,

I am trying to create a formula that will extract the words "Mindray BS 380/ BS 400" from the text "[IFCC/ Mindray BS 380/ BS 400]". I am very close, but can't quite finish it. I have so far used the formula =MID(A1,SEARCH("/",A1)+2,SEARCH("]",A1)), which has given me the result Mindray BS 380/ BS 400]. I can't seem to get rid of that outer square bracket.

It has to be a dynamic formula as the text within the square brackets will not always be the same.

Please help.

Herbz

Afternoon All

I am looking for some help with a bar graph with 3 variables. I have drawn what the graph should look like as well as what the source data looks like. How do I create the graph. The example graph and data is here: https://drive.google.com/file/…pZ0pkb0U/edit?usp=sharing

Please help, not sure if I should be laying my data differently.

Thanks

Herbz