If, iferror, indirect

[wizzan1]

Hello!

Ive a little problem solving this code:

excel2.jpg

=IF(H74=5;INDIRECT("'["&L75&"]"&M75&"'!"&N75);IF(H74=6;INDIRECT("'["&L76&"]"&M76&"'!"&N76)))


Point of my code is:

If current date is 5 or 6 and so on is about to be added, it should point to another workbook(hej.xlsm), and a new sheet with the current date (Sheet5,6 and so on), and cell A1.

What i want to do this when the date changes to the next sheet, that sheet is not yet created and gives an error. Is it possible to lets say if today midnight date switches to 8 and Sheet8 is not yet created - Can it be redirected to previous sheet (Sheet7) until Sheet8 is created?

Best regards

wizzan1

[Glenn Kennedy]

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please don't attach a picture of an Excel sheet (no-one will want to re-type all your stuff before starting).

1. It does NOT have to be your real sheet - mock up a SAMPLE if you need to. But not 1000's of rows!!! It makes manual checking so tedious. Whatever you do... make sure that all confidential information is removed first!!

2. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

3. Make sure that your desired solution is also shown (mock up the results manually).

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[wizzan1]

Ok so in Book1, Sheet1, when the date changes inB2 it should return the number in F13 from A1 in hej.xlsm. But Sheet9 is not yet created. so when the Date changes to 9, I want it to point to the previous sheet(Sheet8) until Sheet 9 is created.

Let me know if unclear

Best regards

[Glenn Kennedy]

Open both files in the same place. try it out. If it's OK, I'll explain what's going on.

[wizzan1]

I've opened them, i dont understand it completely but a little. It gives me a REF error whatever Date number i fill in.

Regards

[AliGW]

Administrative Note:

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

[Glenn Kennedy]

Did you enable macros on both sheets, when opening?

[wizzan1]

Yes they are both macro-enabled worksheets.

[Glenn Kennedy]

Try it now...

I am assuming that it failed in MY demo files and that you have not raced ahead and copied the formulae into your real sheet...

If the two files attached here fail:

1. What do you see in hej, dataA1:A12

2. What do you see in Book1 G3:G11

3. What formula do you see in Book1 G3

and then step thorugh the formula in Book1, f13, using formulas/evaluate formula and transcribe the LAST line before you get he error message and first one after it.