How to pull data from a worksheet where variables are met
Requirement:
The user has 2 rotas depending on which site the user is looking at. The rota changes slightly for each day of the week.
The user needs to fill in the table on the Query Tab, based on the selection of site (A1) and day of the week (AB3) which is linked to the date.
Monday's data comes from the Rota Tab Cells B to Y, Tuesdays from AA to AX etc.
When the day changes to Tuesday, the user needs the formula to change to change to Tuesdays cells i.e. AA to AX.
Solution:
=INDEX(Rota!$A:$FS,IF($A$1="Hosp 1",ROWS(Rota!$A$1:$A15),ROWS(Rota!$A$1:$A31)),MATCH($AB$3,Rota!$A$1:$FS$1,0)+COLUMNS($A$1:A$1))
Obtained from the OzGrid Help Forum.
Solution provided by NBVC.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to use an array formula to omit data if criteria met |
| How to use match formulae: input data range based on cell value |
| How to convert data in one column to two columns |
| How to data trim and clean cell values with VBA code |
| How to use VBA to returning 5 left digits and pasting to bottom of existing data set |
| How to consolidate data into single sheet from the selected spreadsheets |
| How to use a macro or formula to copy data from cell to all cells in that group in adjacent column |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
Gallery
