Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
needyouradvice
 
Posts: n/a
Default Database Connection Nested VLookup option


I'm lost on this one. My train of thought seems simple, but I can't get
it to work.

Here is an example:

Excel Workbook with 2 Spreadsheets
Spreadsheet 1 is called: Server_Connect
Spreadsheet 2 is called: Totals

The Server_Connect sheet is queried to a SQL Database. The only thing
that stays the sames is the columns. All other information is
constantly changing.

The Totals sheet is broken up. Its not uniform so each and every cell
will have to contain a variant of the V or H lookup formulas. No easy
way out of this one.

Ok. Here is the problem, in Sheet1 (Server_Connect) I have 3 Columns:

Name StockID CountofStockID

I need to do a lookup function that will lookup by name, then by
StockID and print the CountofStockID number in my selected cell.
That’s it, but I can't figure it out. I keep getting the usual #NUM or
#N/A error. I tried every know command to no avail.
if(Name="Bob"(Hlookup...)) etc. The idea is there, but the numbers
just aren't.

Thank you for any info,
Tom


--
needyouradvice
------------------------------------------------------------------------
needyouradvice's Profile: http://www.excelforum.com/member.php...o&userid=34250
View this thread: http://www.excelforum.com/showthread...hreadid=540044

  #2   Report Post  
Posted to microsoft.public.excel.misc
Pete_UK
 
Posts: n/a
Default Database Connection Nested VLookup option

Assume in your Totals sheet that you use cell A1 for the name, and cell
B1 for the StockID. Try this array formula* in cell C1:

=SUM(IF((A1=Server_Connect!A2:A5000)*(B1=Server_Co nnect!B2:B5000),Server_Connect!C2:C5000,0))

I've assumed that you have data down to row 5000 - adjust to suit.

* As this is an array formula, once you have typed it in (or
subsequently edit it) you must use CTRL-SHIFT-ENTER instead of just
ENTER. If you do this correctly then Excel will wrap curly braces { }
around the formula - you should not type these yourself.

Hope this helps.

Pete

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Checkboxes vs. Option Buttons JW_4222 Excel Discussion (Misc queries) 2 February 1st 06 02:38 PM
VLOOKUP help needed! MartinC Excel Worksheet Functions 1 January 12th 06 01:46 PM
the option "sent to" mail recipient is not available in excel 200 Ngozi Excel Discussion (Misc queries) 1 August 23rd 05 03:50 PM
Using VLOOKUP for one option, if this option is not valid than sec Edd Excel Discussion (Misc queries) 2 March 31st 05 12:11 PM
Filter option vishu Excel Discussion (Misc queries) 1 February 28th 05 02:36 PM


All times are GMT +1. The time now is 05:30 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"