#1   Report Post  
Old June 24th 08, 02:21 PM posted to microsoft.public.excel.setup
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 1,560
Default Import data from a table

I tried to retreive the data from the table but I got an error. Please let me
know the mistake in my formular. The table is (B2:F5):
0-30 days 31-37 38-44 45-51 days
$0-$10,000 225 345 465 585
$10,001-$15,000 395 505 625 745
$15,001-$20,000 565 685 805 925

I keyed in 10,000 in B8 and 52 in B9 and the output is in B10:

10000 (B8)
52 (B9)
$0-$10,000 (B9 output)

My formular is:
=OFFSET(B2,ABS(ROUNDUP(B8/5000-1,0)),IF(B9<31,1,IF(B9<38,2,IF(B9<45,3,IF(B9<52,4) ))))
I checked the function arguments and was told it has the volatile. Please
re-set a new formular for me if mine is not working. Thanks,



  #2   Report Post  
Old June 25th 08, 09:53 PM posted to microsoft.public.excel.setup
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2008
Posts: 1,358
Default Import data from a table

Your value in B9 is greater 51. Your offset formula's last check is
IF(B9<52,4). Do you have need of a number if it falls outside the dateline?
Perhaps you should have a column G with data, and the header in G2 would be
52+ days. Then you could modify the portion of your offset statement like
this, IF(B9<52,4,5). Or something to that effect. If the value in the 45-51
days is also the data that would be for 52+days, you can delete the nested
B9<52 portion, and modify like this IF(B9<45,3,4).
--
John C


"David" wrote:

I tried to retreive the data from the table but I got an error. Please let me
know the mistake in my formular. The table is (B2:F5):
0-30 days 31-37 38-44 45-51 days
$0-$10,000 225 345 465 585
$10,001-$15,000 395 505 625 745
$15,001-$20,000 565 685 805 925

I keyed in 10,000 in B8 and 52 in B9 and the output is in B10:

10000 (B8)
52 (B9)
$0-$10,000 (B9 output)

My formular is:
=OFFSET(B2,ABS(ROUNDUP(B8/5000-1,0)),IF(B9<31,1,IF(B9<38,2,IF(B9<45,3,IF(B9<52,4) ))))
I checked the function arguments and was told it has the volatile. Please
re-set a new formular for me if mine is not working. Thanks,




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
External Data Range Import to a Cell not Table gcutter Excel Discussion (Misc queries) 0 December 10th 07 04:13 PM
Pivot Table Data Import Jason Excel Discussion (Misc queries) 3 January 3rd 07 02:26 PM
Import External Data Non-HTML Table Jay637 Excel Discussion (Misc queries) 0 March 9th 06 04:37 PM
Is it possible to import external data into existing pivote table helen shuchman Excel Discussion (Misc queries) 0 March 2nd 06 04:00 PM
external import of multiple worksheet data & create pivot table prospects Excel Worksheet Functions 0 November 3rd 05 09:27 PM


All times are GMT +1. The time now is 12:28 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017