Using Function to call Woorbook Sheet

I would like to use WEEKDAY() to call a particular Sheet in a Wookbook. The 
Sheets are called Sunday - Saturday and I have an IF statement that needs to 
check for particular information on that sheet depending on what day it is. 
Have it currently functioning but it is taking up way to much space. I am 
unable to update my code with other functions that I need it to calculate 
until I can figure this part out.

Code
=IF(OR(AND((TEXT(TODAY(),"dddd")="Monday"),(HLOOKUP(MOD(NOW(),1),Monday!$B$1:$BA$40,2))>0),AND((TEXT(TODAY(),"dddd")="Tuesday"),(HLOOKUP(MOD(NOW(),1),Tuesday!$B$1:$BA$40,2))>0),AND((TEXT(TODAY(),"dddd")="Wednesday"),(HLOOKUP(MOD(NOW(),1),Wednesday!$B$1:$BA$40,2))>0),AND((TEXT(TODAY(),"dddd")="Thursday"),(HLOOKUP(MOD(NOW(),1),Thursday!$B$1:$BA$40,2))>0),AND((TEXT(TODAY(),"dddd")="Friday"),(HLOOKUP(MOD(NOW(),1),Friday!$B$1:$BA$40,2))>0),AND((TEXT(TODAY(),"dddd")="Saturday"),(HLOOKUP(MOD(NOW(),1),Saturday!$B$1:$BA$40,2))>0)),"y","")

As you can see it would be much easier if I could just do 
=IF (HLOOKUP(MOD(NOW(),1),WEEKDAY()!$B$1:$BA$40,2))>0)
but obviously that does not work.

Thank you in advance
0
12/2/2004 3:17:02 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
516 Views

Similar Articles

[PageSpeed] 39

Hi
try:
=HLOOKUP(MOD(NOW(),1),INDIRECT("'" & TEXT(TODAY(),"dddd") &
"'!$B$1:$BA$40"),2)....

--
Regards
Frank Kabel
Frankfurt, Germany

"Tahlmorrah" <Tahlmorrah@discussions.microsoft.com> schrieb im
Newsbeitrag news:7B65B4DF-38CA-48CC-965D-9789AD790D7C@microsoft.com...
> I would like to use WEEKDAY() to call a particular Sheet in a
Wookbook. The
> Sheets are called Sunday - Saturday and I have an IF statement that
needs to
> check for particular information on that sheet depending on what day
it is.
> Have it currently functioning but it is taking up way to much space.
I am
> unable to update my code with other functions that I need it to
calculate
> until I can figure this part out.
>
> Code:
>
=IF(OR(AND((TEXT(TODAY(),"dddd")="Monday"),(HLOOKUP(MOD(NOW(),1),Monday
!$B$1:$BA$40,2))>0),AND((TEXT(TODAY(),"dddd")="Tuesday"),(HLOOKUP(MOD(N
OW(),1),Tuesday!$B$1:$BA$40,2))>0),AND((TEXT(TODAY(),"dddd")="Wednesday
"),(HLOOKUP(MOD(NOW(),1),Wednesday!$B$1:$BA$40,2))>0),AND((TEXT(TODAY()
,"dddd")="Thursday"),(HLOOKUP(MOD(NOW(),1),Thursday!$B$1:$BA$40,2))>0),
AND((TEXT(TODAY(),"dddd")="Friday"),(HLOOKUP(MOD(NOW(),1),Friday!$B$1:$
BA$40,2))>0),AND((TEXT(TODAY(),"dddd")="Saturday"),(HLOOKUP(MOD(NOW(),1
),Saturday!$B$1:$BA$40,2))>0)),"y","")
>
> As you can see it would be much easier if I could just do
> =IF (HLOOKUP(MOD(NOW(),1),WEEKDAY()!$B$1:$BA$40,2))>0)
> but obviously that does not work.
>
> Thank you in advance

0
frank.kabel (11126)
12/2/2004 3:26:50 PM
Reply:

Similar Artilces:

Use Form to prompt for report criteria
I have a form that I am using to prompt for report criteria. When I run the query outside of the form, it works fine - prompting me for both criteria. However when I run from the form, I get #Error#. Can you see what I am doing wrong? Thanks in advance. I have two combo boxes that I have put in my underlying query. In the fields of the query are: [Forms]![frmSelection Criteria Form]![OfficeNumber] [Forms]![frmSelection Criteria Form]![Manager] *** On the OnClick event is the following: Private Sub Command6_Click() On Error GoTo Err_command6_Click Dim stDocName As String st...

Naming charts on own sheet
Hi. I have a series of charts (which are all contained on their own sheets). I need to name each of the charts (as they will be used by someone else in a macro). I have tried clicking on them and also pressing shift before clicking on them, and I am not able to change the name in the name combo box. Can anyone advise me of how I can change the names. Thanks for your help. Hi, If you have chart sheets you can change the name by simply changing the sheet tab name. What you described is the method used on chartobjects, which are usually on a worksheet. Cheers Andy -- Andy Pope, Mi...

Index and Match Function in VB
A few days ago, I did receive very good help from Max and Dave to achieve the following formula, =IF(B5="Synth",INDEX(AUTOS!$K$10:$K$500,MATCH(1,(Autos!$F$10:$F$500='ID'!D5)*(Autos!$G$10:$G$400='ID'!$R$2)*(AUTOS!$I$10:$I$400="C"),0))-INDEX(AUTOS!$K$10:$K$500,MATCH(1,(AUTOS!$F$10:$F$500='ID'!D5)*(AUTOS!$G$10:$G$400='ID'!$R$2)*(AUTOS!$I$10:$I$400="P"),0))+$R$2-$R$5,VLOOKUP(D5,$P$4:$R$8,3)-$R$5) Basically I was looking to retrieve data based on multiple criteria and the formula is working just fine. I now want to also be able to def...

Hiding sheet tab names
I created an automated workbook where I need to keep the sheet tab name hidden from the user. I went into Tools-Options-View and unchecke Sheet Tabs. Then I protected the workbook and the sheet yet the use can still go into Tools-Options-View and re-check the Sheet Tabs t view them again. How do I lock the user out of viewing the sheet tabs? :confused -- Message posted from http://www.ExcelForum.com You could use the "very hidden" property that prevents users from viewing hidden worksheets without using VBA: Dim ws2 As Worksheet For Each ws2 In ThisWorkbook.Worksheets If w...

converting tabular structures in a Word document into an actual table or reading data from the tabular structures using VBA code
I have a macro which can read the last cell/column of all tables in a Word 2003/2007 document and store the data in an MS-Access table. But, some Word documents have the data in structures like a table format but are not actually tables. The structure looks like a table, but the table borders are actually line connectors. These documents were created by a software(VeryPDF PDF to Word converter) which converted the PDF documents(the original format these documents were) into Word documents. 1. Is there a way I can convert/replace the tabular structures with actual tables in Word so t...

Insert,Update Data in sage (MS Access Linked tables) using Vb.net form
Hi folks, I am developing application using vb.net which requires integration with SAGE LINE 50 (Accounting software ) V11... The data which SAGE is using is MC ACCESS 2003 database... with linked tables in it... Now I Have developed the Sage connection using ODBC which works fine when reading the record but cannot Add or Update record into the Linked tables.... When i debug the program the error is at the line where it has... <br> MyodbcCommand.ExecutenonQuery() <br> Can anybody Help ????? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/acce...

Excel sheet not visible
When I open a local copy all I can see is the content of 1 cell in the menu bar, the work area is all blank gray. I can see the complete sheet (3 Pages) in print preview. Most users have no problem with this sheet. I have repaired the installation of Office 2000 and ran all updates, Same problem. It is something on the local PC that is causing this effect. Other sheets show up just fine. This is a very simple spred sheet with no formulas. Maybe it's just off the screen. Window|Arrange|Tiled (and then resize it the way you want) New group user 944 wrote: > > When I open a l...

Function is killing performance
Hi - I'm using a custom function in a query and it takes forever - was hoping someone might have an idea of how to speed things up a bit. A little more info: the function is relatively simple (4 arguments that are run through an IF statement to pick a value from another table (only 12 records). The query is run against a table with 41k records. The numbers don't seem to big here, and the query takes over an hour. Any ideas?? Where & how are you using the IF's? Sounds like a coding problem to me Pieter <stephen.h.dow@gmail.com> wrote in message news:1189019241.93425...

Using atl based win dll with CString functions from the mfc projec #3
I have atl based general windows dll with class which contains functions which uses CString as parameters or return values. This dll might be used from the atl or mfc project. Dll can be used from ATL project without problems but whenever I try to use this class from the MFC project I get the following linker errors: error LNK2019: unresolved external symbol "__declspec(dllimport) public: int __thiscall MyClass::AddMenu(long,class ATL::CStringT<wchar_t,class StrTraitMFC_DLL<wchar_t,class ATL::ChTraitsCRT<wchar_t> > > const &,long)" ... If I replace CStri...

Not using zeros in graphing.
I have a running workbook that has tons of information. I have added a sum page in order to have all the data summed up in one simple place. I have formulas that read back into the workbook to link to a cell. Depending on what moth it is, that cell could be empty as it is a yearly wookbook. For example, if this is August, then there is information in the workbook up to August, but none after. With that said, the sum page has the #DIV/0! in the cell which essentially equals zero. I also have graphs that I have linked to this sum page. My problem is in order to keep the graphs up to...

PROPER function
Hi all - Stupid me, maybe, but I can't get this function to work. The help file says: "PROPER (text) Text is text enclosed in quotation marks, a formula that returns text, or a reference to a cell containing the text you want to partially capitalize." So the text I want to convert is in cell, say, I370. In helper cell J130 I enter =PROPER(I370) But all I get in cell J130 after hitting enter is =PROPER(I370). In other words, it doesn't seem to be acting as a formula --- it is just appearing literally. This is an inherited spreadsheet, so it's possible something ...

using vba so search multiple Sheets
i'm trying to search across multiple sheets based on data submitted via an input box. So essentially, you click the button, a box appears, you type what your looking for and if it finds it, it'll select it otherwise a error message appeats. i found the below code, but it only works if the cell with the value in it is active (i.e. i've clicked on it). Code: Dim datatoFind Dim sheetCount As Integer Dim counter As Integer Dim currentSheet As Integer On Error Resume Next currentSheet = ActiveSheet.Index datatoFind = InputBox("Please enter the value to search...

DPM and VMM on same server using side-by-side SQL installation
Windows 2008 Std R2 Server, 64bit VMM 2007 is currently installed (with the built-in default database) SQL 2005 Express Edition, SP3, 32bit. I was advised by Microsoft that both VMM and DPM could be installed on the same server (small DR environment) The VMs are hosted on a Windows 2008 Enterprise Server w/ VS2005R2 - because the hardware didn't support HyperV. VMM works great to manage the Windows 2008 VS2005R2 Host running Win2K3 server instances ... after lots of trial and error. Now, I need to install DPM 2007 onto the same server... DPM wants to install the 64bit vers...

Sheet event
Hi, Is there a way(technic) to find out, when a sheet is being called by a module, to find out the name of the module calling the sheet beside passing the name of the module has a parameter? (The objectif is to avoid the execution of an other module in the "Worksheet Active" event for performance purpose) Regards. On Mar 5, 3:56=A0am, Bobby <rpq...@gmail.com> wrote: > Hi, > Is there a way(technic) to find out, when a sheet is being called by a > module, to find out the name of the module calling the sheet beside > passing the name of the module has a parameter? (Th...

Use a VBA Macro inside an Excel Cell
This is a multi-part message in MIME format. ------=_NextPart_000_02B7_01C9F6B9.C9F418D0 Content-Type: text/plain; charset="windows-1255" Content-Transfer-Encoding: quoted-printable its been helpful to me so maybe it will do good for you too: how to create a simple macro within Microsoft Excel, and then how to use = that macro to calculate a single cell value. http://sysudi.blogspot.com/2009/06/use-vba-macro-inside-excel-cell.html ------=_NextPart_000_02B7_01C9F6B9.C9F418D0 Content-Type: text/html; charset="windows-1255" Content-Transfer-Encoding: quoted-printable &l...

Using "fixed" decimal place function data for charts
Is there a way to graph data which utilizes the "fixed" function for decimal places? When I try this it will not graph, as the data has become text or something - even though I've selected number in the cell formats. If you've fixed the number of digits using only number formats, the values remain numeric, and they should chart just fine. This means selecting the range, going to the Formatting menu and selecting Cells, and on the Number tab, selecting Number in the category list, and setting a number of digits. I'm not familiar with a "fixed function". I...

slow sync with Calendar using Kyocera 7135 via Outlook 2003
I'm stuck and need help. I have a Two Day Old Brand New Dell Dimension 4600. Does anyone out there know what to do when Outlook 2003 is giving a Log report that reads that Outlook Calendar - Slow Sync synchronization failed? This is a first time occurence for me. Outlook also reported that a device is trying to get access to my E- Mail addresses which may indicate a Virus may be present. I've run my McAfee Virus Protection and it didn't detrect anything. I'm at my wits end and don't know what else to do. Can anybody help? ...

Create a Loop for create a phone call
Hi Best Regards Excuse me for my bad english I am developing a workflow, which must create automatically phone calls and assign them to certain users of the following way: it must create the number of 10 calls and to assign them to user 1, to create 5 calls and to assign them to user 2 etc the data of the phone calls is in a database I am trying to create a loop but I have not found like I am thankful for your help regards You will need to do this using the CRM SDK/web service. Do something like : CrmService service = new CrmService(); // Setup the CrmService class here foreach (U...

invalid use of property message
I am trying to cre4ate a macro that checks the value of a number of sheets to determine if they are already visible and if so it will not close them - With Sheets("Pay Inflation - Biometrics") Sheets ("Statistics") Sheets ("Direct Cost Savings Breakdown") Sheets ("OT Reduction") Sheets ("Nurse OT Reduction") Sheets ("Premium Labor Utilization") Sheets ("Pay inflation - Timestamp") Sheets ("Calculation Error") Sheets ("Le...

can not Send/receive using Outlook 2003
Just bouhght new computer and am loading all of my software that worked onmy old one. When I try to send and recieve I get the message object not found. I have used test and this work ok. I have also reloaded all of my broadband and office software numerous times to no prevail. Software Windows xp Office 2003 ADSlL broadband with bigpond Gp Check to make sure the accounts are setup correctly if they are then run Detect and Repair, still an issue then create a new profile. May also want to check your anti-virus software program setting for outgoing mail and disable that option. Rega...

using rule creates multiple entries. How can I stop this?
I'm going to give you the details of this so maybe someone can just tell me how to correct or whether it can be corrected. I've tried creating different rules, rules with exceptions, multiple rules with multiple exceptions, all I can think. Here is the scenario. Although I'm using Lisa as an example, this problem applies to every staffer in my 9 person office. Lisa is my secretary. She sends me internal email, staff questions and stuff which goes to a a "Staff" subfolder of the Inbox. I have a rule for this. She also sends me mail she has scanned. I ha...

Linking Multiple Excel Sheets
I am not an expert in Excel....!, I encountered a situation where in I had to get data from 14 excel separate sheets having same structure (differing in values). After playing aroung with excel for few minutes, I found "Import External Data" under Data. Manually I imported all the 14 sheets and in my main sheet wrote a small formula which would give a consolidated information. With this you also have a feature which refreshes data (at specified time / or when opened). There might be a different & faster way to do it, which I am not aware of!!!. Anyway this worked for me........

Outlook 2002 crashes when using IMAP stuck in loop
I've been using Outlook 2002 with IMAP for some time now. Yesterday, I began receiving a message upon starting up Outlook: "Microsoft Outlook has encountered a problem and needs to close. We are sorry for the inconvenience." The error message is as follows: AppName: outlook.exe AppVer: 10.0.2627.1 ModName: kernel32.dll ModVer: 5.1.2600.1106 Offset: 0001a669 Sometimes if I enter in "Work offline" mode and then check messages I get the following: AppName: outlook.exe AppVer: 10.0.2627.1 ModName: pstprx32.dll ModVer: 10.0.2625.0 Offset: 00005528 I am al...

Using Word To Design Survey Forms
We already have a form generated in Word to manually collect data. We want to use this form as the basis of a Sharepont survey. I know I will need to crete fields in SP to collect data but importing the word file(16 pages) as a basis for the survey form could save a lot of time Trevor SharePoint has a webpart designed for surveys. Give it a try. --rms www.rmschneider.com On 19/01/10 00:07, Trevor Aiston wrote: > We already have a form generated in Word to manually collect data. > > We want to use this form as the basis of a Sharepont survey. > >...

How do I format mergefield dates to dd/MM/yyyy in Publisher[using.
I drive a Publisher catalog merge from an Access 2003 source database. The dates in Access are formatted in European format [dd/MM/yy] but always display in US MM/dd format - which causes confusion e.g. 12/03 would be read in Europe as 12th March, not December 3rd. Short of exporting the Access data to a Word or Excel file and formatting the source data as text, I cannot find any way to change the mergefield format [as you can easily in Word mergefields]. Help please? Roger Walker How do you have the short date setup in the Regional and Language settings in the control panel? -- ...