Sarah has attended:
Excel Advanced course
Dragging a formula horizontally whilst searching vertically
Hello,
I have a large document with lots of separate sheets for different retailers with one main summary sheet.
On the main 'summary' sheet, I want to pull back information from each of the retailer sheets. The summary sheet is working horizontally, while the retailer sheets are working vertically.
This means I want to pull a formula across, whilst returning search result from a separate sheet going vertically
I have used Index formulas however because the cells in the sheet I am looking to are merged, it isn't pulling back the correct merged cell reference and instead is looking at each individual cell and isnt working
I think I could use a sumif formula to do this, but because the cells are pulling back text, I cant seem to do this
If someone could help I would be very grateful!
RE: dragging a formula horizontally whilst searching vertically
Hi Sarah,
Thank you for your question to the forum.
If you have Office 365 Excel then I would suggest you use the TRANSPOSE function which changes the orientation of all linked cells. Let's say your source data is in a sheet called Retail South in cells A2 to A11.
1. Ensure all source cells are unmerged
2. In the summary sheet, select the first cell in the row you want to populate and type =TRANSPOSE('Region South'!A2:A11) or just type =TRANSPOSE and then select the cells from the other sheet
The real benefit of doing this is that the summary data is a live link to the source data. So any changes made will be reflected in the summary sheet.
I hope this helps. Please let me know if it has worked or not
Kind regards
Martin Sutherland
IT Trainer
Will be marked as resolved in 5 days
Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.
