Searching in Excel

dudara

I have one worksheet in excel where a date can be entered.

Using this date, I want to search another worksheet, find the particular cell containing that date, and then use that cell number in another formula.

Any ideas on how to do this?

Capt'n Midnight

hlookup / vlookup any use ?

you can reference cell A1 on another sheet by
=sheet2.a1 (openoffice)
=sheet2!a1 (microsoft)

Dark Hair

dudara wrote:

I have one worksheet in excel where a date can be entered.

Using this date, I want to search another worksheet, find the particular cell containing that date, and then use that cell number in another formula.

Any ideas on how to do this?

vloookup.
Now.. when you are in the cell that you want the desired result to be
Go to the formulae button. Or "insert >function"
In the search boox type in vlookup..... Open said yoke.

A box with Four fields pops up

In NO 1 You click on the cell of the data you want to search for in the other work book. In this case the "Date"

IN NO2 you enter the place where you want excel to search. To do this click in the no 2 box... then click on the sheetwork book you want searched then drag your pointer across the tops of the col's you want included in the search EG A,B,C & D (Col A must be the Date fields in ascending order)

In the third box type in 2 or 3 or 4 depending on what col the data you want found and brough back is in. So D = 4

Now in the fourth and final box type in "False" (That means excl is going for an exact match or else = n/a#

Basicaly what it is doing is looking Is scanning your selection for the date you asked it to look for.. and when it finds it ..it is going to go across to the D col and bring that result into your other work sheet.

HTH

dudara

Those functions return values from the cell, is it possible to return the actual cell reference no?

dudara

@Dark Hair, thanks for that.

What I want to do is identify a particular cell, which I can do through the VLOOKUP method, which can return a value from the same row but different column (col .

However I also want to return the previous 9 values from col B too. Any ideas?

Dark Hair

dudara wrote:

@Dark Hair, thanks for that.

What I want to do is identify a particular cell, which I can do through the VLOOKUP method, which can return a value from the same row but different column (col .

However I also want to return the previous 9 values from col B too. Any ideas?

Some time you just have to simplify things. Searching is all about indexing. As such you can search for A...find A and return B. Now you can do what you ask but then you are into VB editing.

Not teaching you to suck eggs here but...You might find it is just as quick to take your result. CTRL+F find it on the original work book... copy your result + 9 and paste it where ever needed?

I once spent a day trying excel stuff when half an hours cutting and pasting would have done.

The thing is if it's a once/twice off... you just get it done. If it is a constant automated process then its worth investigating further.

What you could do is write a macro? Which is just recording your keystrokes/actions. But it has its limitations.

dudara

Dark Hair wrote:

Not teaching you to suck eggs here but...You might find it is just as quick to take your result. CTRL+F find it on the original work book... copy your result + 9 and paste it where ever needed?

If it is a constant automated process then its worth investigating further.

It is automated, so cutting and pasting is not an option.

I figured out how to do it using a combination of MATCH and INDEX, turned out to be pretty easy in the end.

I'm just used to writing my own code for data handling, and I would never normally use Excel, so I'm pretty ignorant of it's inbuilt capabilities. However, where I am right now uses Excel for all data stuff, so I have to adapt.

tommycahir

Hi Ya

I see that you managed to figure out the above issue .. i have done some VBA programming in the past and have found the following site to be very useful when stuck.. a good tip is try search their forum's as there is quiet a comprhensive library of code hidden in there among the post's that may help with future issues that u may encounter

mr excel