Spreadsheet Excel

Before i start, i know i could ask this in a more appropriate forum, but it has some programming relevance, i think :o)

I'm looking to build a new spreadsheet for the my work. I produce batch cards, for manufacturing paint, which i have used spreadsheets, to allow the changing of the batch size, and have all the raw materials change accordingly, simple stuff.

I also have a workbook in excel holding stainer weighings (amounts of stainers to add to the paint, to tint the appropriate colour.)

Here's my problem. I'm aware of IF statements in excel. They are a little basic though. LOOKUP is better, but what i need is this:

I physically enter the colour code, EG 20C33, and upon entering that, i need to access the correct worksheet in the stainer book, to get the stainer weighings to appear.

For instance:

File: c:\batchcard.xls <<< This is the batch card

File: c:\stainerbook.xls <<< This is the stainer weighing, container worksheets with the EXACT same colour code i would enter.

Anyway to copy a specific worksheets contents, in another worksheet, as the result of an IF statement, or lookup statement, is my question really.

EG

LOOKUP(A2,{"20C33"},{'[c:\stainerbook.xls]20C33'!$A$1})

Sorry for the very long post. It would be great if someone could offer some suggestions :o) ( I do realise this could be done in blitz with some effort, but i have alot of batch card formulations and data already entered into excel, and creating my own blitz app would make the system reliant on an app that is untested and only used be me. Not good if i leave and my app crashes...)

Why not use VBA (VisualBasic for Applications) which comes with Excel. It gives you forms to provide a front end. Everything, really.

You're not restricted to simple if statements then. Colour code selected by list menuetc.

It might have a bit of learning time, but if you've programmed any .Net or Visual s/w it's not too bad.

To find it, look it up in the Excel help.

Hope this helps

Basil

I agree. You'll need to use VBA for this but you could probably record a simple macro as a starting point to build on.

Ah thanks, i never thought of that :o) I learned a fair bit of visual basic at college.

Maybe it's just me, but it sounds like a database is better suited to this kind of stuff than plain Excel...

(e.g . Access, or even an Excel front-end for an access back-end)

Why don't you just have two sheets in the same excel file? It'd be dead easy then.

Imagine you have a excel file, with 'Sheet1' and 'Sheet2'. In sheet1, cell A1, type "hello world!". Then in sheet2, any cell, type '=Sheet1!A1'.

You can link similarily to what GFK suggested but across workbooks no problem. The tax product I develop for work has approx 16 workbooks linked together.

The best way to do it is to open both workbooks together and then link them. The easiest example to set this up is to:

* In Workbook1 select cell A1 and type =
* From the Windows TaskBar, select Workbook2 and select cell A1. The formula bar should show something like this: =[Book2.xls]Sheet1!$A$1
* Press Enter. The worbooks are now linked.

As you build your lookup, swap between sheets/workbooks are required.

Notes
Ideally you should keep both workbooks in the same location. If Workbook2 (in this example is just used to lookup values), you shouldn't even really need to open it as it should dynamically update.

Linking into different folders if fraught with danger especially if you move locations and is not recommended.

Be careful linking a number of workbooks. For example you have Workbook1, Workbook2 and Workbook3. If you link to a cell from Workbook1 to Workbook2, which in turn provides a value from Workbook3 this result would not flow back to Workbook1 (from Worbook3) unless Workbook2 is open - confusing eh!...

In VBA I would do some kind of simple For Next loop going through searching for a match in one file and then go through the other file and find a similar match. Helps also if they're sorted.

Off topic but does anyone know if microsoft one-note has macros incorperated with it?