Double condition formula based on drop down selection

gannesh0604 Posts 24 Registration date Thursday July 20, 2023 Status Member Last seen January 31, 2024 - Updated on Mar 16, 2024 at 04:23 PM
BoBot Posts 182 Registration date Wednesday October 11, 2023 Status Moderator Last seen March 28, 2024   - Mar 16, 2024 at 04:23 PM

Hi All,

I have an excel file with sheet names : Primary Data sheet, Ground floor, First Floor and readings. I need this file to capture printer usage details.

I have given floor id as 3363 for Ground floor and 3975 for first floor. see the cell A1 n both Ground floor and first floor sheets.

If I put floor id number in primary data sheet of A10 , the DP ID will be automatically synchronize/connect with the sheet only based on floor id to capture color and B/W  

Lets say, if i put floor id 3363 in A10 in Jan-24 and DP ID in Jan-24 , color and b/w values are copied in ground floor of Jan-24 DP ID. if i change floor id 3975, automatically the DP ID , color and b/w values will be copied to first floor of Jan-24.

I want to capture the color and B/w printer usage details of different users(DP ID) of two printers(ground floor printer and first floor printer) for every month in the entire year in a single sheet .. 

Reference link to download sample file: https://we.tl/t-xlFM9pHPDL

Regards,

Ganesh

1 response

BoBot Posts 182 Registration date Wednesday October 11, 2023 Status Moderator Last seen March 28, 2024   1
Mar 16, 2024 at 04:23 PM
Hello !
It's me, Bobot, your favorite AI!
You can achieve this using the INDIRECT function, which can refer to a sheet name that changes based on the floor ID you input in cell A10. Combine this with VLOOKUP to fetch the details.

Here's an illustrative formula:

`=VLOOKUP(, INDIRECT("'"&A10&"'!A:D"), , FALSE)`

Explanation:

- is the DP ID on whose basis you want to fetch data.
- INDIRECT, with argument `' A10 '!A:D', creates a dynamic reference to your sheet based on what you input into cell A10 of your primary data sheet. So, it'll refer to your Ground Floor or First Floor sheet based on the floor id.
- is the column number in your lookup range (columns A to D) where the data you want is located. For color and b/w, you will need two separate formulas.

Keep in mind that the floor IDs need to match exactly with the sheet names for the INDIRECT function to work correctly.

0