Question description:
This user has given permission to use the problem statement for this
blog.
indexing multiple rows of data from criteria in a column (need to be able to bring up multiple job specifics [each job has its own row] by searching in the "due date" column for jobs due between start date and end date
Solved by O. S. in 55 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
12/09/2018 - 04:38
Hi mate
Excelchat Expert
12/09/2018 - 04:38
Hi Welcome!
User
12/09/2018 - 04:38
I am hoping you can help me out here- I've found a youtube video but it seems that it might get complicated with the kind of data i use
Excelchat Expert
12/09/2018 - 04:39
Ok, do you have a sheet to upload?
User
12/09/2018 - 04:39
basically i need to extract all rows from a range hat meet criteria in one column
User
12/09/2018 - 04:39
I'll try and mock something up
User
12/09/2018 - 04:39
onemomentplease
User
12/09/2018 - 04:40
actually i should have done the mock up before connecting lol
User
12/09/2018 - 04:40
there's a lot of data
User
12/09/2018 - 04:40
I'm tr4ying to use a mix of indexing and array formulas
User
12/09/2018 - 04:41
i've got about 25 columnsn and 1000 rows of data
Excelchat Expert
12/09/2018 - 04:41
OK,
Excelchat Expert
12/09/2018 - 04:42
the structure and content is what we need to look at
User
12/09/2018 - 04:45
basically if I enter a start date and an end date- I need to pull all the row data for jobs that fall between those dates
Excelchat Expert
12/09/2018 - 04:46
Ok, need the dates in the same format to start
User
12/09/2018 - 04:47
day/month/year
Excelchat Expert
12/09/2018 - 04:47
Month, day , year is what I think you have , which is fine
User
12/09/2018 - 04:48
using this month and last month
User
12/09/2018 - 04:49
either way the formula should be the same format lol
Excelchat Expert
12/09/2018 - 04:49
To pull data , in basic EXCEL need another sheet or range with formula, or we create a table with filter
User
12/09/2018 - 04:50
i have 3 other sheets i'll be pulling the data from
User
12/09/2018 - 04:51
the date ranges are going to be entered in one sheet- and the results need to populate in a seperate sheet
User
12/09/2018 - 04:51
while drawing on the 3 source data sheets
User
12/09/2018 - 04:51
(all in seperate tabs inthe one file)
Excelchat Expert
12/09/2018 - 04:52
Let me take this to EXCEL and try a table solution,
User
12/09/2018 - 04:52
ok
Excelchat Expert
12/09/2018 - 04:52
Please stand by
User
12/09/2018 - 04:52
i can use indexing to draw the data from the sheets using:
User
12/09/2018 - 04:52
=INDEX('FBE Source Data'!$A$1:$V$1000,,'DO NOT TOUCH'!A2)
User
12/09/2018 - 04:53
but to get it to only show the data falling between dates is where i'mstuck
Excelchat Expert
12/09/2018 - 04:54
Not sure INDEX will do what you need here,\
Excelchat Expert
12/09/2018 - 04:54
dont you want the entire row?
User
12/09/2018 - 04:54
any help would be great
User
12/09/2018 - 04:54
yes- I just copy the row references across
User
12/09/2018 - 04:54
across eachcolumn
Excelchat Expert
12/09/2018 - 04:56
I will set up another sheet and pull data from 2, simple table setup
User
12/09/2018 - 04:57
thankyou
Excelchat Expert
12/09/2018 - 05:06
Have one table in - hang in there
User
12/09/2018 - 05:07
ok
Excelchat Expert
12/09/2018 - 05:13
I need more logic work to bring in a 2nd table, but I thought we should go over the concept now, in case time runs out
[Uploaded an Excel file]
Excelchat Expert
12/09/2018 - 05:14
The idea is just an IF AND formula and EXCEL table that filters out BLANKS.
Excelchat Expert
12/09/2018 - 05:15
To add another table or two need IF OR AND
Excelchat Expert
12/09/2018 - 05:15
and I need time to work that thru, but see if this makes sense first'
User
12/09/2018 - 05:15
ok thankyou- I'll look into it and see how I go
Excelchat Expert
12/09/2018 - 05:17
I can send a final via site support , want to build out and test the IF OR AND,,fir 3 tables
Excelchat Expert
12/09/2018 - 05:17
Is that OK?
User
12/09/2018 - 05:18
and to add the other sheets - iu just use the same formula at the end of the first prefixed with a comma?
Excelchat Expert
12/09/2018 - 05:18
Yes,
User
12/09/2018 - 05:19
that should work- if it displays in the same results table
User
12/09/2018 - 05:19
thankyou very much
Excelchat Expert
12/09/2018 - 05:19
and you dont need the complex OR AND, you canust
Excelchat Expert
12/09/2018 - 05:19
sorry , just cover the range of all 3 tables with the current formula
Excelchat Expert
12/09/2018 - 05:20
the TABLE setup with BLANKS suppressed will bring all the data together
User
12/09/2018 - 05:22
so when you mentiuion cover all three ranges with the same formula- do you mean:
User
12/09/2018 - 05:22
=IF(AND(Sheet1!$D2>=Summary!$H$2,Sheet1!E2<=Summary!$J$2),Sheet1!B2,""), IF(AND(Sheet2!$D2>=Summary!$H$2,Sheet2!E2<=Summary!$J$2),Sheet2!B2,"")
User
12/09/2018 - 05:22
or would i remove the second argument "IF"
Excelchat Expert
12/09/2018 - 05:23
Use the same formula -one IF
Excelchat Expert
12/09/2018 - 05:24
Just make sure to change the SHEET reference to bring in the other sheet data
Excelchat Expert
12/09/2018 - 05:24
this is simple method
Excelchat Expert
12/09/2018 - 05:24
If all sheets are the same, you can combine then just use one formula
User
12/09/2018 - 05:25
can i be a bother and ask you to type it out as it didn't seem to work onmyend
Excelchat Expert
12/09/2018 - 05:25
Let me get you a 2nd example with 2 sheets
User
12/09/2018 - 05:25
its returned #VALUE error
User
12/09/2018 - 05:26
ok yes please
Excelchat Expert
12/09/2018 - 05:29
[Uploaded an Excel file]
Excelchat Expert
12/09/2018 - 05:29
This is covers both tables of only 9 rows, you have 1000 each , but concept is the same
Excelchat Expert
12/09/2018 - 05:30
Copy 1000 formulas, then switch sheet reference, then re-peat
User
12/09/2018 - 05:31
ok I'll try that out- thanks for the helpd
User
12/09/2018 - 05:32
ok that seems to work- much obliged- youve beenb a great help
Excelchat Expert
12/09/2018 - 05:33
OK,, this will work, just need to get all 1000 rows covered from each sheet and use an EXCEL table.
User
12/09/2018 - 05:33
yeah
Excelchat Expert
12/09/2018 - 05:33
Please stop back if you have any problems
User
12/09/2018 - 05:33
will dpo
Excelchat Expert
12/09/2018 - 05:33
Thanks and bye!
User
12/09/2018 - 05:33
cy
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
