Formula Inquiry

[EQX_FD]

Hey All,

The following formula is for displaying a time range. If a box is marked in any way it should choose the first and last time marked, in thirty minute increments from 4:30AM-12PM. However, I just added the 4:30AM spot into the sheet(new column, but cannot figure out how to have this additional information calculated into the formula posted below. Any help would be greatly appreciated as the values (AX/AY) are for columns that do not exist in the sheet which is what is confusing me the most.

=IF(OR(ISNA(AX5),ISNA(AY5)),"",""&TEXT(AX5,"h:mm AM/PM")&"-"&TEXT(AY5,"h:mm AM/PM"))

Thank you!

 

[DSCfromCFA]

G'day EQX,

There are a few things that are unclear in your request, but the following cannot be correct - ". . . the values (AX/AY) are for columns that do not exist . . ."

As the formula requires data in AX5 and AY5, if the formula works then those columns clearly do exist, although they may not be visible. It could be that a macro or VBA code is writing to them, possibly from another workbook or some other external source, or that the columns are simply hidden (right click when the cursor is in the column header area and select 'unhide' to see if they appear, or in the Formulas ribbon select the Trace Precedents option when the cell containing the formula is active.

Another thing to try is if the half hour increments beginning at a 4:30 are in a data validation list. Cells subject to data validation can be searched with the Find and Select option in the home ribbon, and choose the data validation option to see if AX5 & AY5 are found.

Good hunting

shane

 

[EQX_FD]

Hey Shane,

Thank you so much for taking a look! It's funny, I thought the same thing but though there are multiple sheets for this particular file, AX/AY is not hidden in any of them nor is it present. To complicate further, If I delete a column or add a column, these values then change. For example, this sheet started with values AY/AZ but I deleted the AX column and they changed to AX/AY. The same is true if I add further columns, values change to AZ/BA and so on.

It looks like the cells are also not in a data validation list as there are no results when attempting this on a 4:30 cell (the one I added) or a 5AM cell(which was present prior)

Please shoot me any other ideas you may have or if I can clear anything else up.

Cheers,

Ethan

 

[DSCfromCFA]

G'day Ethan,

Did the trace precedents reveal anything? - I did a trace precedents on a hidden column and the precedents line went to the cell boundary that contained the hidden columns. Precedents in another worksheet point to a worksheet icon.

In the data ribbon click on 'Connections' (its just below the actual Data tab) to see what other workbooks/data sources are connected to the workbook.

The address/data changes are interesting in that it indicates that whilst the formula is obviously using a relative address, the data it seems is being written into a fixed address (from whatever source).

 

[DSCfromCFA]

G'day again,

and then there is the hide/unhide rows and columns found in the format section of the home ribbon - select Format, then select the hide and unhide submenu.

Cheers

shane

 

[EQX_FD]

Hey Shane,

I used Trace Precedents function and it leads to off the visible table but does not point to anything on any other sheets. I used the unhide/hide functions as well but nothing appeared. I tried the Connections tool but it showed no connections to other workbooks. I'm kind of at a loss at this point but I really appreciate you bringing your expertise to try and help.

Cheers,

Ethan

 

[DSCfromCFA]

G'day Ethan,

The fact that a precedent line appears seems to me means that the cells are being hidden by some process

One last roll of the dice and then I'm out of ideas.

1. Copy and paste the cells you can access to a new workbook and see what happens. (Also try moving the tab to a new workbook and ditto).
2. If you type AX5 into the name box in at the top left of the worksheet, does anything show in the formula bar?
3. Similar to the previous idea - type in the range you want into the name box, hit enter then Contol + c to copy - then paste into a new workbook or worksheet.