Hi there
I am having a real problem with a workbook that updates from a xlsx file and then I need to search for entries between a date range. The workbook works if I manually type the data in in sheet 1 (Optimy) and updates in sheet 2 (Filtering). However, when I refresh the data nothing happens. The formulae have grown to a point that I think I've created a monster. Any ideas to help solve this?
Yes, a monster indeed.
OK - talk us through exactly what your monster formula is doing - step-by-step. Nobody is going to back engineer it to find out for themselves, so let us have that information first and we can take it from there.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
The formula is looking for dates in a date range. If a date is found, it (should) give a value of 1, and only theses values will be visible in the Filtering sheet (if filtering is set to 1). There are twenty five cells per row where a date can occur. The data source is another spreadsheet that is output from our database (as an xlsx file). This updates the Optimy sheet. However, when the workbook is refreshed with the data the Filtering sheet does not work. If I type the data in to the Optimy sheet manually the Filtering sheet works!
No, sorry - that's not quite what I meant!
Talk us through - step-by-step - what the formula is looking at and returning.
Sorry.
If cell C2 in Optimy sheet has a date that is within the date range set in the Filtering sheet at C1 and E1 (etc) then cell A4 in Filtering will have a value of 1 and the part of the row where this is true is revealed. This works when data entered manually in Optimy sheet.
However, when workbook is refreshed from external source, the data refreshes in Optimy sheet but Filtering sheet does not - all cells in Column A return 0.
That' the best I can explain.
Tony
It seems that the values in the 'date' columns are text strings. Example scroll to column EQ and select EQ1. Select the drop down in the 'Number' pane and notice that Number, Currency, Accounting ... all show the same thing. If you select EQ1:EQ24 > Data (tab) > Text to Columns > Next > Next > Date > DMY > Finish, those text strings become dates and ones show up in column A on the Filtering sheet. If the dates are being imported as text then it may be that one of the VBA/ Macro contributors (I am VBA illiterate) could help.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks