Excel help - Cash offered!

Mike1983 · 12 Sep 2017 at 07:01

Hi

I was wondering if the following is possible and what the most efficient way is of doing it to make it user friendly (macro \ VBA?)

Column A of Spreadsheet 1 contains a list of fixed text. This never changes.

Column B of Spreadsheet 1 contains values which are variable. These values are applicable to the fixed text descriptions by the side of them in column A.

A completely different spreadsheet, spreadsheet 2 contains the fixed text of Spreadsheet 1 and also has the variable value by the side of it.

Spreadsheet 1 is the master way of tracking information. Spreadsheet 2 gets updated and released on a periodical frequency.

Is there some kind of function that can be run on Spreadsheet 1 that automatically seeks out the relevant variable values in spreadsheet 2?

I should add that Spreadsheet 2 is formatted differently to Spreadsheet 1 so the function will need to seek out the fixed text and then pick up the variable text in the adjacent column.

I was going to ask this on Fiverr but thought I'd ask here first. Happy to send some beer tokens if a workable example can be sent on (if it's easy).

Thanks

smogsy · 12 Sep 2017 at 07:42

Sounds easy enough. Which version of excel? Newer versions can use c# instead of VBA.

Also example columns for each sheet?
How often should it update and by a button or said time?

HeX · 12 Sep 2017 at 09:52

Surely just a VLOOKUP will do what you require? Or I'm misunderstanding what you need.

gpuerrilla · 12 Sep 2017 at 18:26

Agree. Lots of use of fixed text and variables wasting energy. Seems a simple enough job with a variety of ways of doing it.

Mike1983 · 12 Sep 2017 at 19:53

Hi all.

Sorry about the delay, juggling work and family life. I'm glad this sounds easy to you! I've only ever used simple mathematic formula on Excel, never strayed into macros, VBA or VLOOKUP function.

To further describe & to answer Smogsy's request and hopefully nail down the easiest way to do this, I paste screenshots below to try and explain.

Here's a representation of sheet 1. This is the main sheet that presents the information I'm interested in seeing. The blue text always stays the same, the green text changes periodically. This could be a date, a name, a cost, a duration or something else.

Here's a representation of sheet 2. This is a completely separate spreadsheet / workbook / .xls file, not another sheet within speadsheet 1. This gets updated periodically by someone else. Note that this spreadsheet contains the "important data" on different lines and there's a lot of unimportant data.

To answer Smogsys question, I'm looking to achieve the following process

1 - Spreadsheet 1 already exists with last months out-of-date variable values in there
2 - Spreadsheet 2 is emailed to me by someone else, this contains new variable values
3 - After saving spreadsheet 2 to my computer, I want to press a button within spreadsheet 1 that triggers an action to seek out the important data in spreadsheet 2 and update the variable values in spreadsheet 1.
4 - Note it doesn't have to be a button, it could be an auto function that seeks this info out if I was to save spreadsheet 2 into a predefined folder or something similar.
5 - It's Excel 2010 I'm using, so are the people who send spreadsheet 2.

Hope this makes sense! If the answer is along the lines of "this is actually really easy, just go and research the VLOOKUP function" like Hex says, then that's great, just let me know, it's just an area outside of my comfort zone but love to learn new things.

Thanks for your time all

HeX · 12 Sep 2017 at 20:04

In sheet 1 you would just want a VLOOKUP function in the variable cells (Col B).

So in B2 for example you would have something like:

=IFERROR(VLOOKUP($A2,'[Filename.xlsx]Sheet2'!$A:$E,5,0),"Not found")

What this does is look at cell A2, then tries to find that value in the first column of the range you've pointed it at, in this case Col A of Sheet 2 (the 0 at the end of the formula means find an exact match only). If it finds a match bring back the value in column 5. The IFERROR wrapped around it just catches any situation where it can't find a match, in which case the value "Not found" would be placed in the variable column.

That's the basics of it, you can make it fancier and start wraping more logic around it, but that'll do the core of what you are after.

Google some guides on VLOOKUP, it's a very useful function.

Mike1983 · 13 Sep 2017 at 05:46

I've just tried this and it does exactly what I wanted. Thank you very much for your time in explaining things and helping me out here.

Lartyconshayo · 13 Sep 2017 at 07:59

INDEX MATCH is more flexible than VLOOKUP and worth knowing for the future.

This is the best article I have ever read on it in terms of layman

http://www.randomwok.com/excel/how-to-use-index-match/

smogsy · 13 Sep 2017 at 08:05

Mike1983 said:
I've just tried this and it does exactly what I wanted. Thank you very much for your time in explaining things and helping me out here.

Makes answers much easier once we have that detail

Vlookup will work fine as you have already seen

Things to remember
Filename will need to be the same as vlookup for it to work.

Same folder applies too

If rows/cells move you will need to modify the vlookup.

Glad you got what you needed!. I thought your cells in spreadsheet 2 would be in random places. But appears not making it even easier

moogle · 13 Sep 2017 at 09:34

smogsy said:
If rows/cells move you will need to modify the vlookup.

and if the rows/columns move too often then consider using a structured reference on your table. (columns can be referred to by name, so even if you move them the formula doesn't have to change. only renaming would require some edits)

SourChipmunk · 14 Sep 2017 at 00:29

HeX said:
(the 0 at the end of the formula means find an exact match only).

I learn something new every day. I've been typing 'FALSE' for years, based on Excel's own help.