OzGrid

How to pull data from a worksheet where variables are met

< Back to Search results

 Category: [Excel]  Demo Available 

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



stars (0 Reviews)