excel equation to search a tab for a specfic data

September 21, 2012 at 12:42:33
Specs: Windows 7
I appreciate any help I can get on this! I am looking for an equation that will search all of the tabs in my document looking for a specif word (in this case, the name of a client), and then return the value in the NEXT CELL to a different tab (in this case, the value is an hourly rate). I was actually able to make this work using a very long "if then" equation, but it was tedious and doesn't allow for much flexibility.

I have read in other forums that a macro might work? Not sure what a micro is.

Thank you for your help,steve

See More: excel equation to search a tab for a specfic data

Report •

September 21, 2012 at 15:17:31
A macro is a "script" written in VBA (Visual Basic for Applications) which contains a series of instructions telling Excel and/or VBA what to do. There are some things that can't be done within Excel itself but that can be done with VBA.

One such example is what you are trying to do: search multiple tabs for a value.

Just like Excel formulas, VBA needs to know what cells to look at, such as a specific column or columns, and where the returned information should be placed.

The following code is just an example of a macro. This code will search for the value found in Sheet1!A1. It will look in Column A of each sheet (except for Sheet1) and when it finds the value, it will return the value from Column B of the same row, same sheet. It will place that value in Sheet1!B1.

I believe that that is sort of what you are looking for.

Sub multVLOOKUP()
 On Error Resume Next
   For sht = 2 To Sheets.Count
     myVal = WorksheetFunction.VLookup(Sheets(1).Range("A1"), _
             Sheets(sht).Range("A:B"), 2, 0)
        If myVal <> "" Then Exit For
 Sheets(1).Range("B1") = myVal
End Sub

Now, this is a fairly simple macro that either returns a value if A1 is found or doesn't return anything if A1 is not found. It also needs to be run manually each time A1 is changed.

There are lots of other "features" that could be added, and there are various ways to automate the code. All I am trying to do here is show you what a macro is so that you have an idea about what they can do.

If you'll supply some specifics about your requirements, I'll see what I can offer.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

September 22, 2012 at 14:20:03
DerbyDad03 - holly molly, thank you. I know just enough about xcel and html to understand what you are saying but I have no idea how to implement it.

Since you asked, here is what I am trying to do.

In tab 1 I have a series of client names in column A. Lets say they are Mark, Mathew, Luke, John in lines 1, 2, 3, 4

In tab 2 (lets call it "Monday") I have set up 2 columns that allow me to insert a start time and an end time (8:00 am in column A and 9:00 am in column B, both line 1, and in column C the equation shows "60 min", also line 1. Then in column D I have written a data validation list that references tab 1, column A for my clients' names and allows me to select one of those clients names to appear in column C, line 1. All 4 of those columns work together in line 1 to show me a A) start time, B finish time, C total time spent, and D the client who is responsible for paying for that time that is represented by the name selected in the validation list.

For tab 2 "Monday", I then repeat the equation in line 1 into lines 2 - 15 (including validation lists) in essence, creating a time sheet for "Monday" that allows me up to 15 individual time sequences for up to 15 different clients on 15 lines.

I then created the exact same series of lines/columns in tabs 3, 4, 5, 6, 7, 8, for each day of the week. (I could put all 7 days in the same tab, but for now I have them individually).

The goal is to write macro in tab 1, column B, line 1 that will search JUST column D for ALL tabs 2-8, ALL lines 1-15 for the client name listed in tab 1 column A, line 1. THEN return all of the data for "total time" in column C in ALL of the tabs 2-8, ALL of the lines 1-15 for JUST the client referenced in Line 1 tab 1. This adds together all of the "total time" during that week for that particular client selected in line 1 tab1 .

I'd like to repeat everything in tab 1, line 1 for each of the 4 clients in lines 2-3.

Is that clear as mudd? I'd be happy to send you the spread sheet I have started already if that would help.

Thank you so much for your help.


Report •
Related Solutions

Ask Question