DonationCoder.com Software > Post New Requests Here

In huge need of a formula/macro in MS Excel

(1/2) > >>

alogoc:
-Fill Column Based On Column In Another Workbook-

Hello

I am looking for a macro that will fill the column C by looking at column A at another workbook.

For example:

Full workbook:
A(column that will look) B C
blala blabla Blabla

Workbook missing entries in C row:
A B C
blabla blabla Null

So the workbook that missing entries in Column C,will look for it,by looking at
at column A,wich is the same at full workbook.

Any help would be much appreciated.
Thanks in advance

Here the workbook wich the values will be taken at Column A, named as archive


And the the workbook (named 900066_027_2373.xls)wich the column C will be filled,by looking at column A.Then find that code in the "archive.xls".See what has in it's A cell.Copy it.Paste it in current cell.

Links for the archives:
http://rapidshare.com/files/269522893/900066_027_2373.xls
http://rapidshare.com/files/269430692/archive.xls

I am will pay for this and dying to get this done.

Thanks

AndyM:
Would this be simply a vlookup in the second workbook based on a table in the first workbook?

I'll work on this tonite if no one has gotten to it first.

AndyM:
(I fixed the files, left out range names)

Here's one way using a formula with the Match and Index functions (Vlookup is shorter but requires the lookup list to be sorted)

Rates.xls is a spreadsheet that has haircut rates (column C) for the 3 Stooges (column A).  This would correspond to your "Archive" worksheet.

Formula.xls is a spreadsheet that will populate column C if you type a customer name in column A (only for rows 2 through 15, since the formulas in column C are only in those rows).

If there is no name in Column A, or the name in Column A is not one of the 3 Stooges, then the conditional formatting of C2:C15 hides the #N/A.

You could also populate column C with an Event Change macro, much more elegant.

Target:
I can't access the original files, so I don't know what your data looks like, but Here's one way using a formula with the Match and Index functions (Vlookup is shorter but requires the lookup list to be sorted)

Rates.xls is a spreadsheet that has haircut rates (column C) for the 3 Stooges (column A).  This would correspond to your "Archive" worksheet.

Formula.xls is a spreadsheet that will populate column C if you type a customer name in column A (only for rows 2 through 15, since the formulas in column C are only in those rows).

If there is no name in Column A, or the name in Column A is not one of the 3 Stooges, then the conditional formatting of C2:C15 hides the #N/A.

You could also populate column C with an Event Change macro, much more elegant.
-AndyM (August 20, 2009, 10:03 PM)
--- End quote ---

I can't access the original files, but a lookup would have been better in the example than using index/match formulas together with conditional formatting

index/match is the lookup you use when the value you are returning is to the left of your lookup value (ie if you want to lookup a value in column C, but return a value from column A)

and so long as the lookup values don't have multiple results (eg where looking up MOE could return 1, 2, or 3) vlookup is perfectly acceptable (sorted or otherwise, at least that's my experience, just use the exact match option) 

error values in your results can also be avoided by using conditional statements, eg

  =if(iserror(vlookup(value, range, column, TRUE)),"",vlookup(value, range, column, TRUE))

if the lookup value isn't found it returns a blank, otherwise it returns the lookup value

that could be extended so that blank cells don't calculate, eg

  =if(cell="","",if(iserror(vlookup(value, range, column, TRUE)),"",vlookup(value, range, column, TRUE)))

if your reference range is only small, use absolute referencing, or a named range

AndyM:
index/match is the lookup you use when the value you are returning is to the left of your lookup value
--- End quote ---

I got used to using Index/Match as much for that reason as having to add the "0" parameter to unsorted lookups. 

error values in your results can also be avoided by using conditional statements
--- End quote ---

I used to use all those nested ifs, now find the conditional formatting more flexible and the shorter formulas easier to read and edit.

Still, I'd probably use an Event Change macro and avoid formulas altogether.

One of the many things I like about Excel is that there is usually eight different ways to do anything...

Navigation

[0] Message Index

[#] Next page