Excel Help

[donna]

i am trying to figure out how to bring data automatically from one worksheet to another.

for example, i am listing all hotels that my company works with on one worksheet, but i want to have smaller worksheets depending on the location of the hotel. is there a formula where i can just update the main worksheet, and then the information will automatically transfer over to the smaller one, depending on the location?

[Cuban]

Useful link.

In your target cell, =WorksheetName!CellRef

Example:

=Hotels!c35

Will place in that cell the contents of cell C 35 from the worksheet called 'Hotels'.

HTH.

[donna]

thats nearly it, but i want the formula to automatically pick out the location of the hotel, which has its own cell on the main sheet, and drag it over to the smaller worksheet.

[Guest Reimar]

thats nearly it, but i want the formula to automatically pick out the location of the hotel, which has its own cell on the main sheet, and drag it over to the smaller worksheet.

I'm not an specialist on Excel but as far as I know, you can use filtering for that job or macro's.

Cheers.

[Sir Burr]

I think you will need to use the IF function.

=IF(cell number & worksheet name = "text contents",cell number you want it to appear,0)

What that does is if the designated cell says this, then put it in this cell on this worksheet, if not, put a zero.

PS. You have to use the quotaion marks for text. If you use quotation space quotation, then this means any text.

Quotation word quotation will mean only that word.

Edited May 16, 2008 by Sir Burr

[donna]

in that 'if' scenario, can i specify a range of cells for it to choose from?

[Sir Burr]

I don't think so, but you could try =IF(sheet name B5:B10 etc.

I've never tried it, but try the colon. You might have to put another set of brackets around it.

[Totster]

VLOOKUP allows you to enter a code into one cell and it will pick the details from another worksheet

For example, you can have a list of hotels in one worksheet, then in the other worksheet you can type in the hotels code, and the information will be displayed in the same cell automatically

Might be what you're looking for, not an expert so cannot explain it very well

Totster

[sensei]

For your current and future Excel problems...

Free Excel Tutorial Book HERE

Edited May 16, 2008 by sensei

[Cuban]

I'm currently using one of the 'Thai' computers in the office - so Excel toolbars etc are in Thai and a bugger to read!

Depending on how simple/complex you wish to made the process - you could achive this sort of hotel selection with aid of the auto-filter drop downs in the top of each columm.

You would have one worksheet listing all the hotels that have ever existed - with columns for location, price, parking, spa etc.

Then simply use the column's auto-filter to restrict your view of the whole sheet's data to just the hotels that meet your requirement.

First filter Location, then those with spa or not etc. ?

The displayed results could then be copied to other document etc?

To automate this filtering/selection idea within one sheet but pulling data from other sheets, Vlookup (link with example) as Totster suggests will do this task. The only thing I would add to this idea is that searching on text can be a problem unless you control the data entry. Mis-smellings can mean data is lost unless you carry out data sanity checks from time to time.

HTH.

[slackula]

Totster above was correct I think. VLOOKUP is the function you need, I don't think the =IF(.,.,.) has the capability to do this. Here are some things I found that should help, but it'll take some reading and figuring out:

http://www.timeatlas.com/mos/5_Minute_Tips...OOKUP_in_Excel/

http://www.contextures.com/xlFunctions02.html

http://www.techonthenet.com/excel/formulas/vlookup.php

If you have MS Access in your office then that might be a simpler way to handle this.

[rgs2001uk]

Totster above was correct I think. VLOOKUP is the function you need, I don't think the =IF(.,.,.) has the capability to do this. Here are some things I found that should help, but it'll take some reading and figuring out:

http://www.timeatlas.com/mos/5_Minute_Tips...OOKUP_in_Excel/

http://www.contextures.com/xlFunctions02.html

http://www.techonthenet.com/excel/formulas/vlookup.php

If you have MS Access in your office then that might be a simpler way to handle this.

took the words right out of my mouth, why would you use a spreadsheet to try and do the work of a database. keep it simple, excel for number crunching, access for manipulating data.

[Prakanong]

Totster above was correct I think. VLOOKUP is the function you need, I don't think the =IF(.,.,.) has the capability to do this. Here are some things I found that should help, but it'll take some reading and figuring out:

http://www.timeatlas.com/mos/5_Minute_Tips...OOKUP_in_Excel/

http://www.contextures.com/xlFunctions02.html

http://www.techonthenet.com/excel/formulas/vlookup.php

If you have MS Access in your office then that might be a simpler way to handle this.

If you need a database then MS Access is not the answer - most IT professionals will not allow it in their organisations but sometimes it sneaks in or is a legacy system. I am currently getting rid of one that some outside so called consultancy built to keep track of key customers.

It has its place - at home!

I have used it in household name organisations and first used it back in 91 - version 1.0 or 1.1. It was also used as an almost HR system at BSkyB Call Centres when I worked there and I last used it in 2000 at some company that used it as a database but used "C" I think it was to access it. I earned good money using it when working in Bangkok but the client is always correct and at NTL (Virgin Media) I had stand up arguments about not using it.

[Prakanong]

Hmm maybe I was a bit harsh on MS Access but apart from home, SOHO and very small company use I think companies should move to a more robust database system - even regional offices should have their central IT provide databases for them over the web.

I am actually going to the condo of a Microsoft Director for Asia this evening for a BBQ - I hate to talk shop but I bet he agree's with me as well.

[donna]

my database is quite small, though, so i guess ms access could just be the right tool for the job.

[naka]

my database is quite small, though, so i guess ms access could just be the right tool for the job.

It's a fairly steep learning curve compared to a spreadsheet, but may be worth it.

Naka.

[Prakanong]

my database is quite small, though, so i guess ms access could just be the right tool for the job.

It could well be - what messes up MS Access databases is that the upfront design of the tables is not done correctly - OK they do not have to be fully "Normalised" but they often end up being spreadsheet like anyway

Small databases are fine - its when they grow and start to become business critical and Access can not handle it the backing out and starting again with something like Oracle, Sql Server etc is the problem.

I am sure if you put your small requirements up here someone would help you design the tables

Barring that its incedibly cheap to outsource to good guys here in Asia - I got a great prototype Joomla template and site mock up done last year from a guy in the PI for 100 USD for some private charity work

[jackgopher]

You can check the following resource for some more info regarding the Excel vlookup function:

vlookup