Excel-Grab a specific value from a worksheet

Hewlett-packard / Hp compaq dc7800p small f...
December 8, 2010 at 07:01:48
Specs: Windows XP, 3.158 GHz / 2002 MB
Hi there,

Sorry for the nebulous title question but there are only so many words available in the title! I've trawled your site for an hour or 2 already today, but couldn't find anything exactly relevant (or close enough), so thought I'd ask directly.

I'm trying to run a fairly simple Excel spreadsheet showing a summary of all the previous worksheets results.

The 'Summary' sheet format is that A6:A17 are accounting status names (eg. Not started yet, Not Due Yet, Completed etc), range B6:B17 is the number (count) of applicable clients that fall into this category.

I've imported the data from Access into new worksheets within the same workbook and want the summary sheet to autopopulate based on these sheets.

eg. Sheet1 is 'Summary', sheet 2 is '2003-count' (I will be repeating this exercise ad infinitum for each year).

Row 1 in '2003-Count' shows accounting status from an Access query, and only returns those statuses that are applicable in that year - <> (for blank - which we need to amend on our systems!), Annual return filed, and Completed - Row 2 shows the count - 2, 1 and 38.

What formula or macro will I need to populate fields B6:B17 with the relevant amounts from row 2 based on the 'title' in row 1, and populate the ones that have no corresponding 'title' with 0? (Column C is a percentage field, so needs to be a 0 to work).

I used the LOOKUP formula originally and added the IF funtion and the ISBLANK to the mix and still cannot get it to return what I need... and now am so confused it was actually a relief when I accidently closed it and lost the attempts!

Looking forward gratefully to any help you can provide, I can also provide an example of the worksheet if that helps!


See More: Excel-Grab a specific value from a worksheet

Report •


#1
December 8, 2010 at 07:44:17
I'm Confused!

Perhaps it would help if you posted an example of the sheet layouts so we can better understand what you are trying to do.

Post an example of some of the inputs and the corresponding desired output.

Please read the How To referenced in the following line before you post your examples. Click on "How To"

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#2
December 8, 2010 at 08:28:36
Hi I've never used VBA Code so wouldn't know where to begin even with your tip page I'm afraid - I've tried the right click on the tab and 'View Code' thingy, but it comes up blank :s

However: Sheet1 'Summary'

A / B / C / etc
5 / Accounting Status / '2003 - number' / '2003 - percent'
6 / 'Not Due Yet' / 0 / 0%
7 / 'Annual return Filed' / 1 / 2.44%
8 / 'In Draft' / 2 / 4.88%
9 / 'Completed' / 38 / 92.68%
etc

(we can ignore the % in column c)

Sheet 2: 2003-Count:

A / B / C / D
1 / 'Annual return filed' / 'In Draft' / 'Completed' / <>
2 / 1 / 2 / 38 / <>

Where <> = an empty cell.

so, I want to take the values in sheet 2 and place them into sheet 1 in their relative places - ie if sheet 1 A7 = 'Annual Return Filed', then B7 = sheet 2 A2; however if the title in sheet 1 column A does not appear in sheet 2 row 1 then the answer should be 0 (rather than N/A) in sheet 1 B7.

Hope that makes more sense!


Report •

#3
December 8, 2010 at 08:51:07
The How To has nothing to do with writing VBA code, it's about how to post Excel data and VBA Code in this forum. Please read it so that you can repost your data like this:

            A                     B                  C  
5    Accounting Status    '2003 - number'    '2003 - percent' 
6      'Not Due Yet'             0                   0%

That makes it easier for us to read and to copy to a spreadsheet should we need to set one up for testing.

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

Related Solutions

#4
December 8, 2010 at 09:13:40
      A                     B                 C              etc
5  Accounting Status  '2003 - number'   '2003 - percent' 
6  'Not Due Yet'            0                0%
7  'Annual return Filed'    1              2.44%
8  'In Draft'               2              4.88%
9  'Completed'             38             92.68%
etc

(we can ignore the % in column c)

Sheet 2: 2003-Count:

            A               B                    C               D
1  'Annual return filed'  'In Draft'       'Completed'           <>
2        1                  2                   38               <>
 

There you go, hope that's better!

Thanks in advance


Report •

#5
December 8, 2010 at 09:34:58
Have you tried HLOOKUP?

For values that don't have titles try:

=IF(ISNA(HLOOKUP...), 0 , HLOOKUP(...))

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#6
December 10, 2010 at 03:26:33
Hi,

Just to let you know I'm not ignoring you or being rude by not thanking you, I'm just struggling to re-create my original HLOOKUP formula to get the results, never mind adding the ISNA bit!

I'll no doubt return soon to thank you properly when I have it sorted!

Cheers

Glenn


Report •

#7
December 10, 2010 at 04:14:25
Yep, turns out I needed to change the returned row number to 2 instead of 3... so simple I couldn't see it!

Thanks DerbyDad, was very helpful, did exactly what I needed.

Cheers

Glenn


Report •

#8
December 10, 2010 at 04:19:41
As I'm sure you know, HLOOKUP looks up values in the first row of the table and returns the value from the row specified in the function.

When a value is not found in the first row, HLOOKUP returns a #N/A error.

The ISNA function returns a TRUE or FALSE depending on whether the HLOOKUP returns a value or a #N/A error.

By using ISNA(HLOOKUP(...)) to return a TRUE or FALSE you can get the IF function to return a value (e.g. 0) if the HLOOKUP returns a #N/A error - i.e. ISNA is TRUE.

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#9
December 10, 2010 at 04:39:18
Hmm, tell a lie, it doesn't work entirely...

If I use that formula, it only returns a 0 if the title has been set ie, is not blank. The formula I'm using is below.

=IF(ISNA(HLOOKUP("Completed",'2003-Count'!$A$1:$P$3,2)),0,HLOOKUP("Completed",'2003-Count'!$A$1:$P$3,2))

That returns 38 (as hoped), however:

=IF(ISNA(HLOOKUP("Not due yet",'2003-Count'!$A$1:$P$3,2)),0,HLOOKUP("Not due yet",'2003-Count'!$A$1:$P$3,2))

Also returns 38 - being the last number in the row (I assume).

I've tried it with ISBLANK too and it's the same result.

If I manually write in n/a in the last column it then works - so I'll happily do that for now, it's still cut down the workload significantly as it is!

Thanks again for your help.


Report •

#10
December 10, 2010 at 07:29:54
Oh.... and I needed to add FALSE after the 2 at the end of the HLOOKUP functions... either way it's working now!

Cheers


Report •

#11
December 10, 2010 at 08:58:27
You can either use FALSE or 0 in any of the LOOKUP functions.

Excel will interpret a 1 or 0 as a binary TRUE or FALSE.

Saves keystrokes.

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

Ask Question