Analyze a column in one workbook and paste a result in another workbook

**capnhud** · 06-14-2010, 09:14 AM

I am wanting to have a column analyzed and if the number in the column cell is 1 or greater then paste in another workbook column yes or no. Do I use vlookup for this? Or is there some other excel magic that I am supposed to use? What I am wanting to do is if the cell contains 0 example1 workbook then in the example workbook2 paste the word no not available or yes available. Attached are sample workbooks

**Dulanic** · 06-14-2010, 10:27 AM

Change workbook names and or sheet names as applicable:

=IF('[example1.xls]Sheet1'!A3>=1,"Available","Not Available")

Put on 2nd workbook in A2 and copy down as far as you need.

If you have further criteria like a part # on the sheet you can toss in a vlookup inside the IF.

**capnhud** · 06-14-2010, 10:52 AM

What would I need to change so that this would apply to any worksheet that has quantity column? Or would I need to change the sheet name each time?

I tried
=IF(Activesheet!A2>=1,"Available","Not Available")

but ended up with #REF! error

**Dulanic** · 06-14-2010, 11:51 AM

Activesheet is not valid for formulas. Do either of the workbooks or sheet names ever stay the same? I'm semi confused what your requesting. Something needs to stay static....

**capnhud** · 06-14-2010, 12:04 PM

the sheet is similar in layout, but the workbook that contains the sheet has a different name. For example

Workbook 1. has sheet named workbook 1
workbook 2 has sheet named workbook 2

and so on. I just wondered could I use the statement you provided to apply to any active worksheet that it could apply to without have to contstantly change the name of the activesheet

**Dulanic** · 06-14-2010, 12:24 PM

You would need to enter the name of the workbooks/sheets somehow. You could use variable sheet names as this explains:

http://www.ozgrid.com/Excel/variable...heet-names.htm

The downside is when does yours end? and so on tells me it would just keep going? You need to have a set list of workbooks/worksheets which to me it doesnt sound like that is the case. You have a variable source workbook and a variable output workbook.... That is a challenge (at least to me) when you have no set list of file/sheet names and it can change so much. Even with VBA we could easily do an activesheet, but what about the output? It sounds like thats variable too in which case activesheet wont do you any good since the input would be using the activesheet.

**capnhud** · 06-14-2010, 12:36 PM

The output workbook is always the same it the input workbook that may change. For instance in the variable workbooks there is only one worksheet that always has a column that is named quantity. Is it not possible to reference only the first sheet in a workbook and then perform the IF function?

Sheet Index